Это заключительная глава книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Что, если перед вами огромная формула массива, и вы не можете понять, как она работает? Вспомним подходы, изложенные в предыдущих разделах.
Разбейте формулу на минимально возможные фрагменты и каждый фрагмент поместите в отдельную ячейку. Это особенно актуально, когда фрагмент формулы изменяется, при копировании формулы вдоль столбца (строки).
Рис. 23.0. Выделение фрагмента формулы, соответствующего аргументу номер_строки
Скачать заметку в формате Word или pdf, примеры в формате Excel
Почитайте Help Microsoft по конкретной функции. Обратите внимание на всплывающие подсказки, чтобы увидеть, какие аргументы выделены полужирным шрифтом.
Если формула массива размещена в одной ячейке, изучите, как работает формула шаг за шагом. Для этого пройдите по меню ФОРМУЛЫ –> Зависимости формул –> Вычислить формулу, или нажмите, и удерживайте клавишу Alt, а затем последовательно нажмите Л, Ч. Есть два недостатка этой опции: (1) иногда фрагмент формулы слишком большой для диалогового окна Вычисление формулы; и (2) иногда этот диалог не показывают все шаги вычисления или он показывает иной результат, чем, когда вы используете клавишу F9 в режиме редактирования формулы.
Войдите в режим редактирования формулы (просто кликните внутри строки формул). Выделяйте фрагменты формулы и жмите клавишу F9, чтобы оценить выделенный фрагмент. Фрагмент формулы можно также выделить, кликая на аргумент функции при появлении подсказки (рис. 23.0). Чтобы отменить оценку фрагмента и восстановить формулу, нажмите Ctrl+Z. Если вы не отмените оценку сразу же, и нажмете F9 два или более раз подряд, вы не сможете откатиться к первоначальному варианту. Это связано с тем, что в режиме редактирования существует только одна отмена. Для восстановления первоначальной формулы остается возможность выйти из режима редактирования, нажав Esc. Если фрагмент формулы, который вы пытаетесь оценить, содержит более 8192 символов, вы получите сообщение об ошибке. Этот способ более надежен, чем, описанный выше диалог Вычислить формулу.
Рассмотрим примеры использования перечисленных методов.
Найти первое значение в строке, содержащей пустые ячейки
На рис. 23.1 приведена формула массива, которая возвращает значение первой непустой ячейки в строке: {=ЕСЛИ(СЧЁТ(A3:D3);ИНДЕКС(A3:D3;ПОИСКПОЗ(ИСТИНА;A3:D3<>»»;0));»»)}.
Рис. 23.1. Поиск первой непустой ячейки в строке
Если вы не понимаете, как работает эта формула, вы можете разбить ее на фрагменты, и изучить их более пристально, и даже разместить их в отдельных ячейках. Войдите в режим редактирования формулы (кликните в строке формул или дважды кликните на ячейку), щелкните по формуле и посмотреть, какие фрагменты формулы соответствуют каким аргументам функций. Как показано на рис. 23.2, вы можете начать с первого аргумента первой функции формулы. Нажав аргумент лог_выражение во всплывающей подсказке, можно выделить фрагмент формулы (функция СЧЁТ), который соответствует этому аргументу. Если вы нажмете клавишу F9, то увидите, что СЧЁТ() возвращает значение 2. Отмените вычисление, нажав Ctrl+Z. Это означает, что СЧЁТ возвращает двойку в аргумент лог_выражение функции ЕСЛИ. Поскольку аргумент лог_выражение интерпретирует любое ненулевое число, как ИСТИНА (а ноль, как ЛОЖЬ), функция ЕСЛИ выберет значение_если_истина.
Рис. 23.2. В ячейке F3 нажмите аргумент лог_выражение во всплывающей подсказке функции ЕСЛИ, чтобы выделить фрагмент формулы
Далее нажмите на аргумент значение_если_истина (рис. 23.3). Видно, что в нем размещается функция ИНДЕКС. Нажав F9, вы увидите, что ИНДЕКС возвращает значение 23. (Не забудьте нажать Ctrl+Z, чтобы отменить вычисление.) Наконец, аргумент значение_если_ложь представлен пустой строкой («»).
Рис. 23.3. В аргументе значение_если_истина функции ЕСЛИ размещается функция ИНДЕКС
С функцией ЕСЛИ разобрались. Теперь изучим фрагменты функции ИНДЕКС. В режиме редактирования формулы кликните курсором где-то внутри функции ИНДЕКС, например, на первое А3, затем выделите аргумент номер_строки (рис. 23.4). Выделится функция ПОИСКПОЗ. Нажав F9, вы увидите, что она возвращает значение 1 (относительную позицию внутри массива А3:D3).
Рис. 23.4. Функция ПОИСПОЗ в аргументе номер_строки функции ИНДЕКС
Теперь обратитесь к аргументу просматриваемый_массив функции ПОИСКПОЗ (рис. 23.5). Нажав F9, вы увидите, что это массив значений ИСТИНА и ЛОЖЬ (рис. 23.6). Благодаря тому, что аргумент тип_сопоставления выбран равным нулю, ПОИСКПОЗ найдет первое значение, равное аргументу искомое_значение (ИСТИНА), и в нашем случае ПОИСКПОЗ вернет 1 (подробнее см. справку MS). Справку по функции также можно вызвать, кликнув на имени функции во всплывающей подсказке (рис. 23.7)
Рис. 23.5. Аргумент просматриваемый_массив функции ПОИСКПОЗ
Рис. 23.6. Нажав F9 вы увидите, что просматриваемый_массив возвращает значения ИСТИНА и ЛОЖЬ
Рис. 23.7. Клик на названии функции во всплывающей подсказке открывает справку
Чтобы еще лучше понять, как работает фрагмент формулы с функцией ПОИСПОЗ, разместите этот фрагмент в отдельной ячейке, и «протяните» формулу (рис. 23.8). Видно, что фрагмент подбирает относительную позицию первой значащей ячейки в строке. Когда чисел в строке нет, возвращается ошибка #Н/Д. Функция ИНДЕКС также вернет ошибку #Н/Д. Ничего страшного, так как функция СЧЁТ возвращает 0 (ЛОЖЬ), и функция ЕСЛИ не будет обращаться к аргументу значение_если_истина (которое в этом случае содержит ошибку #Н/Д), а обратится к аргументу значение_если_ложь, и вернет пустую строку («»).
Рис. 23.8. Разместите фрагмент формулы с функцией ПОИСПОЗ в ячейке G3 и скопируйте его вниз; этот фрагмент возвращает относительную позицию первой значащей ячейки в строке
После изучения формулы и отдельных ее фрагментов в режиме редактирования, можно вычислить элементы формулы шаг за шагом (опция ФОРМУЛЫ –> Вычислить формулу).
Еще один отличный трюк для понимания того, как работает формула – выделять последовательно элемент за элементом и нажимать клавишу F9. Не забудьте выйти из режима редактирования нажатием клавиши ESC, чтобы сохранить в ячейке первоначальную формулу. Поскольку клавиша ESC возвращает формулу в исходное состояние, прежде чем перейти в режим редактирования, завершите ввод формулы (нажмите Ctrl+Shift+Enter). Затем войдите в режим редактирования, и выполните указанный трюк (рис. 23.9).
Рис. 23.9. Завершите ввод формулы в ячейку (нажмите Ctrl+Shift+Enter), перейдите в режим редактирования, последовательно выбирайте аргументы функций, и нажимайте клавишу F9, чтобы вычислить фрагменты формулы шаг за шагом, затем нажмите клавишу ESC, чтобы выйти из ячейки с сохранением формулы
Последующие примеры предоставляют вам отличную возможность потренироваться в понимании формул массива.
Поиск заголовка столбца, связанного с первой непустой ячейкой в строке
На рис. 23.10 приведена формула, которая возвращает дату (заголовок столбца) связанную с первой непустой ячейкой в строке. Различия между этой формулой и той, что показана на рис. 23.1, заключаются в том, что аргумент массив функции ИНДЕКС указывает на заголовки столбцов и основан на абсолютных, а не относительных ссылках.
Рис. 23.10. Формула возвращает дату (заголовок столбца), связанную с первой непустой ячейкой в строке
Поиск по определенному столбцу и критерию
На рис. 23.11 показана формула, которая, во-первых, обращается к столбцу D (День 3), затем по критерию (Job 4) отбирает фамилии сотрудников, и размещает их в диапазоне А15:А21 (А24:А31 – для версий Excel, начиная с 2010). Обратите внимание на следующие особенности формулы:
- Функция СЧЁТЕСЛИ (в ячейке С12) работает с «правильным» диапазоном, отобранным функцией ИНДЕКС, размещенной в аргументе диапазон (см. главы 10 и 13).
- Поскольку аргумент номер_строки функции ИНДЕКС (в ячейке С12) опущен, функция возвращает столбец целиком (см. главу 13).
- Формулы извлечения данных подробно описаны в главе 15.
Рис. 23.11. Формула ищет в определенном столбце, и по критерию извлекает имена
Поиск самого длинного слова в столбце
На рис. 23.12 показана формула, которая ищет ячейку с наибольшим количеством символов. Это работает, как для текста, так и для чисел. Обратите внимание:
- Аргумент текст функции ДЛСТР ожидает одно значение, а мы «подсовываем» ему массив. Поэтому функция ДЛСТР возвращает массив.
- Фрагмент формулы МАКС(ДЛСТР(A6:A9)) возвращает значение в аргумент искомое_значение функции ПОИСКПОЗ.
- Функция ПОИСКПОЗ найдет относительное положение в массиве А6:А9 ячейки с наибольшей длиной текста; если есть несколько ячеек одинаковой длины, ПОИСКПОЗ вернет первое.
Рис. 23.12. Поиск ячейки с наибольшим числом символов
Формула для расчета процентилей с одним условием
На рис. 23.13 показано, как вычислить 90% процентиль теста СРА по всем школам и по каждой в отдельности. Значения в Е11:Е14 – граница, разделяющая данные на верхние 10% и нижние 90%.
Рис. 23.13. Рассчитан 90% процентиль для каждой школы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Формула для ранжирования значений с одним условием
Если нужно ранжировать не всю совокупность, а выборки по условию, подойдет формула, представленная на рис. 23.14. Считайте, что это функция РАНГЕСЛИ.
Рис. 23.14. Ранг студента в рамках факультета
Формула для расчета максимальной двухдневной рабочей нагрузки
Цель на рис. 23.15 – найти пару дней, дающую максимальную рабочую нагрузку. Всего рабочих дней семь, так что формула должна выбрать между шестью спаренными итогами. Функция СМЕЩ используется внутри функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ для создания шести двухдневных промежуточных итогов, максимум из которых затем следует выбрать.
Рис. 23.15. Найти пару дней, дающую максимальную рабочую нагрузку
Формула для расчета цены на основе нескольких скидок
На рис. 23.16 показана формула для расчета чистой стоимости на основе серии скидок (для начала значения скидок нужно извлечь из текстовой строки). Например, в ячейке D3 формула сведётся к умножению 0,8*0,95*0,9*0,8.
Рис. 23.16. Расчет цены со скидками
Максимум побед или поражений (смотря, чего больше)
Формула на рис. 23.17 одна из моих любимых. Это решение использует функцию ЧАСТОТА. Критерий ИЛИ позволяет провести подсчет в одном столбце или в другом. Дополнительные сведения о Булевой логике см. главу 11. Дополнительную информацию о функции ЧАСТОТА см. главу 16.
Рис. 23.17. Максимум побед или поражений (смотря, чего больше)
Формула массива не может ссылаться на весь столбец
В справке MS на эту тему говорится следующее: «Хотя в Excel можно создавать очень большие массивы, создать массив, использующий целый столбец или несколько столбцов, нельзя. Поскольку пересчет формулы массива, занимающего весь столбец ячеек, требует много времени, в Excel запрещено создавать подобные массивы.
Примечание. В Microsoft Office Excel 2003 и более ранних версиях Excel столбец состоит из 65 536 ячеек. В Microsoft Office Excel 2007 столбец состоит из 1 048 576 ячеек».
Используйте имена, чтобы избежать ввода с помощью Ctrl+Shift+Enter
В главе 13 говорилось, что, если вы создадите определенное имя, которое содержит формулу, требующую ввода с помощью Ctrl+Shift+Enter, а затем поместите это имя в формулу, последняя не будет требовать нажатия Ctrl+Shift+Enter. Вот крутой пример на эту тему.
На рис. 23.18 три И критерия в ячейках В3:D3. Цель формулы – извлечь записи, соответствующие критериям (это пример из главы 15). Отличие в этом примере заключается в том, что вы берете часть формулы, которая требует сочетание клавиш Ctrl+Shift+Enter и сохраняете его в определенном имени (RP). Теперь, когда вы используете определенное имя в формуле (в ячейках F16:I21), она не требует нажатия Ctrl+Shift+Enter.
Рис. 23.18. Если вы создадите определенное имя, которое содержит формулу, требующую ввода с помощью Ctrl+Shift+Enter, а затем поместите это имя в формулу, последняя не будет требовать нажатия Ctrl+Shift+Enter