Перейти к содержимому

Глава 8. Формулы массива, возвращающие более одного значения

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                               Оглавление                               Следующая глава

До сих пор в этой книге, все операции с массивами приводили к результирующему массиву, использовавшемуся далее в формуле, которая в итоге возвращала одно значение. В этой главе вы узнаете, как создавать операторы массива, которые в качестве окончательного результата возвращают массив. Поскольку в одной ячейке может отображаться только один результат, чтобы вывести результирующий массив на лист Excel, вы должны сначала выделить требуемое число ячеек, а уже после этого ввести формулу массива с помощью Ctrl+Shift+Enter.

Два типа формул массива могут возвращать более одного значения сразу в несколько ячеек:

  • Созданные вами из различных элементов (вы узнаете о них в этой главе).
  • Встроенные функции Excel (вы узнаете о них в главе 9).

В таблице на рис. 8.1 ваша цель – пронумеровать строки в диапазоне A2:A6 от 1 до 5 и затруднить пользователям изменение и удаление отдельных ячеек. Вместо простого ввода чисел от 1 до 5, вы можете создать формулу массива, которая вернет массив чисел {1;2;3;4;5}. Одно из свойств массива – невозможность изменить/удалить отдельный его элемент.

Рис. 8.1. Формула массива для создания последовательного ряда чисел

Рис. 8.1. Формула массива для создания последовательного ряда чисел; выделите пять ячеек, а затем введите формулу с помощью Ctrl+Shift+Enter

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Шаги создания массива последовательных чисел:

  1. Выделите диапазон A2:A6, прежде чем вы начнете создавать формулу массива.
  2. Вы вводите формулу в активную ячейку (в нашем примере A2).
  3. Вы используете функцию СТРОКА, которая возвращает номер строки для каждой ячейки в диапазоне.
  4. Формула СТРОКА(A2:A6) возвращает массив {2;3;4;5;6}.
  5. СТРОКА(A2:A6)-СТРОКА(A2) возвращает массив {0;1;2;3;4}.
  6. СТРОКА(A2:A6)-СТРОКА(A2)+1 дает требуемый массив {1;2;3;4;5}.
  7. Введите формулу массива, нажав Ctrl+Shift+Enter.

Свойства массивов:

  1. Если вы попытаетесь удалить ячейки в диапазоне A2:A6 или вставить строку между 2 и 6, вы получите сообщение об ошибке (рис. 8.2). Это происходит потому, что при вводе формулы массива область становится единым блоком, который запрещает структурные изменения, такие как удаление ячеек, вставку строк или изменение содержимого отдельных ячеек.
  2. Для выделения массива встаньте на любую его ячейку и нажмите Ctrl+/.
  3. Для удаления массива выделите его целиком и нажмите Delete или встаньте на любую ячейку массива, нажмите Backspace, а затем Ctrl+Shift+Enter.
  4. Если вам нужно отредактировать формулу массива, встаньте на любую ячейку массива, измените формулу и нажмите Ctrl+Shift+Enter.
  5. Если вы выберите любую ячейку в диапазоне массива, в строке формул вы увидите одну и ту же формулу (в отличие от ситуации, когда вы «протягиваете» формулу). Заметьте, что вы оставили ссылки относительными (A2:A6 и А2), а могли сделать абсолютными (формуле массива всё равно).
  6. Функции массива (кроме ТРАНСП) могут быть помещены в аргументы других функций, рассчитывающие получить диапазон значений (например, СЧЁТ, МИН, MАКС); такая формула не будет требовать нажатия Ctrl+Shift+Enter.
  7. Формулы, подобные ={1:2:3:4:5}, или =43, или ="Любовь" можно ввести в диапазон (например, A1:A5), нажав Ctrl+Shift+Enter. Хотя при этом никакой операции с массивом и нет, вокруг формулы появятся фигурные скобки, а диапазон будет вести себя как массив.

Рис. 8.2. Отдельные части массива не могут быть изменены

Рис. 8.2. Отдельные части массива не могут быть изменены; во втором случае нажмите «Отмена» [1]

Чем полезна формула СТРОКА(диапазон)-СТРОКА(первая_ячейка)+1?

1. Казалось бы, создать последовательный массив чисел можно и проще (рис. 8.3). Однако, эта формула вернет неверный результат, если вставить строку выше таблицы (рис. 8.4) или переместить таблицу в новое место. Формула СТРОКА(диапазон)-СТРОКА(первая_ячейка)+1 не страдает от этого недостатка. В принципе, можно использовать и массив констант (рис. 8.5). Проблема в том, что если у вас много строк, ввод массива констант займет много времени. Формула СТРОКА(диапазон)-СТРОКА(первая_ячейка)+1 может быть использована для любого числа строк, а ее ввод не займет много времени, кроме того никогда не будет превышен лимит символов в формуле (8192).

Рис. 8.3. Более простая формула для создания массива последовательных чисел…

Рис. 8.3. Более простая формула для создания массива последовательных чисел…

Рис. 8.4. …однако, она вернет неверный результат, если вставить строку выше таблицы

Рис. 8.4. …однако, она вернет неверный результат, если вставить строку выше таблицы

Рис. 8.5. Для большого объема данных массив констант будет неудобен

Рис. 8.5. Для большого объема данных массив констант будет неудобен

2. Формула, показанная на рис. 8.6 также проще, но если вы переместите таблицу или вставите строку выше таблицы, резельтат будет неверным. Формула СТРОКА(диапазон)-СТРОКА(первая_ячейка)+1 не страдает от этого недостатка.

Рис. 8.6. Формула вернет неверный результат, если перенести таблица на новое место

Рис. 8.6. Формула вернет неверный результат, если перенести таблица на новое место

3. Обратите внимание, что формула {=СТРОКА(A2:A6)-СТРОКА(A2)+1} в ячейке A2 (рис. 8.1) ссылается на саму себя. Большинство формул, которые ссылаются сами на себя приводят к циклическим ссылкам. Но только не в нашем случае. Это происходит потому, что формула не включает математических расчетов; формула просто спрашивает: «К каким строкам я отношусь?»

4. Одна из причин, почему формула {=СТРОКА(A2:A6)-СТРОКА(A2)+1} всегда выдает правильный массив чисел, заключается в том, что она самодостаточна. Это означает, что независимо от того, куда вы перемещаете формулу, она всегда будет вычислять правильный массив чисел. Это свойство очень пригодится вам, когда вы будете использовать эту формулу в качестве элемента в сложных формулах для извлечения записей (см. главу 15).

Формула, рассмотренная выше создает порядковые номера в строках. Что делать, если вы хотите пронумеровать горизонтальный диапазон? Воспользуйтесь функцией СТОЛБЕЦ (рис. 8.7).

Рис. 8.7. Функция СТОЛБЕЦ позволяет создать горизонтальный массив чисел

Рис. 8.7. Функция СТОЛБЕЦ позволяет создать горизонтальный массив чисел

Если вас не беспокоит, что в ячейки с последовательными номерами будут внесены изменения, есть несколько других методов для создания нумерации. Мы рассмотрим пять из них.

Создание шаблона. После ввода любых двух чисел в две соседние ячейки (всё равно, по вертикали или горизонтали), вы можете захватить маркер заполнения (в правом нижнем углу выделенной ячейки) и перетащить его вниз или в сторону (рис. 8.8). Заметим, что вы также можете тащить вверх и влево (лишь бы хватало пустых ячеек).

Рис. 8.8. Цель – создать ряд с интервалом в три единицы

Рис. 8.8. Цель – создать ряд с интервалом в три единицы; выделите ячейки А1:А2, возьмитесь мышью за правый нижний угол и тяните так далеко, как вы хотите

Удержание клавиши Ctrl во время перемещения курсора. Если ввести число в ячейку, выделить ее, а затем нажать клавишу Ctrl, вы увидите небольшой значок «+» чуть выше и справа от перекрестия маркера заполнения (рис. 8.9). Если в таком положении потащить за маркер вправо или вниз, то числа увеличатся на единицу, а если влево или вверх – то уменьшатся на единицу.

Рис. 8.9. Удержание клавиши Ctrl во время перемещения курсора изменяет число на единицу

Рис. 8.9. Удержание клавиши Ctrl во время перемещения курсора изменяет число на единицу

Выделение ячейки с числом и пустой соседней. Если вы выделите ячейку с числом и пустую ячейку рядом с ней (всё равно, слева или справа), а затем потяните с помощью курсора мыши вниз, число будет увеличиваться на единицу, а пустая ячейка останется пустой (рис. 8.10). Этот трюк также работает, если вы выделите пустую ячейку под (над) числом и потащите курсор в сторону. Опять же, движение вниз и вправо увеличивает, а вверх и влево – уменьшает на единицу.

Рис. 8.10. Выделите ячейку с числом и пустую ячейку рядом с ней

Рис. 8.10. Выделите ячейку с числом и пустую ячейку рядом с ней; перетаскивание изменяет значение на 1

Инструмент Заполнить (Прогрессия). Если вам нужно заполнить столбец цифрами от 0 до 360, вам не покажется удачным решение напечатать в А1 и А2 числа 0 и 1, а затем перетащить указатель мыши вниз на 360 строк. Используйте инструмент Excel Заполнить. Введите 0 в ячейку A1, выделите ячейку A1, перейдите на закладку Файл (у меня Excel2013) в группу Редактирование и кликните справа от пиктограммы Заполнить; в раскрывающемся списке выберите Прогрессия (рис. 8.11). В открывшемся окне Прогрессия установите переключатель Расположение «по столбцам», Тип «арифметический», Шаг – 1, Предельное значение – 360 (рис. 8.12). Для больших диапазонов этот метод – лучший выбор!

Рис. 8.11. Заполнение с помощью прогрессии

Рис. 8.11. Заполнение с помощью прогрессии

Рис. 8.12. Окно Прогрессия

Рис. 8.12. Окно Прогрессия

Примечание. Вы можете открыть диалоговое окно Прогрессия иначе. Введите 0 в ячейку А1, выделите ячейку и правой кнопкой мыши перетащите вниз на одну ячейку, а затем обратно на одну ячейку вверх; отпустите правую кнопку мыши; выберите Прогрессия из контекстного меню.

На рис. 8.13 показаны еще две формулы, позволяющие создавать ряд чисел с приращением 1. Эти формулы используют расширяющийся диапазон, где ссылка на начало диапазона абсолютная, а на конец диапазона – относительная. Как и выше, несмотря на то, что формула ссылается на саму себя, ошибка циклической ссылки не возникает.

Рис. 8.13. Формулы, использующие расширяющийся диапазон

Рис. 8.13. Формулы на основе расширяющегося диапазона; протаскивая формулу по столбцу (слева) или по ряду (справа), вторая ссылка возрастает, а вместе с ней увеличивается возвращаемое формулой число

При создании последовательности чисел в большом диапазон ячеек, важное различие между формулой массива СТРОКА(диапазон)-СТРОКА(первая_ячейка)+1 и формулой СТРОКА(A$1:A1) заключается в том, что формула массива работает значительно быстрее (например, 10 000 строк формула массива пересчитывает за 0,004 с, а формула СТРОКА(…) за 0,14 с.

Функции, создающие последовательность чисел, довольно широко используются, поэтому остановимся еще на двух их разновидностях. Рис. 8.15 и 8.17 демонстрируют формулы, а рис. 8.16 и 8.18 показывают логику формул. На рис. 8.19 показано практическое применение этих формул.

Рис. 8.15. Формула приращения чисел

Рис. 8.15. Формула приращения чисел возвращает {1:1:1:2:2:2…}

Рис. 8.16. Логика работы формулы

Рис. 8.16. Логика работы формулы =ЦЕЛОЕ((ЧСТРОК(A$1:A1)-1)/3)+1

Рис. 8.17. Формула приращения чисел

Рис. 8.17. Формула приращения чисел возвращает {1:2:3:1:2:3…}

Рис. 8.18. Логика работы формулы

Рис. 8.18. Логика работы формулы =ОСТАТ(ЧСТРОК(A$1:A1)-1;3)+1. Функция ОСТАТ возвращает остаток от деления на 3; например, 3/3 имеет в остатке ноль, а 4/3 имеет в остатке 1

Рис. 8.19. Функция ИНДЕКС

Рис. 8.19. Функция ИНДЕКС преобразует таблицу в столбец, извлекая строку за строкой; в аргументе номер_строки используется формула ЦЕЛОЕ((ЧСТРОК(A$1:A1)-1)/3)+1, а в аргументе номер_столбца – ОСТАТ((ЧСТРОК(A$1:A1)-1);3)+1

Рис. 8.20–8.23 демонстрируют еще один пример формулы массива, которая возвращает несколько значений, а также не позволяет изменять/удалять содержимое отдельных ячеек. Рис. 8.20 показывает обычную формулу (не формулу массива), которая вычисляет номер квартала по дате.

Рис. 8.20. Обычная формула, которую можно «протянуть» вдоль столбца

Рис. 8.20. Обычная формула, которую можно «протянуть» вдоль столбца

Если вы хотите преобразовать эту формулу в формулу массива, которая возвращает несколько значений и предотвращает удаление ячеек, вы можете выделить все ячейки и ввести похожую формулу, заменив ссылку на одну ячейку (А2) в аргументе функции МЕСЯЦ на ссылку на диапазон (А2:А4). Ввод формулы потребует нажатия Ctrl+Shift+Enter (рис. 8.21). Рис. 8.22 показывает готовую формулу, а на рис. 8.23 отражает логику работы формулы.

Рис. 8.21. Введите формулу массива

Рис. 8.21. Введите формулу массива в диапазоне А2:А4

Рис. 8.22. Введите формулу с помощью Ctrl+Shift+Enter

Рис. 8.22. Введите формулу с помощью Ctrl+Shift+Enter

Рис. 8.23. Конструкция МЕСЯЦ и ОКРВВЕРХ

Рис. 8.23. Функция МЕСЯЦ возвращает число от 1 до 12; функция ОКРВВЕРХ округляет число до кратного трем; деление на 3 дает порядковый номер квартал

В заключение главы приводится пример формулы массива, которая возвращает несколько значений для дальнейшего использования в функции ВПР (рис. 8.24). Если цель – создание таблицы подстановки, но вас беспокоит возможность случайного удаления ячейки, вы можете создать массив констант и ввести его в диапазон A2:B4. Для начала выберите любую ячейку и создайте ссылку на выделяемую таблицу (рис. 8.25). Нажмите F9, чтобы вычислить содержимое ячейки D4 (рис. 8.26), а затем Ctrl+C, чтобы скопировать это содиржимое в буфер памяти и Esc, чтобы оставить ячейку D4 пустой (массив констант остался в буфере обмена). Выделите диапазон А2:В4 (нет необходимости удалять содержимое этого диапазона), введите знак равенства, а затем нажмите Ctrl+V, чтобы вставить массив констант из буфера обмена (рис. 8.27). Введите массив в диапазон А2:В4 нажатием Ctrl+Shift+Enter (рис. 8.28). Вуаля

Рис. 8.24. Обычная функция ВПР

Рис. 8.24. Обычная функция ВПР

Рис. 8.25. Введи в ячейку знак =, а затем выделите диапзон

Рис. 8.25. Введи в ячейку знак =, а затем выделите диапзон

Рис. 8.26. Нажмите F9, чтобы вычислить содержимое ячейки D4

Рис. 8.26. Нажмите F9, чтобы вычислить содержимое ячейки D4, а затем Ctrl+C, чтобы скопировать это содиржимое в буфер обмена

Рис. 8.27. Выделите диапазон

Рис. 8.27. Выделите диапазон А2:В4, введите =, а затем нажмите Ctrl+V, чтобы вставить массив констант из буфера обмена

Рис. 8.28. Введите массив в диапазон нажатием клавиш Ctrl+Shift+Enter

Рис. 8.28. Введите массив в диапазон А2:В4 нажатием клавиш Ctrl+Shift+Enter

С помощью функции массива вы создали таблицу подстановки, которая соответсвует исходной, но не может быть случайно повреждена.

С помощью Ctrl+Shift+Enter вы можете создать формулу массива, даже если в формуле не содержится никакого оператора. Например, если вы выделите диапазон ячеек A1:A5 и введете формулу в активной ячейке ={1;2;3;4;5}, или =43, или ="Любовь", а затем нажмете Ctrl+Shift+Enter, то будет создана «своего рода формула массива». Диапазон A1:A5 будут содержать формулу с фигурными скобками; диапазон будут действовать как единое целое: в нем нельзя юудет изменить/удалить никакую его часть. При этом формула не содержит никаких операторов массива (например, умножения или конкатенации). Достаточно нажать Ctrl+Shift+Enter, чтобы ввести формулу массива в несколько ячеек.

 


[1] Мне не понятно, зачем Excel предлагает две кнопки: Ok и Отмена; всё равно по кнопке Ok ничего сделать нельзя, разве что ввести исходную формулу массива 🙂

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *