Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива

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

Ранее я описал, как использовать формулы массива:

Кто не знаком с формулами массива, предлагаю сначала почитать Введение в формулы массива. Рекомендую также Джон Уокенбах «Формулы в Microsoft Excel 2010», а именно часть IV. Формулы массивов (глава 14. Знакомство с массивами; глава 15. Магия формул массива).

Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0!

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

Excel. Транспонирование строк в столбцы (или столбцов в строки) с помощью формулы массива

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

Ранее я описал, как транспонировать столбцы в строки с помощью функции ДВССЫЛ.

Напомню, что, если нужно «переделать» таблицу, ориентированную по строкам, в таблицу, ориентированную по столбцам, можно скопировать строки в буфер, и вклеить на тот же или новый лист с помощью специальной вставки (рис. 1).

Рис. 1. Транспонирование с помощью специальной вставки.

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

Excel. Вычисление стандартного отклонения для данных с тенденцией

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

В своей работе я часто строю контрольные карты Шухарта. Напомню, что контрольные карты Шухарта – один из инструментов менеджмента качества. Используется для контроля над ходом процесса. Пока значения остаются в пределах контрольных границ, вмешательство в процесс не требуется. Процесс статистически управляем. Если значения выходят за контрольные границы, необходимо вмешательство менеджмента для выявления причин отклонений.

Для построения контрольной карты я использую исходные данные, среднее значение (μ) и стандартное отклонение (σ). В Excel: μ = СРЗНАЧ($F$3:$F$15), σ = СТАНДОТКЛОН($F$3:$F$15)

Сама контрольная карта включает: исходные данные, среднее значение (μ), нижнюю контрольную границу (μ – 2σ) и верхнюю контрольную границу (μ + 2σ):

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

Excel. Добавление примечания в формулу с использованием функции Ч

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

Наверное, все знают, как создать примечание к ячейке (рис. 1): вкладка «Рецензирование» → область «Примечания» → «Создать примечание» (или Shift + F2)

Рис. 1. Примечание к ячейке

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

С другой стороны, при создании сложной или редкой формулы через некоторое время вы уже не вспомните нюансы ее написания. Поэтому совсем отказаться от примечаний нельзя. И вот недавно я встретил описание оригинальной функции Excel – Ч.

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

Excel. Добавление фона на часть гистограммы

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

Ранее я показал, как добавить линию на гистограмму. Такая линия, например, может отделять своевременно оплаченные счета (накладные) от просроченных (рис. 1).

Рис 1. Распределение накладных по сроку оплаты; отсрочка составляет 30 дней; вертикальная линия отделяет своевременно оплаченные накладные от оплаченных с задержкой.

Области гистограммы можно разделить и иначе – путем накладки фона. В настоящей заметке я покажу, как создать смешанную диаграмму (рис. 2), где фон создается с помощью дополнительного ряда гистограммы. Фон образуется благодаря другому цвету и размеру столбиков гистограммы, отличному от цвета столбиков основной гистограммы.

Рис. 2. Фон создан с помощью цветных столбиков

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

Excel. VBA-код для создания подписей к данным диаграммы

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

Ранее я уже подробно рассказывал о пузырьковых диаграммах. Кратко напомню.

Пузырьковая диаграмма – это разновидность точечной диаграммы, в которой точки данных заменены пузырьками, причем их размер служит дополнительным (третьим) измерением данных. На пузырьковой диаграмме, как и на точечной, нет оси категорий — и горизонтальная, и вертикальная оси являются осями значений. В дополнение к значениям X и значениям Y, наносимым на точечную диаграмму, на пузырьковой диаграмме показаны также значения Z (размер).

Пузырьковые диаграммы позволяют наглядно представить и анализировать взаимозависимости трех переменных.

В качестве примера построения пузырьковой диаграммы я привел распределение населения России по федеральным округам (рис. 1; см. также Excel-файл). Карта России была найдена в Интернете, сохранена как рисунок и импортирована в качестве фона области построения диаграммы. Координаты (X и Y) каждого «пузыря» были подобраны вручную.

Рис. 1. Распределение населения России по федеральным округам

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

Относительные, абсолютные и смешанные ссылки на ячейки в Excel

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

Этот материал предназначен для начинающих и подготовлен с участием Анны Ивановой

Ссылка в Excel – это адрес ячейки или диапазона ячеек.

В Excel есть два вида стиля ссылок:

  • Классический (или А1)
  • Стиль ссылок R1C1; здесь R — row (строка), C — column (столбец).

Включить стиль ссылок R1C1 можно в настройках Сервис —> Параметры Excel —> закладка Формулы —> галочка Стиль ссылок R1C1:

Рис. 1. Настройка стиля ссылок

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

Как с помощью диаграммы приукрасить действительность? или о факторе лжи Эдварда Тафти

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

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

Существуют три вида лжи: ложь, наглая ложь и статистика
Высказывание приписывается премьер-министру
Великобритании Бенджамину Дизраэли

Я помню, как однажды, работая в издательстве, хотел продемонстрировать сокращение числа ошибок в рекламных объявлениях. Исходные данные выглядели неплохо (рис. 1), но самое первое значение (год 2002-й, I кв.) явно не вписывалось в обнаруженную тенденцию… и я его просто… отбросил (рис. 2).

Рис. 1. Доля объявлений, вышедших с ошибками

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

Принцип Эдварда Тафти минимизации количества элементов диаграммы

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

Edward R. Tufte – автор нескольких книг [1], посвященных дизайну, инфографике, визуальному представлению количественных данных. Согласно принципу Тафти, диаграмма должна содержать только самое необходимое. Информацию передают ряды данных, а не линии сетки, подписи данных и т.п. Таким образом, элементы диаграммы, которые не передают мысль автора, нужно безжалостно удалить.

Рассмотрим несколько примеров. Для удобства откройте файл Принцип минимизации количества элементов диаграммы Эдварда Тафти. Пример в формате Excel2007.

На рис. 1 представлена стандартная гистограмма, построенная Excel без посторонней помощи.

Рис. 1. Стандартная гистограмма

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

Excel. Суммирование значений в четных (или кратных) строках (столбцах)

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

Недавно у меня возникла задача просуммировать значения в каждом третьем столбце. Пример, о котором идет речь, мы рассмотрим позже в силу его сложности, а начнем с более простого случая – найдем сумму значений для всех четных / нечетных строк. Исходные данные представлены на рис. 1 (см. также лист «Пример1» Excel-файла).

Рис. 1. Данные о приходе / уходе [1]

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