Как создать накопительную ячейку в excel: Расчет набегаемой суммы в Excel

Создание сводной таблицы Excel из нескольких листов

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

Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.

Сводная таблица в Excel

Для примера используем таблицу реализации товара в разных торговых филиалах.

Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти величину продаж по каждому отделу, придется посчитать вручную на калькуляторе. Либо сделать еще одну таблицу Excel, где посредством формул показать итоги. Такими методами анализировать информацию непродуктивно. Недолго и ошибиться.

Самое рациональное решение – это создание сводной таблицы в Excel:

  1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
  2. В меню «Вставка» выбираем «Сводная таблица».
  3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
  4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

  • Первая строка заданного для сведения данных диапазона должна быть заполнена.
  • В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
  • В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.



Как сделать сводную таблицу из нескольких таблиц

Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

  1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
  2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
  3. Следующий этап – «создать поля». «Далее».
  4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
  5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
  6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если она исчезла, просто щелкаем мышью по табличке.

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

А вот такой отчет можно сделать, если перетащить поля между разными областями:

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

Проверка правильности выставленных коммунальных счетов

С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.

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

Для примера мы сделали сводную табличку тарифов для Москвы:

Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

Скачать все примеры сводной таблицы

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

Excel

На этой странице представлена коллекция простых и изящных инструментов работы в Excel. То, что по-английски называется tips & tricks. В некоторых случаях я даю ссылку на первоисточник. Это означает только то, что именно там я впервые встретил описание метода.

В Excel существует дополнительное (альтернативное) контекстное меню перетаскивания с полезными опциями (Alternate Drag and Drop menu). Чтобы получить к нему доступ выделите ячейку или диапазон ячеек. Перейдите к правому краю данных. Щелкните правой кнопкой мыши и перетащите границу вправо в новую область. Отпустите кнопку мыши.

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

Если нужно найти сумму цифр в строке текста, можно воспользоваться алгоритмом: 1 * количество единиц в строке + 2 * количество двоек в строке + … + 9 * количество девяток в строке = результат

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

Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

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

Для сортировки значений стандартно Excel использует опцию в таблицах. Но вы также можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ, соответственно, для сортировки диапазона по возрастанию и убыванию.

Если вы хотите сгенерировать неповторяющиеся случайные числа между 1 и 100, Excel стандартно предлагает функции СЛЧИС() и СЛУЧМЕЖДУ(), но обе они, генерируют  случайные числа с морем дублей. Задачу можно решить с помощью создания массива из неиспользованных чисел диапазона, и уже из них выбирать случайным образом. Заметка интересна также методикой работы с мегаформулами.

Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k).

Несмотря на то, что привычный тип ссылок А1 является доминирующим, существуют ситуации, когда стиль R1C1 существенно эффективнее. Такой стиль удобнее при записи макросов, программировании VBA, использовании функции ДВССЫЛ и условном форматировании.

Функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Если вы хотите суммировать только видимые строки, используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Эта функция чаще используется в таблицах, но ничего не мешает использовать ее, как обычную функцию листа.

Функция ДВССЫЛ используется, когда нужно сослаться на ячейку, чей адрес будет определен на основе вычислений. ДВССЫЛ также справится, когда предварительно вычисляется лист, на который нужно сослаться. Билл Джелен описывает трюк, который справится, даже если имя листа представляет собой дату. Более того ДВССЫЛ позволяет ссылаться на диапазон ячеек, который затем используется внутри функций ВПР или СУММЕСЛИ.

Если у вас есть множество листов с идентичной структурой, например, 12 листов с коммерческими результатами за каждый месяц, вы можете найти сумму в одной и той же ячейке на каждом листе с помощью 3D-формулы. Если листы имеют имена Янв, Фев и т.д., а найти нужно сумму в ячейке В4 за все месяцы, используйте формулу =СУММ(Янв:Дек!B4).

Мало кто знает, что оператор пробел позволяет находить значение на пересечении множеств. Например, если вы создали горизонтальный именованный диапазон Бостон и вертикальный диапазон Продажи, то формула =СУММ(Бостон Продажи) позволяет найти значение продаж по Бостону.

До введения VBA, макросы писали на языке xlm (Excel Macro). Язык использовал макрофункции, т.е., функции листа макросов Excel 4.0. Этот язык до сих пор поддерживается Microsoft для совместимости с предыдущими версиями Excel. Язык xlm среди прочих содержит функцию Получить.Ячейку (GET.CELL), которая предоставляет гораздо больше информации, чем современная функция ЯЧЕЙКА(). На самом деле, Получить.Ячейку может рассказать о 66 различных атрибутах ячейки, в то время, как функция ЯЧЕЙКА возвращает лишь 12 параметров.

Начиная с версии 2010 в Excel появилась гибкая функция ЧИСТРАБДНИ.МЕЖД, которая позволяет рассчитать количество рабочих дней для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней.

Если вам нужно найти номер первой непустой ячейки в строке (столбце), или значение первой непустой ячейки, можно воспользоваться одним малоизвестным свойством функции ИНДЕКС возвращать не одно значение, а массив.

В то время как функция ЕСЛИ и большинство подобных функцией легко конвертируют логические значения ИСТИНА/ЛОЖЬ в числовые 1/0, СУММПРОИЗВ не делает этого.  Чтобы справиться с проблемами используйте операцию минус минус, или поместите все критерии в один аргумент, используя умножение (*) для Булевого критерия И и сложение (+) – для ИЛИ.

Если вы не начинающий пользователь, вас может заинтересовать книга Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В частности, вы узнаете о Булевой алгебре, операции минус минус, функции ДВССЫЛ, таймере, ссылках R1C1, датах до 1900 г.

Подробно о, возможно, самой полезной функции Excel — ВПР. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня

Всё о сводных таблицам см. Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. В частности, раскрыты следующие темы:

Функции массива — не самые простые для понимания, однако они дают такие возможности, что дух захватывает. Рекомендую книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

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

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

Уже стало традицией, что Microsoft от версии к версии что-то улучшает, а что-то ухудшает (по крайней мере, делает нечто не однозначное). Так, начиная с 2013 г. нельзя указать Excel сформировать вторую сводную таблицу на основе нового кеша. Как обойти это ограничение, читайте в заметке Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?

Начиная с версии 2010 г. в Excel добавлены срезы — удобный и наглядный инструмент работы со сводными таблицами; срез — это тот же фильтр, только вынесенный в отдельную область. А с 2013 г. добавлен особый вид срезов — временные шкалы, позволяющие визуализировать работу с датами.

С версии 2013 г. в Excel добавлены рекомендуемые сводные таблицы, которые облегчат работу начинающим пользователям и сократят время для опытных.

Начиная с версии 2010 г. в Excel появились спарклайны — маленькие диаграммы, помещающиеся в одну ячейку листа. Спарклайны позволяют обнаружить тенденции, периодические (сезонные) изменения, выделить максимальное (минимальное) значение.

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

В версии Excel Professional Plus доступна надстройка Inquire. Она позволяет: проанализировать активную книгу, сравнить две книги, отобразить в виде схемы связь книги, листа, ячейки; очистить ненужное форматирование. Последнее особенно ценно, если вам достался файл от начинающего пользователя, любящего раскрашивать целые столбцы или строки.

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

Если вам встретились незнакомые единицы измерения, например, баррель, oz, акр, узел, фунт, пинта, функция ПРЕОБР легко переведет их в привычные метры, литры, граммы…

Когда ваша книга Excel содержит множество листов, для удобства навигации вы, возможно, захотите создать оглавление (указатель). Вы можете сделать его вручную или воспользовавшись кодом VВA.

Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. По умолчанию Excel создает не обычные ссылки, типа =С4, а в виде формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вам это неудобно, или вы хотите иметь возможность копировать формулу вдоль столбца, научите Excel ссылаться на ячейку в сводной таблице, как на обычную.

Команда Специальная вставка позволит вам вставлять только значения ячеек (без формул и форматирования), перемещать данные из столбцов в строки и наоборот, преобразовывать диапазон чисел, прибавляя, вычитая, деля и умножая каждое число из диапазона на константу.

Функция ДВССЫЛ одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми.

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

Стандартные средства Excel не позволяют создавать контекстные выпадающие списки. Например, вы хотите, чтобы состав списка зависел от содержимого ячейки, находящейся слева от ячейки со списком. Создайте именованные массивы и сошлитесь на них с помощью функции непрямого действия ДВССЫЛ (INDIRECT).

Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. Например, только нечетные числа, или текст, начинающийся с буквы А. Реализовать такую возможность позволяет команда Проверка данных.

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

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

Финансовые функции в Excel кажутся очень сложными. Чтобы их понимать, нужно разбираться в предметной области — что, собственно говоря, рассчитывают эти функции. Прочитайте заметку Сравнение аннуитетных и дифференцированных платежей в погашение ипотечного кредита, и для вас приоткроется завеса над некоторыми финансовыми функциями, в частности: ПЛТ, ОСПЛТ, ПРПЛТ, ПС, КПЕР, СТАВКА.

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

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

Если вас напрягает необходимость постоянно изменять область диаграммы после каждого добавления данных, замените в диаграммах ссылки на ячейки ссылками на именованные динамические диапазоны. Этот трюк позволяет также создавать диаграммы, отражающие определенное число последних записей (даже если добавите несколько новых)

Стандартные средства Excel в круговых диаграммах позволяют использовать только один набор данных. Если вы хотите сравнить два набора данных, воспользуйтесь несложным трюком, предложенным Д. Холи и Р. Холи в книге «Excel 2007. Трюки».

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Однако само условное форматирование использует некие правила для выделения. Вместо того, чтобы анализировать цвет ячеек, достаточно применить эти правила в функциях СУММЕСЛИМН или БДСУММ.

Около двух лет тому назад мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Оказалось, что файл содержал объекты, и мне удалось их удалить с помощью небольшого кода VBA. У этой истории совсем недавно появилось продолжение. Посетитель блога оставил комментарий к той заметке, указав что стандартный Excel-файл, начиная с версии 2007, является zip-архивом. Так что для удаления «паразитного» содержимого достаточно открыть файл как архив и удалить соответствующую папку.

Если вам нужно подсчитать число вхождений подстроки (символа) в текст, можете использовать нехитрый VBA код или стандартные средства Excel на основе функции ПОДСТАВИТЬ()

Многим известна бухгалтерская «заморочка», связанная с расхождением на копейку в итогах суммирования. Как известно, Excel суммирует на основе точных значений, хранящихся в ячейке, а отражает в соответствии с выбранным вами форматом, например, с точностью до копейки. Чтобы преодолеть эту проблему предлагается изящная формула.

Как подсчитать число ячеек, содержащих символ (букву)? Воспользуйтесь формулой массива.

Вы думаете, что для моделирования с помощью метода Монте-Карло Excel требует какую-нибудь надстройку? Совсем не обязательно… Простейшие модели можно реализовать с помощью обычных таблиц и функции СЛЧИС. Правда, будьте осторожны, так как эта функция пересчитывается при каждом изменении на листе, и если вы захотите реализовать 100 000 сценариев, то Excel может «загрустить»… Но все же, для решения задач моделирования методом Монте-Карло лучше воспользоваться специализированной надстройкой, например, Crystal Ball. Читайте Моделирование методом Монте-Карло в Crystal Ball для Excel и Анализ инвестиционного проекта (приобретение отеля) с помощью Crystal Ball в Excel

Для вычисления среднего значения диапазона данных в Excel есть функция СРЗНАЧ. Она игнорирует пустые ячейки, но, к сожалению, учитывает ячейки с нулевыми значениями. Используйте элегантную и простую формулу массива для вычисления среднего, не учитывающего нулевые значения.

Откройте для себя мир формул массива. Ваши возможности в использовании Excel значительно расширятся.  Если вы впервые знакомитесь с этой темой, начните с Введения в формулы массива, а затем изучите Некоторые примеры использования формул массива.

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

Если вам нужно найти адрес ячейки, содержащей максимальное (минимальное) значение в двумерном диапазоне, можно воспользоваться не очень простой, но весьма изящной формулой массива.

Если при открытии Excel-файла появляется предупреждение о циклической ссылке, существует простой способ найти ячейку, ответственную за это «безобразие».

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

Ранее я описал, как транспонировать столбцы в строки с сохранением связи новой области со старой с помощью функции ДВССЫЛ. А недавно обнаружил значительно более простой и изящный способ, основанный на функции массива {=ТРАНСП(массив)}

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

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

Не все знают, что для расчета таблиц весьма полезными могут быть смешанные ссылки.

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

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

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

В Excel нет стандартной функции суммирующей отдельно значения в четных и нечетных строках. Иногда возникает и более сложная задача — например, сложить значения в каждом пятом столбце. Рекомендую для этих целей функции массива. Проверьте мощь этих функций. Они не так сложны, как об этом принято думать. 🙂

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

Широко известна функция проверки правописания в Word или Outlook. Гораздо реже пользователи применяют проверку формул в Excel. В то же время такая проверка позволяет выявить довольно большое число ошибок.

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

Затенение пространства между линиями графика. Если вы хотите закрасить пространство между двумя линиями графика, сделать это «в лоб» не получится. Однако реализовать задуманное вам поможет использование на одной диаграмме, как линий, так и областей с накоплением.

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

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

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

Воспользуйтесь встроенными возможностями Excel для построения графика нормального распределения.

Если вы используете гистограммы с накоплением, то знаете, что стандартные методы Excel не позволяют вывести подпись, отражающую итоговое значение по столбцу. Изящный трюк позволяет преодолеть эту проблему.

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

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

Преобразование массива в столбец или строку. Если необходимо преобразовать массив в столбец или строку, можно воспользоваться функцией Индекс.

Вывести примечания на лист. Если вы хотите вывести на текущий лист текст всех примечаний, можете воспользоваться макросом. Будьте осторожны, чтобы не затереть содержимое ячеек, расположенных справа от ячеек с примечаниями.

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

Скрыть / показать ленту. Чтобы скрыть ленту дважды щелкнете по любой вкладке ленты. Чтобы временно отобразить ленту, щелкните нужную вкладку. Чтобы вернуть ленту, дважды щелкните на  нужной вкладке.

Прекратить торможение. Если Excel тормозит, и никаких видимых причин к этому нет, проверьте нет ли на листе паразитных объектов. Пройдите по меню: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты. Если таких объектов много и Excel виснет, примените макрос.

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

Вызвать окно «Формат ячеек» при помощи клавиатуры (это быстрее, чем любой иной метод): нажмите CTRL+1

Использовать мастер сводных таблиц. В Excel2007 разработчики Microsoft почему-то решили отказаться от мастера сводных таблиц и упрятали его так далеко, что сразу и не найдешь. Для использования мастера сводных таблиц выведите его на панель быстрого доступа. Подробности здесь.

Используйте функцию СТРОКА для создания заполнителя. Иногда возникает задача создать список, содержащий структурированную последовательность, например, для секторов, рядов и мест в театре / стадионе или для стеллажей на складе. Подробности здесь.

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

Вручную измените цену делений на оси времени. Возможно, вы уже сталкивались с тем, что шкала времени на графиках иногда отражается «криво». Excel автоматически создает цену деления равную, например, 7 мин 12 сек. Креативненько, неправда ли!? 🙂 Установите цену деления сами, не доверяя столь ответственную работу мозгам Excel.

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

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

Научите функцию ДЕНЬНЕД возвращать буквенное значение. Не знаю, как вас, а меня нервирует, что функция ДЕНЬНЕД возвращает число… например, 4. Куда как приятнее – «четверг». Эту проблему может легко решить небольшой код VBA или стандартные средства Excel.

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

Суммируйте значения в ячейках определенного цвета. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, у Excel`я нет такой стандартной функции. Воспользуйтесь простеньким кодом VBA. Вы также можете подсчитать число ячеек, содержащих текст определенного цвета.

Установите фильтр сводной таблицы на основе данных в ячейке. Если вы используете сводные таблицы в Excel, в которых содержатся тысячи или десятки тысяч значений, то выбор одного из них фильтром отчета наверняка сводил вас с ума… Введите значение, по которому требуется установить фильтр в ячейку на листе и запустите макрос.

Применяйте анализ чувствительности при построении моделей. Например, при подготовке инвестиционного проекта результатом моделирования явилась внутренняя норма доходности – IRR. Как поведет она себя при том или ином изменении исходных посылок? Подробности здесь.

Используйте функцию ВПР для извлечения данных из таблицы с двумя параметрами. Например, нужно определить заработную плату сотрудника исходя из грейда (квалификации) и режима работы (5-дневка или сменный). Подробности здесь.

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

Используйте «говорящие» заголовки диаграмм. Такие заголовки сразу обращают внимание на основную мысль, которую вы хотите донести с помощью диаграммы. При этом обратите внимание на выбор типа диаграммы, который поможет лучше отразить вашу мысль. Подробности здесь.

Учебник

. Создание пробной таблицы хранилища — Excel Data Streamer

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

  • Статья
  • 2 минуты на чтение

В этом руководстве вы узнаете, как:

  • Назвать таблицу данных
  • Добавить дополнительные структурированные столбцы для пробного хранилища
  • Добавить объекты для взаимодействия с макрокомандой

Предварительные условия

  • Data Streamer включен
  • Потоковая передача данных датчика в структурированную таблицу в Excel

Имя таблицы данных

[!ВИДЕО https://www. microsoft.com/en-us/videoplayer/embed/RE4d9Ii]

  1. Выберите ячейку в таблице.
  2. Перейдите к Table Design > Properties и установите флажок под Table Name .
  3. Переименуйте таблицу по желанию.

Теперь у вашей таблицы есть имя набора, которое можно проверить и на которое можно ссылаться с помощью макросов.

Добавить дополнительные структурированные столбцы для пробного хранилища

  1. Выберите первую ячейку справа от заголовков таблицы.
  2. Введите желаемое имя для первой пробной колонки вашего первого датчика.
  3. Повторите, но только для всех других желаемых датчиков, которые необходимо сохранить для каждого испытания.
    • Следите за повторяемостью и согласованностью соглашения об именах. Пример: Ch2_T1 , Ч3_Т1 и т. д.
  4. Повторите то же соглашение об именах для всех дополнительных испытаний, каждое из которых предназначено для хранения данных одних и тех же датчиков.
    • Пример: Ch2_T1 , Ch3_T1 , Ch2_T2 , Ch3_T2 и т. д.

В этой таблице теперь есть пустые столбцы для сохранения и хранения каналов данных.

Добавить объекты для взаимодействия с макрокнопками

  1. Перейти к Вставка > Фигуры
  2. Выберите фигуру из этого меню, например, Прямоугольник
  3. Назовите эту фигуру, чтобы она заканчивалась согласованным целым числом, например SaveTrial1
    • Это позволяет макросам просматривать объект имени, чтобы понять, к какому испытанию должен относиться макрос.
  4. Выберите этот объект еще раз и введите имя этой фигуры, чтобы его было легче увидеть позже.
  5. Повторите шаги 2–4 для всех дополнительных испытаний.

Теперь на вашем листе есть кнопки, к которым впоследствии можно прикрепить макросы, используя их имена для изменения взаимодействия.

Осмысление хранения и представления данных в Excel

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

На конференции MERL Tech я посетил сессию под названием «20 навыков, которые решают 80 % проблем с мониторингом и оценкой», представленную доктором Лесли Сейдж из DevResults. Меня поразили практические рекомендации, которыми поделилась Лесли, которые могут быть полезны всем, кто использует Excel для хранения и/или представления данных.

Я свел 20 навыков, представленных на занятии, к трем ключевым выводам, приведенным ниже.

1. Различие между хранением данных и представлением данных

Хранение данных и представление данных выполняют две разные функции, и они никогда не должны совпадать. Другими словами, хранение данных никогда не является представлением данных.

Надлежащее хранилище данных не должно содержать объединенных ячеек, подзаголовков, цвета, используемого для обозначения информации, различных типов данных внутри ячеек (цифр и букв), более одной части данных в ячейке (например, дезагрегации). Кроме того, при правильном хранении данных столбцы должны быть переменными, а строки — наблюдениями или наоборот. Следует избегать неправильных методов хранения данных, поскольку они означают, что вы не можете использовать функции Excel для представления данных.

Типичный пример плохого хранения данных:

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

Вот те же данные, представленные в «хорошем» формате хранения:

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

2. Используйте функции Excel для организации и очистки данных

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

  • Чтобы объединить ячейки с текстом в одну ячейку, используйте функцию конкатенации.
  • Чтобы разделить текст из одной ячейки в разные ячейки, используйте текст в столбцы
  • Для очистки текстовых данных используйте функции Excel: обрезка, нижняя, верхняя, правильная, правая, левая и длина.
  • Чтобы переместить данные из строк в столбцы или из столбцов в строки, используйте функцию транспонирования Excel.
  • Имеется функция удаления дубликатов из данных.
  • Создайте макрос для автоматизации простых повторяющихся действий в Excel.
  • Вставьте проверку данных в электронную таблицу Excel, если вы отправляете электронную таблицу данных разработчикам или партнерам для заполнения.
    • Это ограничивает тип данных или значений, которые можно вводить в определенных частях электронной таблицы.
    • Это также экономит ваше время на очистку данных после их получения.
  • Используйте функцию vlookup в Excel в автономной версии для поиска уникального идентификатора.
    • Спонсоры или доноры обычно требуют, чтобы данные были анонимными, если они обнародуются. Хотя это и не лучший вариант для анонимизации данных, вы можете использовать Excel, если вам не предоставлены определенные инструменты или процессы.
    • . Вы можете создать анонимную «онлайн-версию», содержащую уникальный идентификатор, и «офлайн-версию» (не общедоступную), содержащую идентификатор и личную информацию (PII). Затем, если вам нужно было ответить на вопрос об уникальном идентификаторе, например, в вашем опросе отсутствовали данные, и вам нужно было вернуться и собрать их, вы можете использовать vlookup, чтобы найти конкретную запись.

3. Используйте функции Excel для визуализации данных

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

Вот пример сводной таблицы, созданной на основе данных из приведенного выше примера хорошего хранилища данных (на создание которого ушло около минуты):

Используя сводную таблицу, вы можете использовать быстро сделать гистограмму:

В Будущем

В прошлом я стал жертвой неэффективного хранения данных. Теперь, когда я изучил эти передовые методы и функции Excel, я знаю, что улучшу свои методы хранения и представления данных. Кроме того, теперь, когда я поделился ими с вами; Я надеюсь, что вы тоже!

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

Читайте также: