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

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

Рубрика: 7. Полезняшки Excel

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

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

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

Читать полностью

Глава 11. Булева логика: критерии И, ИЛИ

Рубрика: 7. Полезняшки 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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. 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.1. Поэлементное умножение двух диапазонов одинаковых размеров с последующим суммированием результатов умножения

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

Рис. 1. Елочки в формулах Excel

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

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

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

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

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

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

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

Встроенные функции массива

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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. 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

Читать полностью

Глава 7. Массивы констант

Рубрика: 7. Полезняшки Excel

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

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

Ранее в этой книге вам неоднократно встречались массивы констант. Действительно, каждый раз, когда вы проводили оценку работы элементов формул, используя клавишу F9, вы видели пример массива констант. Например, в первой формуле массива, созданного в главе 2 (снова показан на рис. 7.1), вы можете выделить D2:D5-C2:C5, и нажав F9, увидеть результирующий массив {3;–8;7;6}. Однако, ранее, оценив массив, вы сразу же нажимали Ctrl+Z, чтобы оставить внутри формулы не жесткий код, а ссылку. Если бы вы не использовали Ctrl+Z, вы бы создали массив констант, который бы не изменялся при изменении исходных данных на листе. В этой главе вы узнаете о ситуациях, в которых использование массива констант дает преимущества.

Рис. 7.1. Выделите массив (слева), и, нажав F9, получите результирующий массив констант

Рис. 7.1. Выделите массив (слева), нажмите F9 (справа), и получите массив констант

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

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

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

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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. 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. Цель – создать перекрестную таблицу, основанную на двух критериях поиска

Читать полностью

Глава 4. Сравнение массивов и выборки по одному или нескольким условиям

Рубрика: 7. Полезняшки Excel

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

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

Выборки, основанные на одном или нескольких условиях. Ряд функций Excel используют операторы сравнения. Например, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. Эти функции осуществляют выборки на основе одного или нескольких условий (критериев). Проблема в том, что эти функции могут только складывать, подсчитывать количество, и находить среднее. А если вы хотите наложить условия на поиск, например, максимального значения или стандартного отклонения? В этих случаях, поскольку не существует встроенной функции, вы должны изобрести формулу массива. Нередко это связано с использованием оператора сравнения массивов. Первый пример в этой главе, показывает, как рассчитать минимальное значения при одном условии.

Воспользуемся функцией ЕСЛИ, чтобы выбрать элементы массива, отвечающие условию. На рис. 4.1 в левой таблице присутствуют столбец с названиями городов и столбец с временем. Требуется найти минимальное время для каждого города и поместить это значение в соответствующую ячейку правой таблицы. Условие для выборки – название города. Если вы используете функцию МИН, то сможете найти минимальное значение столбца В. Но как вы выберите только те числа, что относятся только к Окленду? И как вам скопировать формулы вниз по колонке? Поскольку в Excel нет встроенной функции МИНЕСЛИ, вам необходимо написать оригинальную формулу, совмещающую функции ЕСЛИ и МИН.

Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города

Читать полностью