Как сделать аналитическую таблицу в excel?

В Excel 2016 создать таблицу достаточно просто. В качестве демонстрационного примера условно возьмём следующие данные.

Первый	Второй
1	1
	1
1
1

Выделяем диапазон всех ячеек данных B2:C6 и переходим на вкладку Главная -> Форматировать как таблицу и выбираем любой понравившиеся стиль. Таким же образом можно выбрать стили для ячеек.

В итоги получим вот такую таблицу.

При выборе таблицы указателем мыши, появляется конструктор таблицы.

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

• В результате получится следующая таблица
• 
• Рассмотрим, ещё один из способов как сделать таблицу в Excel.
• Сама по себе, изначально, программа Office Excel,  является таблицей — текстовым процессором.

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

Источник: https://www.matematicus.ru/excel/kak-v-excel-sdelat-tablitsu

Как сделать таблицу в экселе?

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

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

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

Свойства созданной таблицы можно регулировать в специальном конструкторе, который появится («Работа с таблицами/Конструктор).

Полученная таким образом таблица будет выглядеть не привычно. Чтобы привести созданную структуру к более нормальному виду зайдите в закладку «Работа с таблицами» и отмените все пункты, как показано на рисунке ниже.

Как видите, вышеприведенный метод довольно-таки запутанный и не оптимальный. Для создания таблицы можно использовать более простой способ — заполнение ячеек нужной информацией.

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

Для удобства можно изменить свойства таблицы. Зайдите в закладку «Главная», выберите раздел «Формат» и нажмите на «Автоподбор ширины столбца». Теперь размер ячейки будет зависит от содержимого и изменяться в зависимости от длины текста. Высоту можно изменить так же.

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

Используя пункт «Выравнивание» можно настроить расположение текста в ячейках.

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

Видео уроки

• Обучение, Развитие, Тренинги

Оцените публикацию

Источник: https://hr-portal.ru/article/kak-sdelat-tablicu-v-eksele

Как создать таблицу в Excel: советы и описание :

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

Однако мало кто знает, на что способно это уникальное приложение. Скептики просто недооценивают его возможности, а их тут, нужно сказать, очень много. Многие спрашивают о том, как создать таблицу в Excel.

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

Что такое Excel

Сам программный продукт в виде приложения Excel был создан в 1987 году, после чего стал неотъемлемым компонентом Microsoft Office. По сути, программа представляет собой средство для работы с табличными данными. Однако основные принципы функционирования в сравнении с текстовым редактором Word или базами данных Access отличается кардинально. Работа с таблицами в Excel более удобна.

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

Основное назначение и операции

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

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

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

Теперь несколько слов об основных возможностях и параметрах программы.

Создание таблиц

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

В крайнем случае можно использовать самую обычную команду меню «Файл» со строкой «Создать…».

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

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

Редактирование ячеек

Отдельно стоит остановиться на том, что представляет собой редактирование ячеек и сама функция. Таблица в Excel позволяет вводить в ячейку данные любого типа. По умолчанию формат ячейки установлен как «Общий». При введении формулы он изменится автоматически.

Но иногда возникает проблема: как добавить таблицу в Excel с числовыми данными? Нет ничего проще. Нужно просто выделить ячейку (ячейки) и правым кликом вызвать дополнительное контекстное меню с командой «Формат ячеек», где выбрать параметр «Числовой».

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

Сам процесс редактирования вызывается двумя простейшими способами: двойной клик на ячейке или нажатие функциональной клавиши F2. Не путайте: вызывается не форматирование ячейки, а именно редактирование введенных в нее данных.

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

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

Простейшие вычисления

Итак, вы теперь знаете, как создать таблицу в Excel. Можно переходить к заполнению ячеек и применению основных математических функций сложения, умножения, вычитания, деления, суммы и т. д. В самих формулах можно использовать стандартные знаки математических вычислений («+», «-», «*», «/») или задавать соответствующие формулы.

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

К примеру, первая ячейка в Excel версии пакета 2003 года имела маркировку строки «1» и столбца «A». В версии 2010 года оба компонента имеют одинаковую маркировку («1»).

В связи с этим и возникает множество проблем с введением формул.

Так, например, в версии 2003 года можно ввести формулу суммы трех чисел в ячейке «=SUM(B1:B3)» или «=СУММ(B1:B3)» в четвертой строке или нажать кнопку, расположенную на главной панели (при условии, что исходные данные находятся в столбце «B» со строками от первой до третьей). В варианте 2010 года ничего подобного нет. Формула выглядит так: «=СУММ(R[-3]C:R[-1]C)». Зачем было создавать такие сложности, до сих пор понять никто не может (наверное, кроме самих разработчиков).

Перекрестные ссылки

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

При этом могут использоваться не только формулы, но и графические изображения, те же скрипты Java или Visual Basic, гиперссылки и многое другое.

Добавление графики

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

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

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

Работа с диаграммами

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

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

В принципе, можно даже импортировать данные из других программ. Например, это может быть Microsoft Access или любое другое приложение, поддерживающее работу с базами данных форматов DBF, DBT, NDX, MDX и т.

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

Дополнительные модули и надстройки

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

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

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

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

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

Это несколько напоминает «облачное» хранилище, но действует иначе (в активном режиме).

Заключение

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

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

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

Источник: https://www.syl.ru/article/186152/new_kak-sozdat-tablitsu-v-excel-sovetyi-i-opisanie

Таблицы подстановки в Excel

Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

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

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

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

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

На конкретном примере

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

Порядок работы

• Запустите MicrosoftExcel и создайте новую электронную книгу.
• Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.Таблица — заготовка для решения
• Расчет ежемесячных выплат по займу происходит с помощью функции ПЛТ (). В ячейку В5 введите формулу:

=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

• Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

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

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

Расчет платежей

• Далее вам нужно выделить диапазон ячеек A9:C18 , после чего перейти на вкладку данные, «анализ что-если» таблица данных. Первое поле «подставлять значения по столбцам в» оставить пустым, а в поле «подставлять значения по строкам в» указать ячейку с величиной процентной ставки зафиксировав ее знаками доллара $B$4.

Подстановка данных

Результат расчетов:

Результат

Источник: https://abuzov.ru/tablicy-dannih-v-excel-zametka/

11 приемов для подготовки таблицы с аналитическим отчетом: формат ячеек, заголовки и одновременное редактирование — Офтоп на vc.ru

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

Есть правила, которые сделают отчеты в таблицах простыми для восприятия.

1. Установить правильный порядок ячеек

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

Например, у нас есть отчет со статистикой по отправителям. Переносим колонку Sender (отправитель) на место колонки А.

Как сделать: выделяем колонку Sender → перетягиваем ее влево. Или выделяем колонку → в меню нажимаем «Правка» → «Переместить столбец влево».

2. Закрепить колонки и столбцы

Первая строка в документе — список характеристик. При прокрутке вниз первая строка исчезает из видимости. Остаются только данные, которые трудно определить к какой-то характеристике. Точно так же и с первым столбцом, если листать таблицу вправо. Видим данные, но неясно к кому они относятся.

Чтобы всегда иметь перед глазами характеристики и объекты отчета, нужно закрепить верхнюю строку и первую колонку.

В примере мы закрепим 2 колонки — Sender (имя отправителя) и Offer (предложение). Если мы будем листать таблицу вправо, эти колонки останутся на месте. Так мы всегда будем понимать к какому отправителю относится информация.

Как сделать: в меню выбираем «Вид» → «Закрепить» → 1 строку и 2 столбца.

3. Выровнять цифры

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

Как сделать: выделяем все ячейки или столбцы с цифрами → выбираем выравнивание по правому краю.

4. Отредактировать заголовки

Не стоит выделять заголовки слишком ярким цветом. Это создает визуальный шум и отвлекает. Поэтому сделайте заливку верхней строки в цвет немного темнее цвета таблицы.

Как сделать: выделяем верхнюю строку → нажимаем «Цвет заливки» → выбираем светло-серый цвет.

Дальше выравниваем заголовки по тому, как выровнен текст в колонке. Если колонка с цифрами — заголовок выравниваем по правому краю. Если весь столбец выровнен по левому краю — заголовки слева.

5. Установить правильные форматы ячеек

В таблицах есть разные форматы ячеек:

• текст;
• числа;
• процент;
• валюта;
• дата и другие.

Зачем устанавливать форматы ячеек:

1. Одинаковый вид. Можно устанавливать количество знаков после запятой. Определять, как отделять сотни от тысяч: 1000, 1 000 или 1.000. Выбрать место для знака процентов: %25 или 25%. Единый формат делает данные одинаковыми, визуально понятными и не дает запутаться.
2. Корректность данных. Например, дата «1/4/2016». Если не установить формат даты для такой ячейки, слеш (/) будет читаться как знак «поделить».
3. Правильный подсчет. Представьте, что вам нужно перемножить два столбца — числа и проценты. Если форматы установлены неправильно, нужно переводить проценты 20% в число 0,2. Или в формуле умножать значение на 100. И это еще очень простой подсчет — со сложными многокомпонентными формулами будет хуже. Установив правильные форматы, вы не рискуете забыть что-то перевести или домножить. Данные считаются правильно.

Формат чисел

При установке формата чисел нужно обратить внимание на количество цифр после запятой. Никому не интересно знать, что среднее количество продаж — 81,39847. Достаточно 81.

Как сделать: выделяем колонку → нажимаем «Формат» → «Числа» → уменьшаем число знаков после запятой.

Установите единую границу между сотнями и тысячами.

Границу между сотнями и тысячами в разных странах пишут по-разному. Кто-то привык использовать точку, кто-то — запятую. У нас принято разделять пробелом. Если разделить данные пробелом вручную, получить 2 разных числа в ячейке. Например, 18 346 — это 18 и 346. Поэтому правильно прописывать границу через формат.

Как это сделать: выделяем колонку → нажимаем «Формат» → «Числа» → «Другие форматы» → «Другие форматы чисел» → прописываем такой формат «### ### ##0».

Формат денег

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

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

Как сделать: если все равно, как разделять сотни и тысячи: Выделяем ячейки → «Формат» → «Числа» → «Другие форматы» → «Другие форматы валют» → выбираем нужный формат.

Если нужно, чтобы сотни и тысячи разделялись пробелом: Выделяем ячейки → «Формат» → «Числа» → «Другие форматы» → «Другие форматы чисел» → прописываем формат «### ### ##0$» или «$### ### ##0».

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

Как сделать: нажимаем на ячейку с установленным правильным форматом → нажимаем «Копирование форматов» → выделяем ячейки, на которые хотим перенести формат.

6. Выделить Total

Если внизу таблицы есть строка с суммой по колонкам (Total), выделите ее полужирным шрифтом.

Как сделать: выделяем строку Total → нажимаем «Полужирный».

7. Выровнять ширину столбцов

Чтобы заголовки выглядели аккуратно, нужно сделать три изменения:

• использовать перенос по словам, чтобы слишком длинные названия столбца не обрезалось, а делилось на несколько строк. Как сделать: выделяем строку → «Формат» → «Перенос текста» → «Переносить по словам»;
• длинные названия перевести в короткие. Например, «List Name / DB size» переводим в «DB size»;
• установить ширину столбцов двойным кликом. Как сделать: кликаем 2 раза на границу между столбцами → ширина колонки адаптируется под текст.

8. Сделать одинаковые названия значений, дополнить таблицу

Приведите таблицу в единый вид. Если есть несколько одинаковых значений, написанных по-разному, исправьте это. Например, значение Buy now и buy now делаем одинаковым — Buy now.

Зачем нужны одинаковые значения:

• для фильтров;
• для сводных таблиц.

Источник: https://vc.ru/flood/19968-web-analyst-report

Финансы в Excel

Подробности Создано 27 Апрель 2011

Вложения:

pivottablesexample.xls

[Сводные таблицы]

97 kB

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

Термины многомерного анализа данных

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

Многомерные данные, измерения

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

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

Например, план продаж можно проанализировать по следующим критериям:

• виды или группы товаров;
• бренды или категории товаров;
• периоды (месяц, квартал, год);
• покупатели или группы покупателей;
• регионы продаж
• и т.п.

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

• объем продаж;
• цена продажи;
• индивидуальная скидка
• и т.п.

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

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

OLAP

Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта.

По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server.

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

Виртуальный куб данных

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

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

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

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

«Сводный отчет» (сводная таблица, Pivot Table) — это пользовательский интерфейс для отображения многомерных данных. С помощью данного интерфейса можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок.

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

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

Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).

Реализованный в Excel интерфейс сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа.

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

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

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

Редактирование сводных таблиц

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

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

Подготовка многомерных данных

Подойдем к практическому применению сводных таблиц. Попробуем проанализировать данные о продажах в различных направлениях. Файл pivottableexample.xls состоит из нескольких листов. Лист Пример содержит основную информацию о продажах за определенный период.

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

Эти данные собраны на листе Справочники. На практике подобных измерений может быть гораздо больше.

Лист Пример содержит стандартное средство анализа данных – автофильтр. Глядя на пример заполнения таблицы, очевидно, что нормальному анализу поддаются данные о продажах по датам (они расположены по столбцам).

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

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

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

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

Именно на анализ больших объемов баз данных ориентирован в первую очередь интерфейс сводных таблиц. Поэтому необходимо придерживаться этих правил и при работе с источником данных в виде диапазонов ячеек.

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

От автофильтра к сводному отчету

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

В нормальном режиме создания сводной таблицы для исходных данных Excel не позволяет связывать данные нескольких таблиц по определенным полям. Обойти это ограничение можно программными средствами – см. пример-дополнение к данной статье на нашем сайте.

Чтобы не прибегать к программным методам обработки информации (тем более, что они и не универсальны), следует добавить дополнительные характеристики непосредственно в форму ввода журнала – см. лист ПродажиАнализ.

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

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

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

Теперь посмотрим какие возможности дает интерфейс сводных таблиц. На листе СводАнализ построено несколько отчетов на основе диапазона ячеек с данными листа ПродажиАнализ.

Первая таблица анализа построена через интерфейс Excel 2007 Лента Вставка Сводная таблица (в Excel 2000-2003 меню Данные Сводная таблица).

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

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

Свойства и форматирование

Кроме непосредственного отображения данных, имеется большой набор возможностей по отображению внешнего вида сводных таблиц. Лишние данные можно скрывать, используя фильтры. Для единичного элемента или поля проще пользоваться пунктом контекстного меню Удалить (в версии 2000-2003 Скрыть).

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

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

После выделения можно изменять вид через ленту, контекстное меню или вызывать стандартный диалог формата ячейки:

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

Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:

Сводная диаграмма

Нажав кнопку на ленте «Сводная диаграмма», можно сформировать специальный тип диаграммы, отображающей данные сводной таблицы:

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

Доступ к внешним данным

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

Excel поддерживает множество типов источников внешних данных:

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

Источник: https://www.excelfin.ru/index.php/articles/96