В прошлом выпуске мы рассмотрели задачу, в которой нужно было определить сотрудника с максимальным возрастом и вывести его имя в отдельную ячейку.
Предложенное решение этой задачи имеет один существенный недостаток. Дело в том, что функция МАКС возвращает первое максимальное значение из диапазона и на этом весь расчет прекращается. Это означает, что если в списке будет два или больше сотрудников с одинаковым возрастом, то в результатах мы увидим лишь первого по списку.
Давайте решим задачу таким образом, чтобы получился список всех сотрудников, имеющих максимальный возраст.
Итак, первым делом мы по-прежнему должны определить максимальный возраст. Для этого вполне подойдет функция МАКС, которую мы уже использовали ранее.
Ну а далее нам нужно сравнивать возраст каждого сотрудника с максимальным и в случае совпадения выводить имя сотрудника в столбце F ниже.
На первый взгляд ничего сложного, но не так все просто, как кажется и сейчас вы в этом убедитесь.
Дело в том, что все стандартные функции Экселя, которые сразу приходят на ум, выдадут лишь первое значение из диапазона, удовлетворяющее условию. Поэтому нам придется воспользоваться формулой массива.
Формула массива является частью стандартного функционала Экселя и многие даже не догадываются об ее существовании.
По сути, формула массива позволяет выполнять несколько вычислений с одним или несколькими элементами диапазона ячеек, то есть элементами массива. В итоге формула массива возвращает либо один результат вычислений, либо несколько результатов, то есть массив значений, что нам и нужно.
Не буду сейчас вдаваться в подробности, так как эта заметка и так будет довольно длинной. Если вы хотите узнать больше о формуле массива, то напишите об этом в комментариях и я раскрою тему подробнее в одной из следующих заметок.
Итак, давайте решим задачу.
Как и в прошлый раз, воспользуемся функцией ИНДЕКС, которая возвращает значение ячейки, заданного номером строки и номером столбца.
Первый ее аргумент - массив значений. Выбираем все ячейки с именами сотрудников.
Далее мы должны указать второй аргумент - номер строки, из которой будет возвращено значение. Нам нужно выводить значение из столбца A той строки, в которой значение в столбце B равно вычисленному значению максимального возраста (находится в ячейке F1), и тут идеально подойдет функция ЕСЛИ - если вычисленное значение из ячейки F1 равно одному из значений диапазона B2:B6, то нам нужно вывести номер этой строки. Определить номер строки довольно просто и в этом нам поможет функция СТРОКА, которая выводит фактический номер строки, определяемый адресом ячейки.
Так, например, пустая функция СТРОКА() выведет номер строки Экселя, в которой находится сама эта формула. Мы же в формуле должны вывести номер строки из диапазона значений и у нас в первой строке Экселя находится заголовок таблицы, учитывать который не нужно. Поэтому из полученного номера строки вычтем единицу, чтобы исключить строку заголовка.
Ну а в случае когда условие функции ЕСЛИ не выполняется выведем пустую строку, для этого укажем две кавычки. Итоговая формула будет выглядеть так:
=ИНДЕКС(A2:A6;ЕСЛИ(B14=B2:B6;СТРОКА(A2:A6)-1;""))
Вроде бы формула готова, но она работать не будет. Как я уже сказал, мы должны использовать формулу массива и поэтому необходимо подготовить формулу соответствующим образом.
Думаю, что проще будет объяснять уже на готовой формуле. Отличаться она будет лишь дополнительной функцией НАИМЕНЬШИЙ, которой мы «обернем» наше условие.
Эта функция возвращает k-ое наименьшее значение во множестве данных. Множество данных определяется результатом функции ЕСЛИ, а вот аргумент k, нужно будет вычислять, так как он должен изменяться.
Для его вычисления снова воспользуемся функцией СТРОКА, но перед этим давайте разберемся с функцией НАИМЕНЬШИЙ, так как она не самая простая для понимания.
Рассмотрим ее на таком примере - введем диапазон значений 1, 2, 3, 4, 5 и укажем формулу:
То есть нам нужно вывести третье наименьшее значение в диапазоне.
Логично, что в диапазоне чисел третье наименьшее равно 3. Если же мы изменим в диапазоне 3 на 1, то и результат также изменится - на 2.
То есть функция проходит весь диапазон значений и, выстраивает их по возрастанию, а затем отсчитывает k-ое (в нашем примере третье) значение по списку.
Если изменить в диапазоне 2 на 1, то функция вернет единицу, так как третье наименьшее равно ей.
Надеюсь, работа этой функции прояснилась.
Теперь возвращаемся к нашей формуле. Выглядеть она будет так:
=ИНДЕКС(A2:A6;НАИМЕНЬШИЙ(ЕСЛИ(B14=B2:B6;СТРОКА(A2:A6)-1;"");СТРОКА()-1))
Здесь формула СТРОКА()-1 вычисляет аргумент k для функции НАИМЕНЬШИЙ.
Как я уже упоминал, пустая функция СТРОКА возвращает номер строки с формулой. Так как у нас формула находится во второй строке Экселя, то мы можем использовать это значение для вычисления соответствующего аргумента k. Чуть дальше мы рассмотрим работу формулы и вам станет понятен принцип вычисления.
Чтобы сделать формулу формулой массива необходимо нажать не Enter, а сочетание клавиш Ctrl+Shift+Enter.
На то, что формула стала формулой массива указывают фигурные скобки, в которые она была заключена.
Это не текстовые скобки, то есть нельзя их просто набрать с клавиатуры. Эти скобки появляются лишь при нажатии сочетания клавиш Ctrl+Shift+Enter и они указывают на то, что программа воспринимает данную формулу, как формулу массива.
Вы видите, что в ячейке F2 появился правильный результат.
Давайте проанализируем формулу с помощью соответствующего инструмента со вкладки Формулы.
Здесь мы можем по шагам проходить все вычисления в формуле и таким образом разобраться в порядке вычислений и в получаемых значениях.
Так как мы имеем дело с формулой массива, то на выходе будем иметь не одно значение, а массив данных, что мы сейчас и увидим.
В первую очередь проверяется условие в функции ЕСЛИ (текущее вычисление подчеркивается).
Мы видим, что условие выдало пять ответов, то есть массив значений, равный по размеру диапазону ячеек с именами сотрудников. Из пяти ответов только последний ИСТИНА, что мы можем также подтвердить, взглянув на первоначальный диапазон значений, в котором пока только у одного сотрудника возраст равен максимальному.
Далее на базе полученных значений функция СТРОКА выдает массив с номерами строк.
Поскольку условие у нас выполняется только в последней строке, то мы получим четыре пустых значения (кавычки) и цифру 5, соответствующую пятой строке массива данных:
Переходим к функции НАИМЕНЬШИЙ, которая должна вернуть нам первое наименьшее значение. И как раз для того, чтобы автоматически вычислять это значение и была вставлена формула СТРОКА()-1. Мы изначально вставили формулу во вторую строку Экселя, поэтому она выдаст единицу, а значит функция НАИМЕНЬШИЙ выдаст первое наименьшее значение, которое будет равно 5.
Соответственно, функция ИНДЕКС выдаст значение, находящееся в пятой строке столбца А, а это и есть имя сотрудника с максимальным возрастом:
Чтобы получить диапазон значений нужно размножить эту формулу автозаполнением, но мы столкнемся с проблемой.
Во-первых, в изначальной формуле использовались относительные ссылки и при автозаполнении это проявилось, то есть диапазоны «съехали».
Во-вторых, функция выводит сообщение #ЧИСЛО! из-за особенностей вычислений. Давайте разберемся в этой ситуации.
Во-первых, изменим ссылки на абсолютные с помощью клавиши F4. Для этого при редактировании формулы устанавливаем текстовый курсор в ссылку и нажимаем F4.
Не забудем в конце нажать сочетание Ctrl+Shift+Enter, чтобы сделать формулу формулой массива.
Теперь снова автозаполнением протянем формулу на весь диапазон и проанализируем вычисление во второй строке. На определенном этапе в функции НАИМЕНЬШИЙ мы будем искать второе наименьшее значение в диапазоне:
Но в диапазоне есть только одна цифра и именно поэтому появляется ошибка #ЧИСЛО!, поскольку формула возвращает недопустимое значение.
Чтобы избавиться от этой ошибки воспользуемся функцией ЕСЛИОШИБКА. Она работает следующим образом - если вычисление по формуле проходит без ошибок, то возвращается вычисленное значение, а если возникает ошибка, то функция вернет значение, которое мы сами укажем. То есть мы эту функцию пишем в самом начале формулы и затем добавляем еще одно условие - двойные кавычки. Это означает, что если вычисления приведут к ошибке, то в ячейке ничего не отобразится.
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$14=$B$2:$B$6;СТРОКА($A$2:$A$6)-1;"");СТРОКА()-1));"")
Делаем формулу формулой массива с помощью сочетания клавиш и вновь протягиваем ее по диапазону. Ошибки исчезли.
Ну а теперь проверим работу формулы, изменив возраст у сотрудников.
С моей точки зрения, наиболее сложный для понимания элемент формулы - это функция НАИМЕНЬШИЙ и вычисление аргумента k. Поэтому предлагаю посмотреть видео на эту тему, в котором более детально изложен материал:
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
