Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами: пошаговая инструкция
Люди, которые хоть раз искали работу через интернет и не только, наверняка часто натыкались на вакансии, где основным требованием к соискателю являлось уверенное знание пакета программ Microsoft Office, и в частности программы Microsoft Excel.
Навигация
Всё дело в том, что на определённых должностях людям приходится работать с большим количеством данных, а программа Microsoft Excel позволяет организовывать их в таблицы и графически представлять информацию. Именно поэтому знать основы данной программы не повредит никому.
Специально для тех людей, которые только начали познавать Microsoft Excel, мы написали эту статью. В ней пойдёт речь о таком важном инструменте программы, как «Сводные таблицы». Именно с помощью этих таблиц сортируются данные и ведётся их учёт.
Что такое сводные таблицы в Excel и для чего они нужны?
Сводными называют те таблицы, которые содержат части данных и показывают их так, чтобы связь между этими данными была наглядно видна. Иными словами, данные таблицы позволяют вести учёт чего-либо в удобной форме. Лучше всего объяснить на примере:
- Представьте себе, что Вы владелец небольшого магазина одежды, в котором работают несколько продавцов. Вам требуется определить активность каждого из них.
- Для этого Вам понадобится выделить несколько показателей: имя продавца, проданный им товар, дату продажи и сумму выручки. Вы открываете Excel, делите рабочее поле на четыре столбика и в каждом из этих столбиков записываете ежедневные показатели продавцов. Выглядит это примерно следующим образом:
- Согласитесь, такой графический способ представления данных очень прост и удобен для чтения? Однако на графической составляющей удобства сводной таблицы не заканчиваются.
- Если внимательно посмотреть, то в шапке таблицы рядом с каждым заголовком можно заметить стрелочку. Если на неё нажать, то можно будет отсортировать данные таблицы по выбранным параметрам.
- Например, можно выбрать только продавца Катю и посмотреть проданные ею товары, даты продаж и суммы. Точно так же можно отсортировать продажи по датам. Выбрав дату, Вы увидите продавцов, которые в тот день продали товары и общую сумму дневной выручки.
Что такое формулы в Excel и для чего они нужны?
- Слово «формула» нам всем знакомо ещё со школьных лет. В Excel формулы предписывают программе определённый порядок действий с числами или значениями, которые находятся в ячейках. Вся суть электронных таблиц заключается именно в формулах. Ведь без них они теряют всякий смысл своего существования.
Для формул в Microsoft Excel применяются стандартные математические операторы:
- Во время умножения всегда необходимо использовать знак «*». Его упущение, как в письменном варианте вычислений, недопустимо. Иными словами, если Вы запишите (4+7)8, то данную запись программа не сможет распознать, так как после круглой скобки не стоит знак умножения «*».
- Программа Excel многими используется вместо калькулятора. Например, если Вы введёте в строку формул 2+3, то в выбранной ячейке отобразится результат 5.
- Однако, при создании сводных таблиц для формул используются ссылки на ячейки со значениями. Когда в ячейке будет меняться значение, формула автоматически будет проводить перерасчёт результата.
Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010
Мы уже разобрались, что сводные таблицы обладают сплошными плюсами. Однако, для того чтобы приступить к их созданию, необходимо иметь уже наработанную базу данных, которая будет внесена в таблицу.
Так как Excel довольно сложная программа с большим набором инструментов и функций, мы не будем углубляться в её изучение с головой, а лишь покажем общий принцип создания базы данных. Итак, приступим:
Шаг 1.
- Запустите программу. На главной странице Вы увидите стандартное поле с ячейками, куда необходимо будет водить нашу базу данных.
- Для начала необходимо создать четыре основных столбца с заголовками.
- Для этого дважды кликните по одной из верхних ячеек и впишите в неё название первого заголовка «Продавец» и нажмите Enter.
- Далее проделайте то же самое с тремя соседними ячейками в той же строке, вписывая в них названия заголовков.
Шаг 2.
- После того, как Вы ввели названия заголовков, необходимо увеличить им размер шрифта и сделать жирными, чтобы они выделялись на фоне других данных.
- Для этого зажмите левую кнопку мышки на первой ячейке и ведите мышь вправо, выделяя соседние три.
- После выделения всех ячеек на рабочей панели с текстом установите для них размер шрифта 12, выделите его жирным и выровняйте по левому краю.
- Текст наверняка не будет помещаться в ячейки, поэтому зажмите левую кнопку мыши на границе ячейки и поведите мышь вправо, тем самым увеличивая её.
- То же самое Вы можете проделать и с высотой ячеек.
Шаг 3.
- Далее, под каждым заголовком вписывайте соответствующий ему параметр точно таким же способом, как Вы вписывали заголовки.
- Для того, чтобы не мучиться с редактированием каждой ячейки, после ввода параметров выделите их все мышкой и примените сразу к ним одинаковые свойства.
Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?
Теперь, когда у Вас имеется база данных, можно переходить непосредственно к созданию сводной таблицы. Для этого проделайте следующие шаги:
Шаг 1.
- Первым делом необходимо выделить одну из ячеек имеющейся у Вас стартовой таблицы. Далее в левом верхнем углу перейдите на вкладку «Вставка» и кликните по кнопке «Сводная таблица».
Шаг 2.
- Перед Вами откроется окошко, в котором потребуется выбрать диапазон или ввести название таблицы.
- Также в окне есть возможность выбора места для отчета. Создадим его на новом листе.
- Пометьте точками пункты, которые указаны на скриншоте ниже и нажмите кнопку «ОК».
Шаг 3.
- Перед Вами откроется новый листок, на который будет помещена ещё незаполненная сводная таблица. С правой стороны окна появится колонка с полями и областями.
- Под полями подразумеваются заголовки столбиков, которые вписаны в вашу начальную таблицу с данными. Мышкой их можно будет перетащить в имеющиеся области, что позволит Вам сформировать сводную таблицу.
- Задействованные заголовки будут помечаться галкой. В одну область можно вносить несколько заголовков и изменять их местоположения для того, чтобы получить наиболее приемлемый для Вас вид сортировки данных.
Шаг 4.
- Далее необходимо решить, что мы хотим проанализировать. К примеру, мы хотим понять, какой конкретно продавец, продал какой товар за определённый период времени и на какую сумму. Фильтрация данных в таблице будет осуществляться по продавцам.
- В колонке справа выберите заголовок «Продавец», зажмите на нём левую кнопку мышки и перетаскивайте в область «Фильтр отчёта».
- Как можно заметить, внешний вид таблицы поменялся, заголовок стал отмеченным.
Шаг 5.
- Далее по той же схеме в колонке справа необходимо зажать левую кнопку мышки на заголовке «Товары» и перетащить его в область «Названия строк».
Шаг 6.
- Далее в графу «Названия столбцов» попробуем перетащить заголовок «Дата».
- Чтобы список продаж отображался не ежедневный а, например, ежемесячный, необходимо кликнуть по любой ячейке с датой правой кнопкой мышки и выбрать из списка пункт «Группировать».
- В открывшемся окне потребуется выбрать период группировки (с какого числа по какое), и выбрать шаг из списка. После чего следует нажать «ОК».
- Далее Ваша таблица преобразуется таким вот образом.
Шаг 7.
- Следующем пунктом перетяните заголовок «Сумма» в графу «Значение».
- Как можно заметить, стали отображаться просто числа, несмотря на то, что к данному столбцу в стартовой версии таблицы был применён формат отображения «Числовой».
- Чтобы поправить данную проблему, выделите в сводной таблице необходимые ячейки, кликните правой кнопкой мышки из списка щёлкните по пункту «Числовой формат».
- В открывшемся окошке в разделе «Условные форматы» выберите пункт «Числовой» и по желанию можете отметить галочкой пункт «Разделитель групп разрядов».
- Далее нажмите «ОК». Как можно заметить, простые числа изменились и теперь отображаются не просто 650, а 650,00.
Шаг 8.
- По идее сводная таблица готова и с ней можно начинать работу. Например, при выборе продавца Вы сможете выбрать одного, двух, трёх или сразу всех, поставив маркер напротив пункта «Отметить несколько элементов».
- Точно таким же способом можно фильтровать строки и столбцы. К примеру, наименования товаров и даты.
- Отметив галочками поля «Брюки» и «Костюм», легко выяснить, сколько с их продажи получилось выручки в целом, или конкретными продавцами.
- Также в графе «Значения» можно задать определённые параметры заголовку. На данный момент там установлен параметр «Сумма».
- То есть, если посмотреть на таблицу, можно увидеть, что продавцом Ромой в феврале было продано рубашек на общую сумму 1 800,00 рублей.
- Для того, чтобы узнать их количество, необходимо щёлкнуть мышкой по пункту «Сумма» и выбрать из меню строчку «Параметры полей значений».
- Далее откроется окошко, где необходимо выбрать пункт «Количество» и нажать кнопку «ОК». После этого Ваша таблица поменяется и, глядя на неё, Вы поймёте, что за февраль Роман продал рубашек в количестве двух штук.
Как уже выше было оговорено, программа Excel имеет обширный ассортимент инструментов и возможностей, поэтому говорить о работе с таблицами можно до бесконечности.
Для того, чтобы её лучше понять, советуем самостоятельно посидеть и поэкспериментировать с изменениями значений. Если что Вам будет непонятно, обращайтесь к встроенной справке Microsoft Excel. Она в нём исчерпывающая.