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

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

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

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

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

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

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

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

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

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

Рис. 3.1. Формула массива суммирует разности между двумя массивами

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

Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. Рассмотрим пример. Допустим вы работаете в крупной торговой компании (продукты питания), и хотите проанализировать динамику расходов на заказ партий товаров (рис. 1).

Рис. 1. Исходные данные

Подробнее »Как научить Excel ссылаться на ячейку в сводной таблице, как на обычную

Формулы массива довольно редко используются и, кроме того довольно трудны. Тем не менее, похоже, вы готовы прочитать о них целую книгу! На это может быть несколько причин:

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

Так начинается книга Майкла Гирвина, целиком посвященная формулам массива. Надо заметить, что с недавних пор я начал использовать формулы массива, но всё еще чувствую себя новичком в этом деле. Я перечитал всё что смог найти на эту тему в русскоязычной литературе (см., например, Excel. Введение в формулы массива, Excel. Некоторые примеры использования формул массива), и не удовлетворившись, нашел вот такую книгу на английском:

Подробнее »Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel

Команда Специальная вставка (Paste Special) в Excel позволяет легко выполнять определенные манипуляции с данными в электронной таблице. В настоящей заметке показано, как с помощью команды Специальная вставка:

• вставлять в другую часть электронной таблицы только значения ячеек (не формулы);
• перемещать данные из столбцов в строки и наоборот;
• преобразовывать диапазон чисел, прибавляя, вычитая, деля и умножая каждое число из диапазона на заданную константу. [1]

1. Как переместить результаты вычислений (не формулы) в другую часть листа?

На рис. 1 диапазон ячеек Е4:Н9 содержит имена, число проведенных игр, общий счет и счет за игру для пяти 10–11-летних баскетболистов из Блумигнтона, штат Индиана. В диапазоне ячеек Н5:Н9 я использовал данные из диапазона F5:G9, чтобы подсчитать число очков, набранных за игру каждым ребенком.

Рис. 1. Исходные данные

Подробнее »Excel. Возможности команды Специальная вставка

Функция ДВССЫЛ (INDIRECT) — одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми. По сути, если в формуле есть раздел ДВССЫЛ со ссылкой на ячейку, эта ссылка обрабатывается как содержимое соответствующей ячейки. [1] Например (рис. 1), в ячейке С4 я ввел формулу =ДВССЫЛ(А4), и Excel возвратил значение, равное 6. Excel возвращает именно это значение, поскольку ссылка на А4 немедленно заменяется текстовой строкой В4. Следовательно, формула обрабатывается как =В4, что дает нам 6. По аналогии, если ввести в ячейке С5 формулу =ДВССЫЛ(А5), Excel вернет значение ячейки В5, то есть 9.

Рис. 1. Простой пример функции ДВССЫЛ

Подробнее »Excel. Примеры использования функции ДВССЫЛ (INDIRECT)

Впервые я применил функцию СМЕЩ когда начал использовать динамические диапазоны. Поясню. Если вы строите графики или сводные таблицы на основе данных, которые периодически обновляются, использование динамических диапазонов позволяет до минимума сократить ручной труд, связанный с обновлением таблиц и графиков (см., например, Автоматическое обновление сводной таблицы).

Формально функция СМЕЩ (OFFSET) применяется для создания ссылки на прямоугольный диапазон, которая представляет собой заданное количество строк и столбцов, отстоящих от ячейки или диапазона ячеек (см. Help MS Excel). Неплохую статью для первого знакомства с функцией СМЕЩ написал Ренат Лотфуллин (рекомендую!)

1. Чтобы создать ссылку на диапазон ячеек, сначала необходимо указать начальную ячейку. Затем надо указать, на расстоянии скольких строк и столбцов от нее начинается прямоугольный диапазон. Например, с помощью функции СМЕЩ можно создать ссылку на диапазон ячеек, включающий две строки и три столбца и начинающийся на два столбцов правее и на одну строку выше текущей ячейки (рис. 1). Хитрость функции СМЕЩ в том, что она возвращает диапазон, поэтому, если просто ввести ее в ячейку, ничего не выйдет – функция СМЕЩ вернет ошибку #ЗНАЧ! Поэтому в примере использована функция СУММ, которая суммирует значения в диапазоне, возвращаемом функцией СМЕЩ. Заметим, что, если функция СМЕЩ возвращает одну ячейку, например, =СМЕЩ(A4;-1;2;1;1), то ее можно использовать напрямую, она вернет значение ячейки С3.

Рис. 1. Пример использования функции СМЕЩ; диапазон суммирования, возвращаемый функцией СМЕЩ подсвечен

Подробнее »Excel. Примеры использования функции СМЕЩ (OFFSET)

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Подробнее »Как обойти ограничение Excel и сделать выпадающий список зависимым

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel. Собственно, ответ на вопрос дочери см. Как обойти ограничение Excel, и сделать выпадающий список зависимым.

Средство проверки данных

Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. [1] Например, необходимо, чтобы число, содержащееся в ячейке, принадлежало диапазону от 1 до 12. В случае если пользователь введет неправильное значение, программа выведет соответствующее сообщение (рис. 1).

Рис. 1. Вывод сообщения о неправильном вводе данных

Подробнее »Excel. Проверка данных

Некоторые статистические данные могут отображаться на диаграммах, даже без создания отдельных рядов. Многие (но не все) диаграммы позволяют дополнить ряд (ряды) данных полосами погрешностей. [1] Полосы погрешностей [2] отображают дополнительную информацию о данных. Например, их можно использовать для изображения ошибки или неопределенности, связанной с каждой точкой данных.

Например (рис. 1) полосы погрешностей могут изображать диапазоны ошибок измерения каждой точки данных. В этом примере полосы погрешностей выражены в процентах: значение плюс-минус 10% от значения. [3]

Рис. 1. График с полосами погрешностей, выраженных в процентах

Подробнее »Диаграммы в Excel. Использование полос погрешности