Жизнь человека из мира технологий - это невероятная комбинация цифр, показателей, которые периодически требуют систематизации. Чтобы осуществить последнее, приходится использовать особые технические инструменты. Наша статья докажет, что сводные таблицы в Excel доступны даже для чайников.
Немного теории
Сводные таблицы Excel (для чайника) - это разновидность реестра, которая содержит конкретную часть данных из источника для анализа и изображена так, чтобы можно было проследить между ними логические связи. Основа для ее проектирования - определенный список значений.
Прежде чем начать работу, стоит подготовить для неё необходимые материалы, которые можно свести для анализа. Формируя подготовительный вариант, следите за тем, чтобы данные были классифицированы, например, цифры не путались с буквенным обозначением, а все столбцы имели заголовки.
Сводные таблицы в Excel незаменимы для тех, кто по роду занятости имеет дело с большим количеством цифр, которые периодически необходимо систематизировать и формировать отчетность. Программа Excel поможет подсчитать и проанализировать большое количество значений, сэкономив время.
Плюсы использования такого вида группировки данных:
- во время работы не нужны особые познания из сферы программирования, метод подойдет и для чайников;
- возможность комбинировать информацию из других первоисточников;
- можно пополнять базовый экземпляр новой информацией, несколько подкорректировав параметры.
Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.
Пример создания сводной таблицы Excel – алгоритм для чайников
Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.
Запустив его, перед вами откроется обширное поле, разделенное на большое количество ячеек. Более детально о том, как делать сводные таблицы в Excel, вам подскажет видеоурок выше.
С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel.
На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».
Далее на экране открывается диалоговое окошко, где требуется указать соответствующие параметры. На этом этапе создания сводных таблиц в Excel есть несколько важных моментов. Если перед тем, как начать формирование реестра, вы установили значок курсора на листе, то заполнение пустых строчек окна произойдёт автоматически. Иначе адрес диапазона данных нужно обозначить самостоятельно.
Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.
Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.
Заранее озаглавьте каждый столбик
Выберите место, где будет располагаться будущая рамка с ячейками. Это может быть новое окно или же этот лист, рекомендуем использовать другой лист.
Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром.
Теперь необходимо разобраться, как строится вся эта конструкция. В окне настроек «Поля свободной таблицы», вы обозначаете данные, которые будут присутствовать.
Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии.
Обратите внимание, как расположились эти данные в нижней области панели настройки.
Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец.
При этом происходит суммирование. Сейчас мы можем узнать из нашего отчета, сколько товаров поступило в каждый отдел и их общую стоимость.
Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel.
Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов - просто наведите мышь, нажмите левую кнопку и тяните.
Что касается подсчета результатов, то сумма – далеко не единственная функция. Чтобы посмотреть, какие Excel предлагает еще, нажмите на название в области значений и выберите последнюю команду.
В параметрах полей значений вы найдете множество вариантов для анализа.
Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой.
Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.
Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры - просто перетяните, как было указано выше.
Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все».
Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу.
Также можно выбрать и значения для отдела.
Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.
Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки.
Обновление данных в сводной таблице в Excel
Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».
Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.
Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.
Как в сводную таблицу Excel добавить столбец или таблицу
Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра.
Перейдите на вкладку «Анализ» и откройте источник данных.
Excel сам все предложит.
Обновите и вы получите новый перечень полей в области настройки.
Добавить таблицу вы сможете только если «склеите» ее с исходной. Можно заменить диапазон в уже существующей, но добавить «на лету» другой диапазон нельзя. Зато можно создать новую сводную таблицу на основе нескольких исходных, даже расположенных на разных листах.
Как сделать сводную таблицу в Excel из нескольких листов
Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».
Выберите все команды.
И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.
Значок появится сверху.
У вас должны быть две идентичные по полям таблицы на разных листах. У нас это данные о поступлениях в отделы за май и июнь. Нажимайте на ярлык мастера сводных таблиц и выбирайте консолидацию диапазонов.
Нам нужно несколько полей, а не одно.
На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист (щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.
Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел».
Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.
Щелкайте «Далее» и создавайте на новом листе.
После нажатия на «Готово» получим результат. Это многомерная таблица, так что управлять ей довольно сложно. Поэтому мы и выбрали диапазон меньше, чтобы не запутаться в измерениях.
Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.
Снимая или устанавливая галочки, вы регулируете значения, которые вам необходимо увидеть. Неудобно и то, что расчет проводится для всех значений одинаковый.
Как видите, у нас одно значение в соответствующей области.
Изменение структуры отчёта
Мы поэтапно разобрали пример, как создать сводную таблицу Exce, а как получить данные другого вида расскажем далее. Для этого мы изменим макет отчета. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета».
Вам откроются на выбор три типа для структуризации информации:
- Сжатая форма
Такой тип программа применяется автоматически. Данные не растягиваются, поэтому прокручивать изображения практически не нужно. Можно сэкономить место на подписях и оставить его для чисел.
- Структурированная форма
Все показатели подаются иерархично: от малого к большому.
- Табличная форма
Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.
Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.
Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.
Отличного Вам дня!
Сводная таблица - это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Настройка сводной таблицы
Подготовка
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей . При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
В верхней части области имя поля установите флажок для поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк , поля даты и времени добавляются в область столбцов , а числовые поля добавляются в область значений . Вы также можете вручную перетащить любой доступный элемент в любое поле сводной таблицы или если вы больше не хотите использовать элемент в сводной таблице, просто перетащите его из списка полей или снимите флажок. Возможность изменить порядок элементов полей - это одна из функций сводной таблицы, которая делает ее более удобной для быстрого изменения внешнего вида.
Значения в сводной таблице
Обновление сводных таблиц
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить . При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы , щелкните стрелку под кнопкой Обновить и выберите команду Обновить все .
Удаление сводной таблицы
Если вы создали сводную таблицу и решили, что она больше не нужна, можно просто выделить весь диапазон сводной таблицы, а затем нажать клавишу Delete . Это не повлияет на другие данные, сводные таблицы и диаграммы вокруг него. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Теперь вы можете вставить сводную таблицу в электронную таблицу в Excel в Интернете.
Работа со списком полей сводной таблицы
В верхней части области поля сводной таблицы установите флажок для поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область " строки ", поля даты и времени добавляются в область столбцов , а числовые поля - в область " значения ". Вы также можете вручную перетащить любой доступный элемент в любое поле сводной таблицы или если вы больше не хотите использовать элемент в сводной таблице, просто перетащите его из списка полей или снимите флажок. Возможность изменить порядок элементов полей - это одна из функций сводной таблицы, которая делает ее более удобной для быстрого изменения внешнего вида.
Работа с значениями сводной таблицы
Обновление сводных таблиц
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Удаление сводной таблицы
Если вы создали сводную таблицу и решили, что она больше не нужна, можно просто выделить весь диапазон сводной таблицы, а затем нажать клавишу DELETE. Это не повлияет на другие данные, сводные таблицы и диаграммы вокруг него. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте
Excel - мощное офисное приложение, позволяющее проводить сложнейшие вычислительные расчёты, систематизировать информацию, создавать аналитические расчёты. К сожалению, многие пользователи, не разбираясь в тонкостях Excel , используют её только в качестве простого счётного инструмента вроде калькулятора.
Проанализировать в рекордно короткое время представленную информацию, сопровождающуюся числовыми показателями, принять на основе них эффективные управленческие решения, помогает сводная таблица в Excel, а как сделать её, можно легко понять, ознакомившись с рекомендациями продвинутых пользователей.
Сводная таблица создаётся, приняв за основу уже имеющуюся таблицу, которая может быть продуктом Excel или другой базы данных.
Если пользователь решил пройти начальный курс, как создать сводную таблицу в Excel,то ему лучше потренироваться на табличном варианте, созданном этим офисным приложением. После отличного усвоения основ, как делать сводные таблицы в Excel , можно переходить к более усложнённым вариантам, когда показатели получают путём загрузки из документов, созданных совершенно иными приложениями.
Основные требования
Существует ряд требований, обязательных для выполнения, в противном случае сводная таблица не будет выполнять задачи, на которые так рассчитывает пользователь.
Главным требованием является то, что все данные должны быть занесены в виде таблицы, в верхней части которой, безусловно, оформляется шапка (с указанием названий столбцов).
Кроме этого, должны быть полностью исключены пустые строки и столбцы. За пределами таблицы не должны находиться никакие примечания и посторонние записи, занесённые в строки или столбцы, примыкающие к используемой области.
Желательно, чтобы во всех табличных ячейках были тоже занесены данные, по возможности следует избегать пустых ячеек, поскольку это может негативно отразиться на систематизации и последующем анализе информации.
Часто в Excel пользователи объединяют несколько ячеек. Если необходимо создать сводные отчёты, то придётся избегать такого объединения. Недопустимо скрывать строки или столбцы, которые, по мнению пользователя, в этой ситуации не являются важными.
Инструкция по созданию
После того как пользователем были соблюдены все вышеописанные требования, можно приступать к дальнейшим действиям, позволяющим понять, как сделать сводную таблицу в Эксель.
Сначала нужно найти первую ячейку, которая расположена сразу под табличной шапкой, и сделать её активной. Для этого потребуется установить курсор мышки на эту ячейку и кликнуть левой клавишей.
Затем в основном меню следует перейти во вкладку «Вставка», после чего отобразится его подменю, среди которого будет пиктограмма с названием пункта «Сводная таблица». Кликнув по нему, активизируется мастер создания сводных отчётов, воспользовавшись которым, будет значительно проще делать табличный аналитический отчёт.
В появившемся диалоговом окне табличный редактор запросит указать диапазон, который будет подлежать анализу. Чаще всего, мастер самостоятельно правильно определяет его, поэтому вносить изменения не потребуется.
Также необходимо указать, где пользователь желает создать сводный отчёт:
- на том же самом листе, где имеются основные показатели;
- на новом листе.
Перейдя на новый лист, пользователь обнаружит там область для таблицы, а также список допустимых полей. Определившись с тем, какие конкретно показатели должны подлежать анализу, пользователь должен перетянуть мышкой выбранные поля в области «Фильтр», «Колонна». Таким способом удастся создать столбики сводного отчёта.
Далее, точно таким же способом оформляются строки, перетаскивая мышкой нужные поля. Если строк несколько, то важно определить, какая должна находиться вверху, а какая внизу. Можно расположить сначала строки в одном порядке и пронаблюдать, насколько это будет удобно, насколько корректно будет отображаться сводная информация. Если будет сложно анализировать данные при таком расположении, то всё легко исправляется – достаточно зажать определённое поле мышкой и переместить его вверх или вниз.
В завершение остаётся сделать последнюю область сводного отчёта «Значения». В неё переносится поле, являющееся ключевым для анализа, например, общая масса, общее количество.
Если всё делать строго по инструкции, то уже на этом этапе отобразится аналитический отчёт. При выявлении ошибок, достаточно поменять местами поля, обновить данные, и таблица автоматически перестроится.
Использование сводных отчётов
Пользователь принимает решение делать сводную таблицу, если возникает необходимость часто прибегать к аналитической деятельности. В связи с этим полезно знать и то, что таблица оснащена фильтром, позволяющим систематизировать данные по указанным критериям.
Применение фильтра
Начинающим пользователям полезно знать, как обновить сводную таблицу в Excel, и как осуществлять запрос на основе группировки данных.
Обновить данные в сводной таблице важно тогда, когда пользователем были внесены любые изменения в основной таблице. Для того чтобы обновить данные, следует кликнуть мышкой в пределах табличной области, вызывая дополнительное меню. В нём легко найти пункт «Обновить», а выбрав его, автоматически произойдёт обновление.
Применение фильтра в сводных отчётах также является достаточно важным действием. Очень часто заносятся ежедневные данные работников, а сотруднику, ответственному за аналитические отчёты, необходимо проанализировать данные за квартал, полугодие.
Как раз решить такую задачу помогает фильтр. Делать опять ничего сложного не понадобится. Достаточно кликнуть по любой дате мышкой (правой клавишей), в контекстном меню выбрать «Группировать», вслед за этим откроется диалоговое окно, в котором можно определить приемлемые для себя способы группировки. После этого таблица автоматически перестроится, значительно уменьшится в размерах, в связи с этим анализировать данные будет значительно проще.
Итак, создавать сводные таблицы несложно, если предварительно потренироваться на простой таблице , соблюдая все требования и вводя правильные данные. Овладев такими навыками, пользователь очень быстро ощутит преимущества, поскольку анализ данных будет осуществляться за короткий промежуток времени, сопровождаясь абсолютной точностью.
Сводные таблицы в Excel – мощный инструмент для создания отчетов. Он особенно полезен в тех случаях, когда пользователь плохо работает с формулами и ему сложно самостоятельно сделать анализ данных. В данной статье мы рассмотрим, как правильно создавать подобные таблицы и какие для этого существуют возможности в редакторе Эксель. Для этого никаких файлов скачивать не нужно. Обучение доступно в режиме онлайн.
Первым делом нужно создать какую-нибудь таблицу. Желательно, чтобы там было несколько столбцов. При этом какая-то информация должна повторяться, поскольку только в этом случае можно будет сделать какой-нибудь анализ введенной информации.
Например, рассмотрим одни и те же финансовые расходы в разных месяцах.
Создание сводных таблиц
Для того чтобы построить подобную таблицу, необходимо сделать следующие действия.
- Для начала ее необходимо полностью выделить.
- Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».
- В результате этого появится окно, в котором вам нужно указать несколько основных параметров для построения сводной таблицы. Первым делом необходимо выбрать область данных, на основе которых будет проводиться анализ. Если вы предварительно выделили таблицу, то ссылка на нее подставится автоматически. В ином случае ее нужно будет выделить.
- Затем вас попросят указать, где именно будет происходить построение. Лучше выбрать пункт «На существующий лист», поскольку будет неудобно проводить анализ информации, когда всё разбросано на несколько листов. Затем необходимо указать диапазон. Для этого нужно кликнуть на иконку около поля для ввода.
- Сразу после этого мастер создания сводных таблиц свернется до маленького размера. Помимо этого, изменится и внешний вид курсора. Вам нужно будет сделать левый клик мыши в любое удобное для вас место.
- В результате этого ссылка на указанную ячейку подставится автоматически. Затем нужно нажать на иконку в правой части окна, чтобы восстановить его до исходного размера.
- Для завершения настроек нужно нажать на кнопку «OK».
- В результате этого вы увидите пустой шаблон, для работы со сводными таблицами.
- На этом этапе необходимо указать, какое поле будет:
- столбцом;
- строкой;
- значением для анализа.
Вы можете выбрать что угодно. Всё зависит от того, какую именно информацию вы хотите получить.
- Для того чтобы добавить любое поле, по нему нужно сделать левый клик мыши и, не отпуская пальца, перетащить в нужную область. При этом курсор изменит свой внешний вид.
- Отпустить палец можно только тогда, когда исчезнет перечеркнутый круг. Подобным образом, нужно перетащить все поля, которые есть в вашей таблице.
- Для того чтобы увидеть результат целиком, можно закрыть боковую панель настроек. Для этого достаточно кликнуть на крестик.
- В результате этого вы увидите следующее. При помощи этого инструмента вы сможете свести сумму расходов в каждом месяце по каждой позиции. Кроме того, доступна информация об общем итоге.
- Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.
- Снова закрываем помощник для построения.
- На этот раз мы видим, что сводная таблица стала намного больше, поскольку сейчас в качестве столбцов выступают не месяцы, а категории расходов.
Если у вас не получается самостоятельно построить таблицу, вы всегда можете рассчитывать на помощь редактора. В Экселе существует возможность создания подобных объектов в автоматическом режиме.
Для этого необходимо сделать следующие действия, но предварительно выделите всю информацию целиком.
- Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.
- Сразу после этого появится окно, в котором будут различные примеры для построения. Подобные варианты предлагаются на основе нескольких столбцов. От их количества напрямую зависит число шаблонов.
- При наведении на каждый пункт будет доступен предварительный просмотр результата. Так работать намного удобнее.
- Можно выбрать то, что нравится больше всего.
- Для вставки выбранного варианта достаточно нажать на кнопку «OK».
- В итоге вы получите следующий результат.
Обратите внимание: таблица создалась на новом листе. Это будет происходить каждый раз при использовании конструктора.
Как только вы добавите (неважно как) сводную таблицу, вы увидите на панели инструментов новую вкладку «Анализ». На ней расположено огромное количество различных инструментов и функций.
Рассмотрим каждую из них более детально.
Нажав на кнопку, отмеченную на скриншоте, вы сможете сделать следующие действия:
- изменить имя;
- вызвать окно настроек.
В окне параметров вы увидите много чего интересного.
Активное поле
При помощи этого инструмента можно сделать следующее:
- Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».
- Сразу после этого вы увидите следующее окно. Здесь можно указать тип операции, которую следует использовать для сведения данных в выбранном поле.
- Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.
- В результате появится окно «Формат ячеек».
Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.
Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.
Вставить срез
Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. При этом ничего сложного делать не нужно.
- Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
- В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.
- В результате появится небольшое окошко, которое можно перемещать куда угодно. В нем будут предложены все возможные уникальные значения, которые есть в данном поле. Благодаря этому инструменту вы сможете выводить сумму лишь за определенные месяцы (в данном случае). По умолчанию выводится информация за всё время.
- Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.
- Таким образом получится выбрать любой промежуток времени.
- В любой момент всё можно вернуть в исходный вид. Для этого нужно кликнуть на иконку в правом верхнем углу этого окошка.
В данном случае мы смогли сортировать отчет по месяцам, поскольку у нас существовало соответствующее поле. Но для работы с датами есть более мощный инструмент.
Если вы кликните на соответствующую кнопку на панели инструментов, то, скорее всего, увидите вот такую ошибку. Дело в том, что в нашей таблице нет ячеек, у которых будет формат данных «Дата» в явном виде.
В качестве примера создадим небольшую таблицу с различными датами.
Затем нужно будет построить сводную таблицу.
Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.
- Затем нас попросят выбрать диапазон значений.
- Для этого достаточно выделить всю таблицу целиком.
- Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».
- Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).
- Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).
- На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».
- Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.
- Выбираем другой месяц и данных нет, поскольку все расходы в таблице указаны только за март.
Если вы внесли какие-нибудь изменения в исходные данные и по каким-то причинам это не отобразилось в сводной таблице, вы всегда можете обновить её вручную. Для этого достаточно нажать на соответствующую кнопку на панели инструментов.
Если вы решили изменить поля, на основе которых должно происходить построение, то намного проще сделать это в настройках, а не удалять таблицу и создавать её заново с учетом новых предпочтений.
Для этого нужно нажать на иконку «Источник данных». Затем выбрать одноименный пункт меню.
В результате этого появится окно, в котором можно заново выделить нужное количество информации.
Действия
При помощи этого инструмента вы сможете:
- очистить таблицу;
- выделить;
- переместить её.
Вычисления
Если расчетов в таблице недостаточно или они не отвечают вашим потребностям, вы всегда можете внести свои изменения. Нажав на иконку этого инструмента, вы увидите следующие варианты.
К ним относятся:
- вычисляемое поле;
- вычисляемый объект;
- порядок вычислений (в списке отображаются добавленные формулы);
- вывести формулы (информации нет, так как нет добавленных формул).
Здесь вы сможете создать сводную диаграмму либо изменить тип рекомендуемой таблицы.
При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.
Благодаря этому вы сможете:
- настроить отображение боковой панели со списком полей;
- включить или выключить кнопки «плюс/мину»с;
- настроить отображение заголовков полей.
При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.
Можно настроить:
- промежуточные итоги:
- не показывать;
- показывать все итоги в нижней части;
- показывать все итоги в заголовке.
- общие итоги:
- отключить для строк и столбцов;
- включить для строк и столбцов;
- включить только для строк;
- включить только для столбцов.
- макет отчета:
- показать в сжатой форме;
- показать в форме структуры;
- показать в табличной форме;
- повторять все подписи элементов;
- не повторять подписи элементов.
- пустые строки:
- вставить пустую строку после каждого элемента;
- удалить пустую строку после каждого элемента.
- параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
- заголовки строк;
- заголовки столбцов;
- чередующиеся строки;
- чередующиеся столбцы.
- настроить стиль оформления элементов.
Для того чтобы увидеть больше различных вариантов, нужно кликнуть на треугольник в правом нижнем углу этого инструмента.
Сразу после этого появится огромный список. Можете выбрать что угодно. При наведении на каждый из шаблонов ваша таблица будет меняться (это сделано для предварительного просмотра). Изменения не вступят в силу, пока вы не кликните на что-нибудь из предложенных вариантов.
Помимо этого, при желании, вы можете создать свой собственный стиль оформления.
Также тут можно изменить порядок отображения строк. Иногда это нужно для удобства анализа расходов. Особенно, если список очень большой, поскольку необходимую позицию проще найти по алфавиту, чем листать список по несколько раз.
Для этого нужно сделать следующее.
- Кликните на треугольник около нужного поля.
- В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).
Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».
В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».
Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.
Сводные таблицы в Excel 2003
Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.
Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.
- Перейти в раздел меню «Данные» и выбрать соответствующий пункт.
- В результате этого появится мастер для созданий подобных объектов.
- После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».
- Для завершения настроек жмем на «Готово».
- В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.
- К примеру, может получиться вот такой результат.
Становится очевидно, что создавать подобные отчеты намного лучше в современных редакторах.
Заключение
В данной статье были рассмотрены все тонкости работы со сводными таблицами в редакторе Excel. Если у вас что-то не получается, возможно, вы выделяете не те поля или же их очень мало – для создания такого объекта необходимо несколько столбцов с повторяющимися значениями.
Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.
Видеоинструкция
Для тех, у кого всё равно остались вопросы без ответов, ниже прилагается видеоролик с комментариями к описанной выше инструкции.
Федеральное агентство по образованию
филиал государственного образовательного учреждения
высшего профессионального образования
«Московский энергетический институт
(технический университет)» в г. Смоленске
Кафедра информатики
по лабораторной работе №12,13.
Тема «Табличный процессор MS-EXCEL.Абсолютная и относительная ссылка. Создание диаграмм.»
по курсу «Экономическая информатика»
Студент: Якубова И.А.
Группа: ПИЭ-09
Преподаватель: Сизов А.А.
Смоленск, 2009
Ход работы.
1. Одна из наиболее распространенных ситуаций в жизни – покупка товара. Магазин по продаже компьютерных аксессуаров продает товары, указанные в прайс-листе. Стоимость товара указана в долларах. Если стоимость товара превышает 250 рублей, покупателю предоставляется скидка 5%.
2. Создайте и оформите нижеследующую таблицу в EXCEL .
3. Заполните столбцы «Цена,руб», «Стоимость,руб», введя в ячейки соответствующие формулы .
Заполняем 7 строку. Цена руб.)=$B$1*B7, Стоимость= D7*C7. Растягиваем формулу на все строки.
4. Вычислите при помощи Мастера функций сумму «Итого» для столбца стоимость.
Вводим формулу СУММ(E7:E14).
5. Вычислите при помощи ввода логической формулы сумму «Итого в рублях с учетом скидки».
Вводим формулу =ЕСЛИ(Е16>250;Е16*(1-B2);Е16).
6. Строки 5,6,16,17 отформатируйте полужирным шрифтом.
Выделяем нужную строку. На панели форматирования выбираем полужирный.
7. Сохраните таблицу под именем PRICE 1. xls на диске .
8. Внедрить полученную таблицу в новый документ MS - W 0 RD .
Правка→Специальная вставка→ Лист Microsoft Excel (объект)→ связать.
9. Вставить объект MS - W 0 RD в свою РК.
Копируем документ MS-W0RD в буфер обмена. В MS-EXCEL выбираем Правка→Специальная вставка→объект Документ MS - W 0 RD →связать.
10. Изменить способ вставки, используя вставленный, внедренный и связанный объекты.
Копируем таблицу в буфер обмена. В WORD выбираем Правка→Специальная вставка→Текст в формате RTF – вставленный. Правка→Специальная вставка→Лист Microsoft Excel (объект) – внедренный; Правка→Специальная вставка→связать - связать.
11. Записать разницу в этих способах.
Вставленный – объект вставляется как обычная таблица в WORD, внедренный – объект вставляется как часть документа EXCEL, связанный – объект связан с исходным документом EXCEL и при его изменении объект в WORD тоже меняется.
Создайте линейную диаграмму успеваемости по четвертям и круговую диаграмму итоговой успеваемости за год. Отредактируйте диаграммы по образцу, выполнив последовательно следующие действия:
1.Создайте предложенную таблицу в EXCEL .
2. В строке «Итог за год» получите результаты, используя Мастер функций.
Вызываем мастер функций, вставляем формулу СУММ(B3:B6) для соответствующих строк.
3. Отформатируйте таблицу командой Автоформат – Классический 2.
Командой Формат→Автоформат→Классический 2.
4. Создайте предложенные диаграммы по образцу.
Выделяем таблицу, выбираем Вставка→Диаграмма , кнопка Мастер диаграмм , следуя предложенным шагам создаем диаграмму.
5. Сохраните работу под именем ДИАГРАММА. xls на дискете.
6. Подготовьте таблицу по образцу. Построить диаграмму .
|
Административный округ |
Численность рабочих |
|
Центральный |
|
|
Северный |
|
|
Северо-Западный |
|
|
Северо-Восточный |
|
|
Юго-Западный |
|
|
Юго-Восточный |
|
|
Западный |
|
|
Восточный |
7. Создайте легенду.
В контекстном меню в параметрах диаграммы выбираем пункт легенда , ставим добавить легенду и выбираем необходимое ее расположение.
8. Отредактируйте легенду, поменяв цвет отдельных элементов.
Щелкаем дважды на квадратик с цветом элемента, задаем новое значение цвета.
9. Изменить содержание или оформление элемента диаграммы.
Выделяем элемент и меняем его свойства.
10. Выполните повороты диаграммы.
В контекстном меню выбираем объемный вид, с помощью соответствующих кнопок поворачиваем диаграмму.
11. Созданную диаграмму можно рассматривать как формулу. Внесите изменения в данные, используемые при ее создании. Что при этом изменится?
Вносим изменения в таблицу, при этом меняется и полученная из нее диаграмма.
Заключение.
В ходе данной работы мы ознакомились с приемами работы в MS-EXCEL. Были изучены способы создания таблиц, их форматирования, создания графиков и диаграмм, их редактирования по необходимым элементам.