В этом разделе представлено несколько полезных примеров использования формул массива. Большинство приведенных формул возвращает несколько или все значения диапазона, реорганизованные в определенном порядке.
Извлечение положительных значений из диапазона
Представленная ниже формула массива работает с одномерным вертикальным массивом, имеющим имя Данные. Формула массива расположена в диапазоне такого же размера, что и Данные. Для извлечения из диапазона Данные только положительных значений (нулевые и отрицательные значения игнорируются) используется следующая формула массива:
{=ИНДЕКС(Данные;НАИМЕНЬШИЙ(ЕСЛИ(Данные>0;
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))));
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))))}
Как видно на рисунке, эта формула работает, но имеет некоторые недостатки. Диапазон Данные находится в ячейках А5:А24, а формула массива введена в диапазон С5:С24. Однако для ячеек, которые не содержат значения, формула массива возвращает сообщение об ошибке #ЧИСЛО!.
Модифицированная формула массива, введенная в диапазон ячеек Е5:Е24, использует функцию ЕСЛИОШИБКА во избежание сообщений об ошибках.
{=ЕСЛИОШИБКА(ИНДЕКС(Данные;НАИМЕНЬШИЙ(ЕСЛИ(Данные>0;
ДВССЫЛ((“1:”&ЧСТРОК(Данные))));
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))));“”)}
Извлечение непустых ячеек из диапазона
Формула этого раздела работает с одномерным вертикальным массивом Данные. Формула массива вводится в такой же по размерности диапазон, что и Данные, и возвращает только непустые ячейки из этого диапазона.
{=ЕСЛИОШИБКА(ИНДЕКС(Данные;
НАИМЕНЬШИЙ(ЕСЛИ(Данные<>0;
ДВССЫЛ((“1:”&ЧСТРОК(Данные))));
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))));“”)}
В более старых версиях программы (до Excel 2007) можно использовать следующую формулу:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(Данные<>“”;
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))));
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))));“”;
ИНДЕКС(Данные;НАИМЕНЬШИЙ(ЕСЛИ(Данные<>“”;
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))));
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))))))}
Изменение порядка следования элементов в диапазоне на противоположный
Представленная ниже формула массива работает с одномерным вертикальным массивом Данные. Она расположена в диапазоне, имеющем такой же размер, что и Данные. Для возвращения массива с противоположным порядком следования элементов используется следующая формула:
{=ЕСЛИ(ИНДЕКС(Данные;ЧСТРОК(Данные)-
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))+1)=“”;“”;
ИНДЕКС(Данные;ЧСТРОК(Данные)-
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))+1))}
Присвоим диапазону А4:А13 имя Данные, а формулу массива введем в диапазон С4:С13. На рисунке продемонстрирована работа представленной выше формулы массива.
Динамическая сортировка значений диапазона
Предположим, существует рабочий лист с одномерным вертикальным диапазоном Данные. Для сортировки значений массива по убыванию воспользуемся следующей формулой массива. Следует заметить, что данная формула работает только с числовыми значениями, но не с текстом.
{=НАИБОЛЬШИЙ(Данные;СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))))}
На рисунке ниже показан пример; диапазон ввода находится в столбце А; формула массива введена в столбец С.
Эта формула может оказаться полезной, если требуется отсортировать данные немедленно. Начните с присвоения диапазону данных имени Данные. Затем введите формулу в другой диапазон данных, размещенный в тех же строках.
Если в исходной ячейке не содержится данных, формула массива отображает значение ошибки #ЧИСЛО!. В следующей, модифицированной формуле используется функция ЕСЛИОШИБКА. Она позволяет избежать отображения на экране сообщения об ошибке. Эта формула используется в столбце Е на рисунке.
{=ЕСЛИОШИБКА(НАИБОЛЬШИЙ(Данные;
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))));“”)}
Эта формула должна вводиться в такой же по размеру диапазон, что и Данные. Она тоже сортирует только числовые значения.
Для сортировки значений Данные по возрастанию можно использовать следующую формулу:
{=НАИМЕНЬШИЙ(Данные;СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные))))}
Возвращение списка уникальных значений диапазона
Если существует одномерный диапазон с именем Данные, для вывода списка уникальных элементов этого диапазона можно воспользоваться следующей формулой:
{=ИНДЕКС(Данные;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(Данные;
Данные;0)=СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)));
ПОИСКПОЗ(Данные;Данные;0); “”);
СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))))}
Эта формула не работает, если диапазон Данные содержит хотя бы одну пустую ячейку. В незаполненных ячейках формулы массива будет отображаться сообщение об ошибке #ЧИСЛО!. На рисунке представлен пример применения формулы массива. Диапазон А5:А23 имеет имя Данные, а формула массива введена в диапазон С5:С23. Обратите внимание, что в незаполненных ячейках отображается ошибка #ЧИСЛО!.
Чтобы избежать отображения значения ошибки, лучше воспользоваться функцией ЕСЛИОШИБКА.
{=ЕСЛИОШИБКА(ИНДЕКС(Данные;НАИМЕНЬШИЙ(ЕСЛИ
(ПОИСКПОЗ(Данные;Данные;0)=СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)));
ПОИСКПОЗ(Данные;Данные;0); “”);СТРОКА(ДВССЫЛ(“1:”&ЧСТРОК(Данные)))));“”)}
На рисунке эта формула используется в столбце Е.
Отображение календаря в диапазоне
На рисунке представлен результат одной из формул, отображающей “живой” календарь в диапазоне ячеек. Если изменить дату верхней части диапазона, календарь автоматически отобразит даты соответствующего месяца и года.
Для создания приведенного выше календаря в диапазоне В2:Н9 выполните следующие действия.
1. Выделите диапазон ячеек В2:Н2 и выберите команду Главная→Выравнивание→Объединить и поместить в центре.
2. Введите дату в объединенный диапазон. День месяца в данном случае не имеет значения.
3. Введите аббревиатуры дней недели в диапазон В3:Н3.
4. Выделите ячейки В4:Н9 и введите следующую формулу массива (не забывайте, что после ввода формулы массива следует нажимать клавиши <Ctrl+Shift+Enter>, а не просто <Enter>).
{=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(В2);МЕСЯЦ(В2);1))<>МЕСЯЦ(ДАТА(ГОД(В2);
МЕСЯЦ(В2);1)-(ДЕНЬНЕД(ДАТА((ГОД(В2);МЕСЯЦ(В2);1))-1)+
{0;1;2;3;4;5}*7+{1;2;3;4;5;6;7}-1);“”;ДАТА(ГОД(В2);
МЕСЯЦ(В2);1)-(ДЕНЬНЕД(ДАТА((ГОД(В2);МЕСЯЦ(В2);1))-1)+
{0;1;2;3;4;5}*7+{1;2;3;4;5;6;7}-1)}
5. Отформатируйте диапазон В4:Н9 с пользовательским форматом d. В таком формате на экране будут отображаться только даты. Для выбора этого формата воспользуйтесь категорией Дополнительно диалогового окна форматирования ячеек.
6. Скорректируйте ширину столбцов согласно своим предпочтениям.
Изменение месяца и года в ячейке В2 приводит к автоматическому обновлению календаря. После создания календаря можно скопировать его диапазон в любое место рабочего листа или книги.
Формула массива фактически возвращает даты, но ячейки отформатированы так, чтобы отображать только дни. Также обратите внимание, что в формулах массива используются массивы констант.
Рассмотрим еще одну версию приведенной выше формулы. Функция ЕСЛИ в исходной формуле проверяет каждую дату на предмет того, принадлежит ли она текущему месяцу. Если это не так, функция ЕСЛИ возвращает пустую строку.
Приведенную выше формулу массива можно несколько упростить, удалив функцию ЕСЛИ.
=ДАТА(ГОД(В2);МЕСЯЦ(В2);1)-(ДЕНЬНЕД(ДАТА(ГОД(В2);МЕСЯЦ(В2);1))-1)+
{0;7;14;21;28;35}+{0;1;2;3;4;5;6}
Эта версия формулы отображает также дни предыдущего и следующего месяцев. Предыдущая формула отображала ячейки, относящиеся к другим месяцам, пустыми.
