7. Полезняшки Excel

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

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

При создании формул массива, операторы массива могут значительно увеличить время расчета. В этой короткой главе рассматриваются два примера, в которых обычная формула справляется с задачей значительно быстрее формулы массива.

Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Подробнее »Глава 12. Выборка из диапазона дат с помощью критерия в ином формате

Один посетитель сайта (Валерий) недавно спросил: «Есть ли инструмент поиска листа в книге по его названию». Возможное решение – просто выбрать лист из списка (рис. 1). Для этого кликните правой кнопкой мыши в левом нижнем углу экрана на одной из кнопок прокрутки.

Рис. 1. Выбор листа из списка

Подробнее »Excel. Создание указателя листов в книге

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

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

В предыдущих главах вы уже использовали И критерий при написании формул. Например:

Рис. 10.24 – суммирование с использованием двух критериев
=СУММЕСЛИМН(C8:C19;A8:A19;">="&A2;A8:A19;"<="&B2;B8:B19;C2)

Рис. 10.25 – также суммирование с использованием двух критериев =БДСУММ(A7:C19;C7;A1:C2)

Рис. 4.39 – нахождение минимума на основе двух критериев
=АГРЕГАТ(15;6;$C$3:$C$13/(($A$3:$A$13<>$F$2)*($B$3:$B$13=E5));1)

Рис. 10.26 – суммирование с использованием трех критериев; формула массива, основанная на булевой логике с И критерием: =СУММПРОИЗВ(--(A8:A19>=A2);--(A8:A19<=B2);--(B8:B19=C2);C8:C19)

Во всех четырех формулах И критерий означает, что все условия, или логические тесты должны быть удовлетворены для включения данных в расчет. В этой главе мы подробно рассмотрим также ИЛИ критерий.

Булеву алгебру предложил в 1854 году Джордж Буль. Логическими (булевыми) называют тип данных, которые имеет только два варианта значений: ИСТИНА и ЛОЖЬ (или 1 и 0).

ИСТИНА = 1, ЛОЖЬ = 0

Подробнее »Глава 11. Булева логика: критерии И, ИЛИ

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

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

Функция СУММПРОИЗВ имеет так много удивительных применений в Microsoft Excel, что ей не зазорно посвятить целую главу. Прелесть этой функции в том, что она без применения Ctrl+Shift+Enter способна:

• перемножить два и более массива и сложить результаты умножения;
• сложить элементы одного массива.

Как уже неоднократно упоминалось ранее, если у вас есть выбор между двумя одинаково эффективными формулами, одна из которых требует нажатия Ctrl+Shift+Enter, а вторая – нет, следует выбрать вторую. Вот почему использование СУММПРОИЗВ зачастую более предпочтительно, чем СУММ.

Вспомним примеры использования СУММПРОИЗВ, рассмотренные в предыдущих главах:

Рис. 2.14 – сумма разностей двух столбцов =СУММПРОИЗВ(D3:D6-C3:C6)
Рис. 3.4 – сумма произведений столбца на константу =СУММПРОИЗВ(B2:B5*(1-B7))
Рис. 6.6 – общее число символов в колонке =СУММПРОИЗВ(ДЛСТР(A2:A6))
Рис. 6.11 – сумма затрат, выбранных из таблицы просмотра функцией СУММЕСЛИ (для неотсортированных исходных данных) =СУММПРОИЗВ(СУММЕСЛИ(Е4:Е6,B3:В7,F4:F6))
Рис. 6.12 – (подобно рис. 6.11), но для отсортированных данных =СУММПРОИЗВ(ПРОСМОТР(B3:B7,Е4:F6))
Рис. 7.18 – сумма n наибольших значений в диапазоне В2:В8, где n размещено в ячейке D3 =СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B8;СТРОКА(ДВССЫЛ("1:"&D3))))
Рис. 7.33 – сумма затрат из некоторых столбцов (заданных массивом констант), выбранных по определенному товару функцией ВПР =СУММПРОИЗВ(ВПР(A7;A2:H4;{2;4;5;7;8}))

То, что вы познакомились с таким большим количеством примеров использования СУММПРОИЗВ, еще до главы, специально посвященной этой функции, подчеркивает, насколько она полезна!

Рис. 10.1. Поэлементное умножение двух диапазонов одинаковых размеров с последующим суммированием результатов умножения

Подробнее »Глава 10. Удивительные функции СУММПРОИЗВ и СУММЕСЛИМН

Если вы читаете эту заметку, вероятно вас тоже напрягает WordPress своей высокоинтеллектуальной опцией замены обычных двойных кавычек " на елочки « или » в зависимости от того, в начале или конце слова они используются. Я, как человек много лет проработавший в издательствах, не могу не порадоваться этой опции. Безусловно, это замечательный образчик типографики, русская традиция которой предписывает использование в качестве основной формы кавычек, именно елочки. Такую же автозамену применяет и MS Word… Вот только в Word’е всё же можно ввести и обычные кавычки, если этого требует контекст, а в WordPress это сделать нельзя (по крайней мере, у меня не получилось). Особенно неприятной эта особенность становится, если вы приводите фрагменты кода. Я, в частности, публикую формулы Excel и код VBA. Подобный код, скопированный со страниц Интернета, не будет работать именно из-за елочек. Вот, что у меня получилось совсем недавно:

Подробнее »Кавычки елочки: как заставить WordPress отображать обычные кавычки там, где вы этого хотите

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

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

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

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

Оба типа формул массива возвращают несколько элементов, подчиняясь одним и тем же правилам, подробно описанным в главе 8. Вот они вкратце:

1. Если формула массив вернет n элементов, и вы хотите отобразить n элементов в n ячейках, вы должны выбрать n ячеек до ввода формулы. Кроме того, необходимо учитывать, является ли массив одно- или двумерным. В первом случае вы выберите ряд или столбец ячеек, во втором – прямоугольную область.
2. Создайте свою формулу массива в активной ячейке.
3. Введите формулу массива нажатием Ctrl+Shift+Enter.

Функции массива являются встроенными функциями Excel, которые специально предназначены для возврата более чем одного элемента в более чем одну ячейку. Всего их восемь:

Первые три функции будут рассмотрены в этой главе; оставшиеся пять – в следующей.

Подробнее »Глава 9. Знакомство с функциями массива. ТРАНСП, МОДА.НСК и ТЕНДЕНЦИЯ

Это глава из книги: Майкл Гирвин. 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. Формула массива для создания последовательного ряда чисел; выделите пять ячеек, а затем введите формулу с помощью Ctrl+Shift+Enter

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

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

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

Ранее в этой книге вы познакомились с операторами массивов: математическими, сравнения и конкатенации. В этой главе рассматриваются операции с массивами, выступающими аргументами функций. Для начала задумайтесь, как работает аргумент функции одного элемента. Например, функция ДЛСТР подсчитывает количество символов в ячейке (рис. 6.1). Текстовый аргумент функция ДЛСТР ожидает, что в него будет помещен один элемент. Благодаря этому функция ДЛСТР обеспечивает однозначный ответ – 15 символов в ячейке А3.

Рис. 6.1. Текст в ячейке A3 – это один элемент, который помещен в текстовый аргумент

Подробнее »Глава 6. Использование массива, как аргумента функции

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

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

Оператор конкатенации – амперсанд & – позволяет объединить два элемента так, чтобы они стали единым целым. Вы можете соединять числа, текст (в кавычках), результаты формулы и др. Вы увидите, как использовать амперсанд, чтобы создать оператор массива, содержащий поиск по двум критериям.

На рис. 5.1 показаны исходные данные в диапазоне A2:C16 и отчет, который вы хотите создать в диапазоне E2:G10. Кросс-табличный отчет должен показывать объем продаж для каждого кода товара и соответствующего типа: левостороннего (L) и правостороннего (R). Для расчетов вы хотите создать формулу в ячейке F4, а затем скопировать ее в диапазон F4:G10. Проблема в том, что для каждой ячейки в диапазоне F4:G10, у вас есть два критерия поиска. Например, в ячейке F4, объем 30 взят на основе значения кода товара в ячейке E4 и значения L в ячейке F3. Стандартные функции поиска Excel запрограммированы так, чтобы искать только одно значение в одном столбце. Один из способов решения задачи – объединить два критерия поиска в один внутри формулы. (Предполагается, что таблица А2:С16 содержит по одной комбинации кода и типа товара, например, 2А35-2А36 типа L представлен в таблице одной строкой.)

Рис. 5.1. Цель – создать перекрестную таблицу, основанную на двух критериях поиска

Подробнее »Глава 5. Оператор конкатенации массивов