Формирование списка с помощью формул Excel
Сегодня хотелось бы рассмотреть прием формирования отдельного списка с помощью формул Excel. А рассмотрим мы это на таком примере. Пришел вопрос от читателя моего сайта примерно следующего содержания
Хочу сделать список должников документов в excel. Как сделать так чтобы строка в которой есть слово «нет» (т.е у этого контрагента нет документов), автоматически копировалась на отдельный лист.
То есть, необходимо формировать список на отдельно листе в зависимости от определенного условия. На первый взгляд для решения задачи без макросов не обойтись, но есть определенный прием, который позволяет сделать это с помощью формул. Для наглядности я буду делать это на одном листе, но принцип один, при необходимости можно перенести на отдельный лист.
Итак, посмотрите на рисунок выше, к чему мы должны прийти. Список должников должен автоматически пополняться фамилиями, если в столбце «B» будет появляться слово «Нет». Нам потребуется добавить вспомогательный столбец. Для удобства мы добавим его самым первым, чтобы можно было использовать функцию ВПР (чтобы искомое значение находилось левее возвращаемого значения), можно было бы использовать функцию ИНДЕКС, в этом случаем вспомогательный столбец можно было разместить где угодно.
В данном столбце мы пропишем такую формулу
=СТРОКА()
Функция так и прописывается без аргументов. Эта функция возвращаем нам номер строки. То есть если эта функция будет прописана в третьей строке, то результатом будет 3 и так далее. Единственное, нам нужно, чтобы вычисления номера строки было только в тех строках, где есть слово «Нет». Для этого воспользуемся функцией ЕСЛИ и сделаем проверку. Если в столбце «B» есть слово «Нет», то вычислять номер строки, если нет, то указать в ячейку пусто. Формула примет следующий вид.
=ЕСЛИ(B2=»Нет»;СТРОКА();»»)
Протягиваем формулу на все строки.
В итоге мы получили номер строки только в тех ячейках, где напротив в столбце «B» есть слово «Нет».
Далее переходим в столбец «Е», это тоже будет вспомогательный столбец, здесь мы воспользуемся функцией НАИМЕНЬШИЙ, функция выглядит следующим образом
=НАИМЕНЬШИЙ(A:A;k)
где k — это порядок старшинства. Например, если указать 2, то будет выбрано второе наименьшее число из столбца «A». Нам же нужно, чтобы сначала было самое маленькое число и потом оно должно увеличиваться, то есть k должно начинаться с единицы и с каждой ячейке должно увеличиваться. Для этого мы можем сделать еще один дополнительный столбец и прописать пункты по порядку 1, 2, 3 и так далее и сделать ссылку на эти ячейки, но мы попробуем обойтись без вспомогательного столбца. Вместо k мы можем так же воспользоваться функцией СТРОКА(), которая при протягивании вниз как раз будет увеличиваться на единицу, но так как формулу мы начинаем со второй строки, а нам нужна первая k равная 1, то мы можем прописать СТРОКА()-1, в итоге формула примет вид в ячейке «E2», эту формулу протянем вниз с запасом.
=НАИМЕНЬШИЙ(A:A;СТРОКА()-1)
Единственное, что когда все числа из столбца «A» закончатся, то формула будет выдавать ошибку «#ЧИСЛО», поэтому с помощью функции ЕСЛИОШИБКА, сделаем проверку и если возвращается ошибка, то будем указывать пустую ячейку.
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(A:A;СТРОКА()-1);»»)
Вот теперь, всё как необходимо. Осталось последнее действие. С помощью функции ВПР, по искомому значению из столбца «E», возвращаем соответствующие фамилии.
=ВПР(E2;A:B;2;0)
Сделаем так же проверку, если в столбце «E» будет пустая ячейка, то будет возвращать пусто, если ячейка не пустая, то будем выполнять функцию. Пропишем итоговую формулу в ячейке «F2» и протянем формулу вниз так же с запасом.
=ЕСЛИ(E2=»»;»»;ВПР(E2;A:B;2;0))
Все, теперь ФИО будет автоматически появляться, когда напротив фамилии будет появляться слово «Нет».
Скачать пример файла https://yadi.sk/i/9HjjoSwzf4jS7
Понравилась статья? Ставьте лайки, вступайте в нашу группу ВКонтакте.
как прописать эти формулы на другой странице?
а как прописать эти формулы на другой странице?
Замечательная статья. Спасибо огромное авторам.
Вот только никак не получается эти формулы прописать на другой странице(
может кто подскажет
Точно так же. Можете из примера «вырезать» столбец F который получился и «вставить» его в нужное место на другом листе. Например на листе 2 в ячейку A1 и нажать «Вставить» все должно работать а вы сможете посмотреть как это прописать.
ПРИВ ОЧ ПОМОГЛИ! А МОЖНОЛИ СФОРМИРОВАТЬ КОНЕЧНЫЙ СПИСОК С ПРОПУСКОМ ЧЕРЕЗ ОДНУ СТРОКУ?
Спасибо, отличный способ!
Сам сидел над подобным вопросом пол дня, а тут все так изящно
Да, именно так! Всё изящно, но только до тех пор пока в отчете не появляется пару тысяч строк.
Если вместо ВПР использовать ИНДЕКС, то и несколько тысяч будут достаточно комфортно работать
А если необходимо, чтоб excel считал не только строки в которых есть слово «нет», а любое текстовое значение. Как надо поменять формулу =ЕСЛИ(B2=»Нет»;СТРОКА();»») ?
Иными словами, чтоб он считал формулу, когда в столбце будет любое текстовое значение, например «должен», «да» и т.д. любое слово.
Есть такая функция ЕТЕКСТ() — проверяет является ли значение в ячейке текстом. Если текст, то возвращает истину, иначе ложь. В итоге формула будет такой
=ЕСЛИ(ЕТЕКСТ(b2);СТРОКА(); «») ?