Excel вернуть массив по условию
Как узнать, является ли данная формула формулой массива? Что вообще она означает?
На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу "обрабатывает". Подтверждение формулы с помощью сочетания клавиш "Ctrl + Shitf + Enter" – это является для Excelя командой на выполнение (обработку данных) как массив вычислений. Тогда он используется в качестве аргумента функции и возвращает в качестве результата вычислений таблицу (массив данных).
Примеры формул массива и отличие от обычных формул в Excel
Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т.д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.
К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к "функциям массива" (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.
Когда формула является формулой массива, а когда обычной?
Для начала определимся как выглядит обычный массив значений в Excel. Это значения, которые находятся внутри фигурных скобок и разделены между собой точкой с запятой. Например:
– это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.
Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ() – они идентичны:
Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.
Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:
Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):
В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:
А так выглядят эти значения в массиве:
Например, чтобы прочитать этот массив и получить второе значение (сделать выборку значений) воспользуемся функцией:
Тоже самое что и:
Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив . Схематически вторую таблицу можно отобразить так:
Так же ее можно прочитать функцией:
В примере с функцией ЕСЛИ была введена формула массива только в одну ячейку, поэтому в результате получили только одно значение, соответствующее первому значению в таблице. Однако достаточно ввести формулу массива в диапазон ячеек, чтобы увидеть все значения массива результатов. Для этого выделяем диапазон из нескольких ячеек, нажимаем клавишу F2 (или заново вводим формулу вручную) и жмем CTRL+SHIFT+Enter.
В примере (рисунок ниже) видно, что таблица результатов содержит ровно четыре элемента, о которых я упоминал выше.
Примеры как использовать формулу массива в Excel
Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».
Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.
Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения "-32". Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:
0;$A$1:$A$4;0))' >
Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив . Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив . Затем эта таблица передается в качестве аргумента функции =СУММ(), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.
Как отличать формулу массива от обычной формулы
При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?
Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.
Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить "Ctr+Shift+Enter". Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.
Примеры вычислений и анализа формул массива
Каким образом можно просматривать и проверять значения промежуточных результатов расчета, например, содержание массивов, созданных в памяти компьютера и используемых для выполнения последующих действий? Ничего сложного! Пример 1:
Перейдите на ячейку с формулой, а затем в строке формул выделите в первом аргументе функции ссылку на диапазон ячеек:
Нажмите клавишу F9 (или "Пересчет" в правом верхнем углу меню "Формулы"), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:
- запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом - ";" (точкой с запятой).
Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).
То есть, созданный в памяти компьютера массив:
Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):
Нажмите клавишу F9 и вы получите массив итоговых результатов вычисления, которые используются для суммирования, как показано ниже:
То есть, созданный в памяти компьютера массив:
Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»
После чего нажмите на кнопку «Вычислить»:
В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:
Мы получили массив значений теперь уже для функции СУММ. Такой же, как и в примере 3.
Часто неопытные пользователи Excel возмущаются, что формула не работает. В конце концов оказалось, как легко догадаться, формулу в массиве вводили как обычную (просто Enter). Речь идет не о недоразумении, которое произошло, а о том факте, что у этих пользователей возникает вопрос: как избегать таких ошибок? Поэтому важно сразу во всем разобраться, чтобы в дальнейшем больше не задавать таких вопросов.
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "кузьмина" или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку "Перенос текста" (вкладка "Главная", группа "Выравнивание").
Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
Примеры этой главы предназначены для того, чтобы дать вам отправную точку. Но они, конечно же, не исчерпывают всех возможностей использования обычных функций Excel в сочетании с новыми свойствами динамических массивов. Всякий раз, когда функция ожидает скаляр, передача ей массива приведет к подъему. И в итоге функция вернет диапазон значений.
Использование СЕГОДНЯ() и ПОСЛЕД() для календаря
Допустим, вы используете СЕГОДНЯ() в качестве третьего аргумента функции ПОСЛЕД(). Тогда формула =ПОСЛЕД(6;7;СЕГОДНЯ()) в ячейке А4 вернет массив из 6 строк и 7 столбцов календаря, начинающегося сегодня:
Рис. 74. Использование СЕГОДНЯ() и ПОСЛЕД() для создания динамического календаря на ближайшие шесть недель
Добавьте формулу =ТЕКСТ(A4:G4; " ДДД " ) в ячейку А3 для заголовков дней недели. Обратите внимание, что эта формула разливается на 7 ячеек по горизонтали.
ТДАТА() и ПОСЛЕД() для вывода интервалов времени
Допустим вам нужно получить массив времени с интервалом 5 минут. Одна минута = 1/1440 суток. Поэтому, чтобы формула массива возвращала 5-минутные интервалы, используйте 1/288 в качестве аргумента шаг: =ПОСЛЕД(5;4;ТДАТА();1/288)
Рис. 75. 5-минутные интервалы, отсчитанные от текущего времени
Генерация последовательности букв алфавита
Функция СИМВОЛ() возвращает букву, основанную на коде ASCII. Например, СИМВОЛ(65) вернет A (английскую), а СИМВОЛ(90) – Z. Можно использовать ПОСЛЕД() внутри СИМВОЛ() для генерации последовательности букв, а затем передать этот массив в функцию ОБЪЕДИНИТЬ(), чтобы показать последовательность букв, разделенных дефисом (или любым другим символом). Формула в С3: =ОБЪЕДИНИТЬ( " — " ;ИСТИНА;СИМВОЛ(ПОСЛЕД(B6;1;КОДСИМВ(A6))))
Рис. 76. Генерация последовательных букв с разделителем
Заметим, что ASCII коды принимают значения от 0 до 255, поэтому если ввести строчную я, и указать число букв более 1, функция вернет ошибку, т.к., после я символов ASCII нет.
Несколько наибольших значений диапазона
Если вам нужно вернуть N наибольших значений диапазона, введите требуемое число в G1. Формула в G2 вернет N наибольших чисел, автоматически расширив область результата:
Рис. 77. Возвращает N наибольших значений диапазона
Несколько наименьших значений диапазона с изменением направления вывода
Усложним задачу. Нужно вывести N наименьших значений, но расположить их не по вертикали, а по горизонтали. Первое, что напрашивается, поместить функцию НАИМЕНЬШИЙ() внутрь функции ТРАНСП(): =ТРАНСП((НАИМЕНЬШИЙ(A1:E8;ПОСЛЕД(G1)))).
Рис. 78. Вернуть N наименьших значений в горизонтальном направлении
Посмотрите еще раз на рис. 78. Формула в G2 использует ПОСЛЕД(4) для возврата вертикального массива, который затем превращается в горизонтальный с помощью ТРАНСП(). Так почему же сразу не вернуть горизонтальный массив!? Для этого используйте внутри функцию ПОСЛЕД(1;G1). Эта формула используется в ячейке G3, и она намного короче формулы в G2.
Обратите также внимание, как в ячейках G6, G7 и G11 выводится текст формул. Это делается с помощью функции Ф.ТЕКСТ(). На рис. 78 формула в G6 сама является функцией динамического массива! Функция Ф.ТЕКСТ() ожидала на вход скаляр, а получила массив G2:G3, и разлилась на две ячейки G6 и G7.
Имитация сводной таблицы диапазоном с тремя формулами
Как автор книги Сводные таблицы в Microsoft Excel, я люблю хорошую сводную таблицу. Но менеджер Microsoft проекта динамических диапазонов Джо Макдэйд и MVP Excel Роджер Говьер указали, что три формулы, вполне способны заменить сводную таблицу. При этом не будет потребности в обновлении.
Чтобы построить отчет:
Рис. 79. Замена сводной таблицы тремя формулами; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Отображение чисел в двоичном, восьмеричном или шестнадцатеричном виде
Если нужно представить десятичные числа в двоичном, восьмеричном или шестнадцатеричном виде, используйте функции ОСНОВАНИЕ() и ПОСЛЕД(). Формула в А4: =ОСНОВАНИЕ(ПОСЛЕД(B1);;). У функции ОСНОВАНИЕ() три аргумента. Первый число здесь представлен функцией ПОСЛЕД(B1), которая задает массив целых чисел от 1 до 16. Второй – основание, здесь это массив констант . Третий – мин_длина, здесь также массив констант . Работа этой формулы дает пример попарного подъема.
Рис. 80. Генерация двоичных, восьмеричных и шестнадцатеричных чисел
Суммирование длин текстовых строк большого числа ячеек
Используйте формулу =СУММ(ДЛСТР(A2:A12)). Теперь для ее ввода не требуется Ctrl+Shift+Enter.
Рис. 81. Проверка длины ваших твитов
Текст по столбцам
Эта сложная формула разработана Риком Ротстайном. Благодаря использованию функции ПОСЛЕД(), вы можете разбить текстовую строку на слова с использованием лишь одной формулы в В1.
Рис. 82. Функция ПОСЛЕД() облегчает синтаксический анализ
Суммирование всех ВПР
В старом Excel это можно было сделать только с помощью старинной функции ПРОСМОТР(). На самом деле, это была одна из двух причин, по которой пользователи Excel добирались до функции ПРОСМОТР(). С динамическими массивами вы можете использовать ВПР. Вы всё еще будете использовать старую функцию ПРОСМОТР, когда у вас вектор поиска и вектор результатов ориентированы в противоположных направлениях. Единственная формула в Е9 выполняет сначала расчет массива ВПР, а потом суммирует отдельные значения: =СУММ(ВПР(C2:C26;E1:F6;2)). Обратите внимание, что поиск ВПР ведет приблизительно – четвертый аргумент опущен.
Рис. 83. Вычисление всех ВПР и последующее их суммирование
Объединение имени и фамилии
В этом примере используется попарный подъем, который описан в главе 9. Функция получает диапазон имен, один скаляр (пробел) и диапазон фамилий. Первый и последний вектор имеют одинаковый размер. Формула =ПРОПНАЧ(A2:A10& " " &B2:B10) возвращает массив 9×1.
Рис. 84. Объединение имени и фамилии и понижение регистра
Одна формула вместо таблицы данных «что если»
А в этом примере свойство трансляции (см. главу 9) используется для расчета ежемесячных платежей по автокредиту. Диапазон В4:F7 – динамический массив с единственной формулой в В4: =ПЛТ(C9/12;A4:A7;-B3:F3). Первый аргумент – процентная ставка за период. Обратите внимание, что используется простая относительная ссылка, так как не нужно протягивать формулу. Второй аргумент – общее число периодов выплаты по займу – вместо скаляра получает массив А4:А7. Третий аргумент – сумма кредита с обратным знаком – также вместо скаляра получает массив В3:F3. Благодаря трансляции Excel генерит массив из 4 строк и 5 столбцов и передает эти 20 значений в ПЛТ. Обратите внимание, условное форматирование отлично работает с динамическим массивом.
Рис. 85. Одна формула динамического массива в ячейке В4 заменяет таблицу данных
Условное форматирование на основе функции ЗНАК()
Вот еще один пример условного форматирования. Всякий раз, когда я добавляю набор значков «три треугольника» (который явно состоит из двух треугольников и прямоугольника… но я отвлекся), я использую функцию ЗНАК(), чтобы разделить все возможные значения на три группы: больше, равно и меньше. В примере ниже показаны цены закрытия акций компании DJI (ведущего китайского производителя дронов). Используется попарное поднятие для нахождения разницы двух массивов, смещенных один относительно другого на один день. Формула в C3: =ЗНАК(B3:B17-B4:B18) позволяет показать в какую сторону изменилась цена акций.
Рис. 86. Отражение динамики цен закрытия акций
Попарный подъем основан на двух массивах с одинаковым числом строк. В этом примере оба массива находятся в столбце B, но один начинается с B3 (для значения сегодняшнего дня), а другой – с B4 (для значения вчерашнего дня). На рисунке ниже показана формула в C3 в режиме редактирования, так что вы можете видеть два массива.
Рис. 87. Сравнение цен закрытия текущего и предыдущего дней
Это пример генератора анаграмм. Анаграмма – литературный прием, состоящий в перестановке букв слова или фразы, которая в результате дает другое слово или словосочетание. В оригинале автор использует английскую фразу, буквы которой переставляет случайным образом. Случай редко приводит к осмысленному результату, но позволяет получить что-то любопытное. При переводе я использовал слово из статьи Анаграммы.
Рис. 88. Генератор анаграмм с использованием динамических массивов
Использование ссылки на массив как части ссылки
Создание элементов календаря
Рис. 90. Генерация элементов календаря
Прогнозирование
Рис. 91. Прогноз на следующие 12 месяцев
Более сложное прогнозирование
В предыдущем примере мы вычислили массив из 12 ячеек, каждая из которых содержала месячный прогноз. В примере ниже этот массив помещается в функцию СУММ, чтобы вернуть прогноз на год. Формула усложняется, и для ее понимания, аргументы в нотации разнесены на отдельные строки:
Рис. 92. Годовой прогноз продаж
Функция ПРЕДСКАЗ.ETS ожидает скаляр, но вместо этого вы передаете ей массив из 12 значений, используя ПОСЛЕД() внутри КОНМЕСЯЦА(). Прогноз основан на фактических данных за 2007–2018 гг., расположенных в ячейках В2:B145. Формулу из E2 можно скопировать в E3:E6.
Возможно, вы спросите: почему нужно пять отдельных формул? Нельзя ли заменить ссылку на год – D2 ссылкой на массив ПОСЛЕД(5;1;2019)? Ответ – нельзя. С двумя вертикальными массивами в одной функции Excel попытается сделать попарный подъем. Но массивы имеют разный размер, поэтому вернется ошибка.
Транспонирование одного массива для предотвращения попарного подъема
Одна из стратегий предотвращения попарного подъема состоит в том, чтобы расположить годы по горизонтали. Одна формула в E3 возвращает массив 12*5:
Рис. 93. Если разместить годы по горизонтали, формула работает
Прогнозирование всех пяти лет в одной формуле
Если вы передадите ПРЕДСКАЗ.ETS в функцию СУММ, то получите суммарный прогноз продаж на 60 месяцев:
Рис. 94. Одна формула возвращает 5-летний прогноз
Данные в строке (столбце) разместить в диапазоне
Предположим, что у вас есть вектор чисел (D1:O1), и вы хотите разместить его в диапазон шириной N столбцов. Введите количество столбцов в D3. Тогда формула =ЕСЛИОШИБКА(ИНДЕКС(E1:AH1;ПОСЛЕД(15;D3));»») вернет диапазон шириной три столбца. Поскольку вы не знаете сколько понадобится строк, и чтобы избежать появления ошибок в нижней части диапазона, вы поместили вашу формулу внутрь функции ЕСЛИОШИБКА().
Рис. 95. Данные из строки в массив из трех столбцов
Использование динамических массивов для зависимой проверки
Функция проверки данных позволяет выбирать из выпадающего списка. Это прекрасно работает, пока кто-то не захочет иметь два списка. Причем так, чтобы элементы во втором списке зависели от того, что выбрано в первом. Это называется зависимой проверкой.
Рис. 96. Зависимая проверка
Как VBA работает с динамическими массивами?
Существуют миллионы строк кода VBA, которые используют синтаксис Range.Formula. Если и в вашем коде есть такие конструкции, Excel применит неявное пересечение, и вы не получите динамический массив, который мог бы разлиться. Код VBA будет работать также, как и до введения динамических массивов, и поэтому код останется рабочим.
Если вы пишете код для версии Excel, поддерживающей динамические массивы, используйте новый синтаксис Range.Formula2. Такой код создаст формулу, которая потенциально может разлиться. На рисунке ниже первая строка кода пыталась сгенерировать =ПОСЛЕД(10), но Excel автоматически добавил оператор @ неявного пересечения, так что код вернет в ячейку А1 только одно значение – единицу. Напротив, формула в L1 разольется и вернет динамический массив:
Рис. 97. Для вывода динамического диапазона используется новый синтаксис Range.Formula2
Аналогично код Range.FormulaR1C1 вернет одно значение, а Range.Formula2R1C1 – динамический диапазон.
Одним из основных способов поиска данных в таблицах Excel является функция ВПР, однако она имеет массу недостатков, и зачастую пользователи испытывают сложности при ее использовании. Связка функций ИНДЕКС и ПОИСКПОЗ открывает более широкие возможности для поиска данных в одной и даже нескольких таблицах сразу, на что неспособна ВПР.
Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум критериям в Excel
Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:
- Сколько очков заработала команда (поиск по названию) на данный момент.
- Суммарное значение очков, заработанных всеми командами.
- Сколько игр было сыграно какой-либо командой.
Вид исходной таблицы данных:
Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:
В открывшемся диалоговом окне необходимо выбрать «Тип данных:» - «Список» и указать в поле «Источник» диапазон ячеек:
Переходим в ячейку A13 и выполняем аналогичные действия только лишь указываем другую ссылку на диапазон в поле «Источник:»
Такой же выпадающий список следует создать и для ячейки A15.
Для подсчета общего количества очков в ячейке B11 используем формулу:
Для получения корректного результата выражение должно быть выполнено как формула массива. Функция СУММ получает массив ячеек в виде столбца таблицы, номер которого был определен функцией ПОИСКПОЗ по критерию поиска «Очки» (наименование столбца). Поскольку в качестве аргумента номер_строки функции ИНДЕКС было передано значение 0, будет возвращен весь столбец.
Количество сыгранных игр для каждой команды может быть рассчитано как сумма выигранных, сыгранных вничью и проигранных игр. Используем следующую формулу:
Данная формула аналогична предыдущей и также должна использоваться в качестве формулы массива. Благодаря списку, привязанному к ячейке A13, можно легко определить число сыгранных игр для любой команды:
Для определения количества очков используем формулу ИНДЕКС, в которой оба аргумента, указывающие номер строки и столбца, будут принимать значения, возвращаемые функцией ПОИСКПОЗ:
В результате мы получили значение по 2-м критериям:
Динамическое суммирование диапазона ячеек по критерию в Excel
Пример 2. Используя таблицу из предыдущего примера определить суммарное количество заработанных очков несколькими командами (задается опционально).
Вид таблицы данных:
Искомое значение может быть найдено с помощью следующей формулы:
Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при этом количество ячеек для расчета может быть задано с помощью критерия – выбранного названия команды. Функция ИНДЕКС может возвращать не только значение, хранящееся в искомой ячейке, но и ссылку на эту ячейку. Поэтому можно использовать запись типа E2:ИНДЕКС(…). В результате выполнения функция ИНДЕКС вернет ссылку на ячейку, и приведенная выше запись примет, например, следующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».
Проверим результат выборочного динамического суммирования столбца таблицы с верху вниз.
Сумма чисел в диапазоне E2:E7 и в ячейке B13 совпадает все ОК.
Подсчет количества рабочих дней в Excel по условию начальной даты
Пример 3. В таблице табеля рабочего времени хранятся данные о недавно принятых сотрудниках фирмы. Определить, сколько рабочих дней на текущий момент отработал любой из новых недавно принятых сотрудников фирмы.
Вид таблицы данных:
Как видно на рисунке в ячейке A10 снова используется выпадающий список, созданный по аналогичной схеме, описанной выше.
Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):
Первая функция ИНДЕКС выполняет поиск ячейки с датой из диапазона A1:I1. Номер строки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится первая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>""» выполняет поиск первой непустой ячейки для выбранной фамилии работника, указанной в ячейке A10 (<>”” – не равно пустой ячейке). Второй аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строки с выбранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>""» - номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), полученном в результате операции сравнения с пустым значением.
Примеры определения дат для нескольких сотрудников:
Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:
Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:
Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel
Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.
При необходимости можно получить целую строку или целый столбец, указав в качестве номера строки и столбца соответственно значение 0 (нуль). Для вывода полученных строки или столбца функцию ИНДЕКС необходимо использовать в качестве формулы массива.
Поскольку ПОИСКПОЗ возвращает относительную позицию элемента в диапазоне, то есть, номер строки или столбца, эта функция может быть использована как один или сразу два аргумента функции:
=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))
Такая формула используется чаще всего для поиска сразу по двум критериям.
Часто случается так что при считывании значений столбцов таблиц поиск значений для выборки следует выполнять по крайним правым столбцам. Excel предлагает несколько функций возвращающих значения ячеек находящиеся по левой стороне столбца, по которому и выполняется поиск.
Как применить формулу ИНДЕКС и ПОИСКПОЗ в Excel
Ниже на рисунке представлены города и области, в которых находятся магазины сети. Допустим, что после выбора пользователем названия области с выпадающего списка в ячейке G1 должны автоматически заполнится поля «Город» G2 и «№ магазина» G3:
Функция ИНДЕКС возвращает значение ячейки, находящееся в определенной строке и столбце указанного диапазона ячеек. В данном случае в аргументе функции как диапазон ячеек указана исходная таблица магазинов. Во втором аргументе указывается сначала номер строки, а в третьем – номер столбца. В формуле для поиска города значения считываются с первого столбца, поэтому последний аргумент содержит число 1.
Формула для поиска номера магазина по названию города возвращает значения из четвертого столбца:
Поэтому последний аргумент в функции ВПР равен числу 4.
Если диапазон ячеек не начинается с ячейки A1 аргументы, определяющие строку и столбец, не соответствуют номерам строк и столбцов рабочего листа Excel. Отсчет номеров производится, начиная от ячейки левого верхнего угла указанного диапазона (в данном примере это A2) в первом аргументе функции ИНДЕКС. Например, формула =ИНДЕКС(G2:P10;2;2) возвращает значение ячейки H3, которая находится во второй строке второго столбца диапазона G2:P10.
Чтобы получить номер соответствующей строки для функции ИНДЕКС необходимо использовать функцию ПОИСКПОЗ, которая возвращает позицию найденного значения в таблице. Функция ПОИСКПОЗ использует 3 аргумента в своем синтаксисе:
- Искомое значение – то что следует найти.
- Просматриваемый массив – одномерный массив или диапазон ячеек из одной строки или из одного столбца где должно быть найдено искомое значение.
- Тип сопоставления – точность соответствия (необязательный аргумент для заполнения), при точном соответствии указывается значение ЛОЖЬ или 0. Если требуется неточное совпадение значений, тогда ИСТИНА или 1.
В данном примере искомым значением выступает название области указана в ячейке G1. Данное значение формула ищет в списке областей диапазона C2:C11. Функция ПОИСКПОЗ поочередно проверяет все ячейки, пока не найдет строку «Свердловская», находящееся на 5-ой позиции. После чего данная функция возвращает значение 5, которое будет использовано как второй аргумент с номером строки для функции ИНДЕКС.
После возвращения результата через функцию ПОИСКПОЗ функция ИНДЕКС будет иметь все необходимые данные для отображения соответственного значения. Перейдет на 5-тую строку диапазона ячеек и выберет значение из первого столбца «Город» или из четвертого «№ магазина».
Как сделать выборку из списка формулой ПРОСМОТР в Excel
Формула из комбинации функций ИНДЕКС и ПОИСКПОЗ – это самая популярный тип формул для поиска значений в таблице Excel. Значительно реже используется функция ПРОСМОТР. В ее синтаксисе используется 3 аргумента:
- Искомое значение – значение позицию которого необходимо найти.
- Просматриваемый вектор – одномерный массив или диапазон ячеек из одной строки или из одного столбца где будет вестись поиск искомого значения.
- Вектор результатов - одномерный массив или диапазон ячеек из одной строки или из одного столбца из которого следует вернуть результат (необязательный для заполнения).
Внимание! Сразу же отметим недостатки функции ПРОСМОТР, а они весьма существенные:
- Нет возможности установить точное совпадение как в функциях ВПР, ГПР и ПОИСКПОЗ.
- Просматриваемый одномерный массив вектора должен быть отсортирован по возрастанию иначе функция будет возвращать ошибочные результаты как оказано на рисунке:
Поэтому сначала перед применением функции отсортируем просматриваемый диапазон вектора по возрастанию. Следующие две формулы предназначены для альтернативного поиска города:
И соответственного номера магазина:
Таблица пересортирована по диапазону C2:C11 по возрастанию и теперь все работает как надо.
Первые два аргумента функции ПРОСМОТР такие же, как и у функции ПОИСКПОЗ. Эти две функции работают по одному и тому же принципу. То есть ищет заданное значение в одном направлении вектора. Отличие в том, что она возвращает не позицию где находится найденная ячейка в диапазоне с нужным значением, а содержимое соответственной ячейки находящиеся на позиции (указанной в векторе результатов) от нее. Ну и не стоит забывать, что для функции ПРОСМОТР нужно сортировать таблицу по возрастанию просматриваемого диапазона и у нее нет аргумента точности совпадения при поиске значений.
Чтобы найти необходимый город, функция ПРОСМОТР сначала определяет, что строка «Свердловская» находится на 10-ой позиции (после сортировки таблицы) просматриваемого вектора по диапазону C2:C11. После формула чего возвращает содержимое 10-ой ячейки, но уже по вектору в диапазоне A2:A11. Аналогичным принципом функция ищет номер магазина, но там результирующий вектор – это уже диапазон D2:D11.
Читайте также: