СОРТируем
Начнем с функции сортировки. Она помогает представить данные в нужном нам порядке — буквально, отсортировать массив по тому или иному параметру (например, по возрастанию или убыванию значений в определённом столбце). Как это работает?
На скриншоте с примером функция у нас имеет вид =СОРТ(A2:F45;1). Здесь, после открытия скобок, в первую очередь мы обозначаем рабочий диапазон данных, которые будем сортировать, в нашем случае A2:F45. После символа точки с запятой — порядковый номер столбца в массиве, по которому мы хотим выполнить сортировку. Для примера мы решили отсортировать данные по годам — а это первый столбец.
Стоит обратить внимание — мы говорим именно о номере столбца в массиве, а не в редакторе. Если бы в переданном на обработку диапазоне значения данные начинались бы со столбца B, а не A, как у нас, первым был бы именно он, С — вторым и так далее.
Разберём синтаксис функции-сортировщика чуть подробнее. Условно его логическая структура выглядит так:
=СОРТ(массив,[индекс_сортировки],[порядок_сортировки],[по_столбцу])
То есть, если мы добавим через точку с запятой ещё один параметр, он будет отвечать за порядок сортировки. Соответственно =СОРТ(A2:F45;1;-1) проделает то же самое, но в обратном порядке — от самого недавнего года к самому «древнему».
Ещё одна опция — выбор сортировки по строкам, а не по столбцам (бывают такие массивы, что именно так удобнее: строк немного, а столбцов — наоборот, огромное количество). Параметр это логический, по умолчанию (если не прописывать) его значение = ЛОЖЬ. Это значит — сортируем строки (обычный режим). Если поставить ИСТИНА, то редактор будет сортировать по строкам, а не по столбцам. Формула тогда будет иметь вид =СОРТ(A2:F6;1;1;ИСТИНА), и — обратите внимание — индекс сортировки, наш второй параметр после диапазона, в этом случае будет определять номер строки (точно так же, по порядку в обрабатываемом диапазоне), а не столбца.
Может возникнуть закономерный вопрос: сортировка ведь есть в виде кнопки в интерфейсе редактора, зачем знать эту формулу? А ответ довольно прост — если у вас простая задача по сортировке и функционала кнопки хватит — пользуйтесь ею, конечно. Но в данном случае, во-первых, сортировке подвергается именно массив, с которым вы работаете, а формулу вы можете примостить в любом удобном поле: она результат вам выдаст, но исходный массив останется в том же виде, что и ранее. Иногда это важно. Есть еще и другие факторы, но о них — ближе к финалу. А пока займёмся фильтрацией.
ФИЛЬТРуем
Название функции отражает её суть, поэтому логика её работы интуитивно понятна. Если из массива данных необходимо извлечь строки, соответствующие заданному условию, используется фильтрация. К примеру, в нашем условном массиве мы хотим найти и показать отдельно строки, имеющие отношения к региону «Урал». Наша формула будет иметь такой вид: =ФИЛЬТР(A2:F45;C2:C45=H2)
Давайте разберём её структуру. Первый параметр — привычно, диапазон нашего исходного массива, A2:F45. Второй, после точки с запятой — C2:C45=H2, где мы указываем столбец, в котором содержится критерий фильтрации, а =H2 указывает на конкретное значение в этом столбце, по которому мы хотим отфильтровать. Для удобства оно у нас выписано в отдельную ячейку, H2 — мы ведь хотим отфильтровать «Урал».
Но это ещё не всё. Критерии поиска можно комбирировать. Например, вычленить из массива не только «Урал», но и «Фрукты».
Формула имеет вид =ФИЛЬТР(A2:F45;(C2:C45=H2)*(E2:E45=I2)). Обратите внимание, критерии фильтрации у нас объединяются через символ звёздочки, и находятся — если их несколько — в собственных скобках. Значит, в конце нашей формулы скобка должна быть двойной. Мы к изначальному варианту присовокупили значение «Фрукты» на I2, и, соответственно диапазон столбца с типом товара — E2:E45.
Вы спросите: зачем знать формулы, если в редакторе уже есть возможности фильтрации? Ответ: если задача простая и использовать формулы не обязательно, делайте так, как удобнее. Но стоит иметь в виду: формула оставляет исходный массив в том самом виде, в котором он был изначально. И второй момент, еще более важный: формулы можно комбинировать. Чем мы сейчас и займёмся.
Комбинируем: сортируем отфильтрованное, фильтруем отсортированное
А теперь попробуем объединить наши формулы. Давайте отсортируем по объему сделок в рублях (столбец «Продажи», он шестой по счету в массиве) результаты фильтрации по критериям «Урал» и «Фрукты», которые мы только что получили. Причём в обратном порядке, от большего значения к меньшему. То есть, вверху будут наиболее объёмные сделки.
Формула будет такой: =СОРТ(ФИЛЬТР(A2:F45;(C2:C45=H2)*(E2:E45=I2)); 6;-1)
Обратите внимание, наша формула построена по всем правилам синтаксиса функции СОРТ, просто в качестве исходного диапазона массива для сортировки у нас подставлены результаты фильтрации, которую мы делали выше. А далее всё достаточно просто — индекс 6 (шестой столбец исходного массива), то есть сортируем объем сделок. А через точку с запятой — порядок выдачи, у нас -1, то есть обратный, от большего к меньшему.
Реклама: АО «Р7», ИНН: 5259019343, erid: 2W5zFJgFdHK
Начать дискуссию