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

Работа добавлена на сайт samzan.net:

Введение

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

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

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

Таблица-список Excel должна удовлетворять следующим требованиям.

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

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

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

Сортировка списков и диапазонов

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

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

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

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

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

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

Для выполнения сортировки списка может быть использованы:

кнопка, расположенная на стандартной панели элементов ,

команда меню Данные

Сортировка по одному столбцу с помощью кнопки Сортировка и фильтр

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

для сортировки символов по возрастанию выбрать команду Сортировка от А до Я
для сортировки символов по убыванию выбрать команду Сортировка от Я до А.

При необходимости выполнить сортировку с учетом регистра в меню кнопки Сортировка и фильтр необходимо выбрать команду Настраиваемая сортировка и в открывшемся окне нажать кнопку Параметры. В появившемся окне Параметры сортировки установить флажок Учитывать регистр.

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

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

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

Упражнение: Выполните сортировку данных списка (исходной таблицы) в алфавитном порядке по наименованию поставщика. Для этого:

Скопируйте содержимое листа Исходный список на новый лист, для чего установите курсор мыши на прямоугольник, находящийся на пересечении линий-указателей строк и столбцов в левом верхнем углу окна документа, при этом указатель мыши примет вид белого крестика.
Щелкните левой кнопкой мыши, при этом цветным фоном будет выделен весь лист
Щелкните правой кнопкой мыши и в контекстном меню выберите команду Копировать, при этом вокруг листа появится плавающая рамка.
Перейдите на новый лист.
Щелкните левой кнопкой мыши в ячейке А1 нового листа , а затем щелкните правой кнопкой мыши и в контекстном меню выберите команду Вставить. Снимите выделение листа, щелкнув левой кнопкой мыши в произвольном месте листа.
Назовите новый лист Сортировка.
Установите курсор в ячейку с данными столбца Наименование поставщика.
На вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр.
В открывшемся меню выполните команду Сортировка от А до Я.

Сортировка по нескольким столбцам с помощью вкладки Данные

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

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

Скопируйте содержимое листа Исходный список на новый лист. Назовите лист Сортировка1.
Установите курсор в ячейку с данными таблицы.
На вкладке Данные нажмите кнопку Сортировка
В окне сортировка в группе Столбец в поле Сортировать по выберите Наименование поставщика.
В группе Сортировка выберите Значения.
В группе Порядок выберите От А до Я.
Нажмите кнопку Добавить Уровень. Появится строка Затем по.
В группе Столбец в поле Сортировать по выберите Дата поставки.
В группе Сортировка в поле выберите Значения.
В группе Порядок выберите От старых к новым.
Нажмите кнопку Добавить Уровень. Появится строка Затем по.
В группе Столбец в поле Сортировать по выберите Сумма заказа.
В группе Сортировка в поле выберите Значения.
В группе Порядок выберите По возрастанию.
Нажмите ОК.

Сортировка по настраиваемым спискам

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

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

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

Упражнение: выполните сортировку данных таблицы по столбцу Наименование товара в соответствии со следующим списком товаров – шкаф, стол, тумба, стул. Для этого:

Скопируйте содержимое листа Исходный список на новый лист. Назовите лист Сортировка2.
Установите курсор в ячейку с данными столбца Наименование товара.
На вкладке Главная в группе Редактирование раскройте кнопку Сортировка и фильтр.
В открывшемся окне выберите команду Настраиваемая сортировка.
В окне сортировка в группе Столбец в поле Сортировать по выберите Наименование товара.
В группе Сортировка выберите Значения.
В группе Порядок выберите Настраиваемый список.
В окне Списки в группе Списки выберите Новый список.
В группе Элементы списка введите список шкаф, стол, тумба, стул.
Нажмите ОК в окне Списки.
Нажмите ОК в окне Сортировка.

Промежуточные итоги

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

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

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

Упражнение: в качестве промежуточных итогов выведите общую сумму заказа по каждому наименованию поставщика. Для этого:

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

Упражнение: в дополнение к существующим промежуточным итогам выведите среднюю сумму поставки по каждой дате. Для этого:

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

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

Структура данных рабочего листа

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

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

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

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

Обеспечение поиска и фильтрации данных

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

Отфильтровать список - значит скрыть все строки, за исключением тех, которые удовлетворяют заданным условиям отбора. Excel предоставляет две команды: Автофильтр - для простых условий отбора и Расширенный фильтр - для более сложных критериев.

Применение Автофильтра

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

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

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

Упражнение: с помощью автофильтра выберите из списка данные о поставленных товарах поставщиком АО «Макс» в марте месяце на сумму менее 100000 руб. Для этого:

Установите курсор в ячейку с данными таблицы на листе Исходный список.
Нажмите кнопку Фильтр на вкладке Данные.
Нажмите кнопку со стрелкой в правом углу ячейки «Наименование поставщика».
В раскрывшемся окне снимите флажок Выделить все, а затем установите флажок рядом с наименованием АО «Макс», нажмите ОК.
Нажмите кнопку со стрелкой в ячейке Дата поставки и в раскрывшемся окне оставьте флажок рядом с наименованием месяца Март, нажмите ОК.
Нажмите кнопку со стрелкой в ячейке Сумма поставки.
В раскрывшемся окне выберите команду Числовые фильтры и в следующем окне выберите Меньше.
В раскрывшемся окне Пользовательский автофильтр в области Сумма поставки установите меньше 100000, нажмите ОК.
для удаления всех автофильтров и их кнопок еще раз нажмите кнопку Фильтр на вкладке Данные.

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

Применение расширенного фильтра

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

Команда Расширенный фильтр позволяет:

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

Задание диапазона условий

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

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

Условия на одной строке считаются соединенными логическим оператором И.
Условия на разных строках считаются соединенными логическим оператором ИЛИ.

Примечание: при каждом выполнении команды «Расширенный фильтр» просматривается полный список в таблице, а не текущее множество ранее отфильтрованных строк.

Упражнение:. с помощью расширенного фильтра выберите товары с ценой >5000 и <20000 руб. Для этого:

Для удобства применения расширенного фильтра скопируйте часть строк (со 2-ой по 29) исходного списка на новый лист Фильтр, в ячейку А1. Если в результате копирования просматриваются не все значения – увеличьте ширину столбцов
Вставьте 5 строк выше таблицы, для чего выделите первые 5 строк исходной таблицы и нажмите стрелку рядом с командой Вставить на вкладке Главная. Далее выберите Вставить строки на лист.
Скопируйте название графы Цена товара в ячейки В1 иС1.
В ячейке В2 введите с клавиатуры >5000, в ячейку С2, соответственно, <20000.
Установите курсор в любую ячейку с данными исходного списка (таблицы).
Раскройте меню Данные и выберите команду Дополнительно. При этом появилось диалоговое окно Расширенный фильтр и вся таблица выделена подвижной рамкой
В раскрывшемся окне Расширенный фильтр в области Обработка выберите Фильтровать список на месте.
Проверьте, что в окне Исходный диапазон указан блок ячеек, соответствующий всей выделенной таблице
В области Диапазон условий нажмите цветную кнопку справа, затем выделите блок ячеек В1:С2 на рабочем листе и вновь нажмите цветную кнопку.
В окне Расширенный фильтр нажмите ОК.

Упражнение: в дополнение к первым двум условиям выберите товары с ценой >5000 и <20000 руб по поставщику с кодом 002. Для этого:

Скопируйте название графы Код поставщика в ячейку А1.
В ячейку А2 введите с клавиатуры 002.
Установите курсор в любую ячейку с данными исходного списка (таблицы).
Раскройте меню Данные и выберите команду Дополнительно.
В раскрывшемся окне Расширенный фильтр в области Обработка выберите Фильтровать список на месте.
Проверьте, что в окне Исходный диапазон указан блок ячеек, соответствующий всей выделенной таблице
В области Диапазон условий нажмите цветную кнопку справа, затем выделите блок ячеек А1:С2 на рабочем листе и вновь нажмите цветную кнопку.
В окне Расширенный фильтр нажмите ОК.

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

Упражнение: с помощью расширенного фильтра выберите товары с суммой заказа >200000 руб. или с суммой поставки <10000 руб. Для этого:

Скопируйте название графы Сумма заказа в ячейку Е1, а название графы Сумма поставки – в ячейку F1.
В ячейку Е2 введите с клавиатуры >200000, в ячейку F3 введите <10000.
Установите курсор в любую ячейку с данными исходного списка (таблицы).
На вкладке Данные и выберите команду Дополнительно.
В раскрывшемся окне Расширенный фильтр в области Обработка выберите Фильтровать список на месте.
Проверьте, что в окне Исходный диапазон указан блок ячеек, соответствующий всей выделенной таблице
В области Диапазон условий нажмите цветную кнопку справа, затем выделите блок ячеек E1:F3 на рабочем листе и вновь нажмите цветную кнопку.
В окне Расширенный фильтр нажмите ОК.
Просмотрите результат поиска.
Для того, чтобы просмотреть исходный список после фильтрации данных нажмите кнопку Очистить на вкладке Данные.

Расширенный фильтр с использованием вычисляемых значений

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

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

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

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

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

В ячейку Н1 введите текст «Среднее значение».
В ячейку Н2 введите формулу: =СРЗНАЧ(J7:J33).
В ячейку J1 введите заголовок для диапазона условий: «Выше среднего».
В ячейку J2 введите формулу: =J7>$H$2, для чего на клавиатуре нажмите кнопку со знаком = (равно), щелкните мышью в ячейке J7, введите с клавиатуры знак > (больше), щелкните мышью в ячейке Н2, затем в ячейке J2 (или в строке формул) выделите курсором адрес Н2 и нажмите на клавиатуре функциональную клавишу F4, чтобы он стал абсолютным, после чего нажмите клавишу Enter. Формула условия сравнивает со средним значением значение ячейки J7, т.к. она является первой ячейкой в столбце «Сума заказа» неотфильтрованного списка, и автоматически со средним значением будут сравниваться все нижерасположенные ячейки этого столбца.
Выделите блок ячеек A6:N6 (шапку таблицы) и скопируйте его в ячейку А37 данного листа для вывода результатов поиска. Копировать можно не всю шапку таблицы, а отдельные названия граф в соответствии с требованиями пользователя.
Установите курсор в ячейку с данными исходной таблицы.
На вкладке Данные и выберите команду Дополнительно. При этом появилось диалоговое окно Расширенный фильтр и вся таблица выделена подвижной рамкой.
В раскрывшемся окне Расширенный фильтр в области Обработка выберите Скопировать результат в другое место.
Проверьте, что в окне Исходный диапазон указан блок ячеек, соответствующий всей выделенной таблице.
В области Диапазон условий выделите блок ячеек J1:J2 на рабочем листе .
Установите курсор область Поместить результат в диапазон, а затем на рабочем листе укажите с помощью мыши диапазон ячеек А37:N37.
Нажмите кнопку ОК в окне Расширенный Фильтр.

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

В ячейку L1 введите заголовок для диапазона условий: «Ниже среднего».
В ячейку J2 введите формулу: =L7< СРЗНАЧ($L$7:$L$33), для чего нажмите на клавиатуре кнопку со знаком = (равно), щелкните мышью в ячейке L7, нажмите на клавиатуре кнопку со знаком < (меньше), в строке формул нажмите кнопку fx (вставить функцию), в окне Мастер функций выберите функцию СРЗНАЧ (категория – Статистические), нажмите ОК. В окне Аргументы функции нажмите цветную кнопку и опции Число1, выделите блок ячеек L7:L33 в таблице, затем в окне Аргументы функции выделите курсором появившуюся надпись L7:L33 и нажмите на клавиатуре функциональную кнопку F4, чтобы адрес указанного диапазона ячеек стал абсолютным, после чего вновь нажмите цветную кнопку справа. В окне Аргументы функции нажмите ОК.
Установите курсор в ячейку с данными исходной таблицы.
На вкладке Данные и выберите команду Дополнительно. При этом появилось диалоговое окно Расширенный фильтр и вся таблица выделена подвижной рамкой.
В раскрывшемся окне Расширенный фильтр в области Обработка выберите Скопировать результат в другое место.
Проверьте, что в окне Исходный диапазон указан блок ячеек, соответствующий всей выделенной таблице.
В области Диапазон условий выделите блок ячеек L1:L2 на рабочем листе.
Установите курсор область Поместить результат в диапазон, а затем на рабочем листе укажите с помощью мыши диапазон ячеек А37:N37.
Нажмите кнопку ОК в окне Расширенный Фильтр.

Анализ данных с помощью сводных таблиц

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

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

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

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

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

Упражнение. Создать сводную таблицу с подведением итогов о поставке товаров поставщиками по видам продукции и количеству заказанного и поставленного товара магазинам-закупщикам. Для этого:

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

При помощи мыши перетащите поле «Город» (3-е сверху) в область Фильтр отчета, поле «Наименование товара» - в область Название строк, поле «Наименование поставщика» - в область Названия Столбцов, поле «Кол-во заказанного товара» - в область Значения, затем поле «Кол-во поставленного товара» - в область Значения.
Установите курсор в любую ячейку сводной таблицы, затем в группе Сводная таблица на вкладке Параметры нажмите кнопку Параметры
В открывшемся окне на вкладке Вывод в области Экран включите параметр Классический макет сводной таблицы (разрешено перетаскивание полей). Нажмите ОК.

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

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

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

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

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

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

Выделите ячейку В3 сводной таблицы.
С клавиатуры введите новый текст «Поставщик» (можно использовать функциональную клавишу F2 для редактирования содержимого ячейки).
Поместите курсор мыши в макет сводной таблицы в область Значения и нажмите стрелку на названии поля Сумма по полю Кол-во заказанного товара. В открывшемся окне выберите команду Параметры полей значений.
В открывшемся окне Параметры поля значений в опции Пользовательское имя введите текст «Заказанный товар». Нажмите ОК.
Аналогичным способом измените текст названия поля «Сумма по полю Кл-во поставленного товара» на текст «Поставленный товар»

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

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

Установите курсор в любую ячейку созданной сводной таблицы. (ели при этом на экран не выводится окно Список полей сводной таблицы, то необходимо на вкладке Параметры в группе Показать или скрыть нажать кнопку Список полей).
Для очистки макета оттащите поля, расположенные в нижней части окна Список полей сводной таблицы в любое место окна.
При помощи мыши перетащите поле «Наименование товара» в область Названия строк, поле «Сумма заказа» - в область Значения и сделайте щелчок левой кнопкой мыши по данному полю. В открывшемся окне выберите команду Параметры полей значений…, затем в опции Пользовательское имя введите с клавиатуры «Сумма заказа», после чего на вкладке Операция выберите Среднее и нажмите ОК. Перетащите поле «Сумма поставки» - в область Значения и сделайте щелчок левой кнопкой мыши по данному полю. В открывшемся окне выберите команду Параметры полей значений…, затем в опции Пользовательское имя введите с клавиатуры «Сумма поставки», после чего на вкладке Операция выберите Среднее и нажмите ОК.
Установите курсор в сводную таблицу.
Выберите в группе Сводные таблицы вкладку Параметры, нажмите кнопку Параметры.
В открывшемся окне Параметры сводной таблицы на вкладке Итоги и фильтры удалите флажки Показывать общие итоги для столбцов и Показывать общие итоги для строк. Нажмите ОК

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

Установите курсор в любую ячейку созданной сводной таблицы..
Очистите макет предыдущей сводной таблицы
При помощи мыши перетащите поле «Наименование поставщика» в область Названия строк, поле «Сумма поставки» - в область Значения и сделайте щелчок левой кнопкой мыши по данному полю. В открывшемся окне выберите команду Параметры полей значений…, затем в опции Пользовательское имя введите с клавиатуры «Сумма поставки» и нажмите ОК.
При помощи мыши вторично перетащить поле «Сумма поставки» в область Значения и сделайте щелчок левой кнопкой мыши по данному полю. В открывшемся окне выберите команду Параметры полей значений…, затем в опции Пользовательское имя введите с клавиатуры «Доля поставщика в общей сумме», в этом же окне выберите вкладку Дополнительные вычисления. В опции Дополнительные вычисления нажмите стрелку вниз и выберите Доля от общей суммы. Нажмите ОК

Групповые операции в сводных таблицах.

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

Упражнение. Создать сводную таблицу с данными о суммах поставленных товаров в определенные периоды времени (месяцы), для этого

Установите курсор в любую ячейку созданной сводной таблицы.
Для очистки макета оттащить поля в любое место окна
При помощи мыши перетащите поле «Наименование поставщика в область Фильтр отчета «Наименование товара»- в область Названия строк, поле «Дата поставки» - в область наименования столбцов, поле «Сумма поставки» - в область Значения
Установите курсор в ячейку В3 сводной таблицы и щелкните правой кнопкой мыши для вызова контекстного меню.
В контекстном меню выберите команду Группировать.
В открывшемся окне Группирование в опции С_шагом выберите Месяцы. Нажмите ОК.

Создание вычисляемых полей в сводных таблицах

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

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

Установите курсор в любую ячейку созданной сводной таблицы.
Для очистки макета оттащите поля в любое место окна.
При помощи мыши перетащите поле «Наименование товара» в область Названия строк, поле «Сумма поставок» - в область Значения.
В макете сводной таблицы в области Значения сделайте щелчок левой кнопкой мыши по расположенному там полю. В открывшемся окне выберите команду Параметры полей значений. В открывшемся окне Параметры поля значений в опции Пользовательское имя введите с клавиатуры «Сумма поставок в рублях», затем нажмите кнопку Числовой формат в нижней части окна. В открывшемся окне Формат ячеек в опции Числовые форматы выберите Денежный, в опции Обозначение установите р., нажмите ОК. Нажмите ОК в окне Параметры поля значений.
Установите курсор в любую ячейку созданной сводной таблицы.
На вкладке Параметры в группе Сервис нажмите кнопку Формулы. В открывшемся окне выберите команду Вычисляемое поле.
В открывшемся окне Вставка вычисляемого поля в окне Имя введите «Сумма поставок в евро», затем переведите курсор в окно Поля и сделайте двойной щелчок по полю Сумма поставок (имя данного поля будет отражено в окне Формула после знака «=»), нажмите знак разделить (/) и введите с клавиатуры значение текущего курса евро. Нажмите ОК.
.В макете сводной таблицы в области Значения сделайте щелчок левой кнопкой мыши по второму появившемуся там полю. В открывшемся окне выберите команду Параметры полей значений. В открывшемся окне Параметры поля значений в опции Пользовательское имя введите с клавиатуры «Сумма поставок в евро», затем нажмите кнопку Числовой формат в нижней части окна. В открывшемся окне Формат ячеек в опции Числовые форматы выберите Денежный, в опции Обозначение установите евро., нажмите ОК. Нажмите ОК в окне Параметры поля значений.

1. Административная и гражданско-правовая ответственност
2. Вечерах на хуторе По торговым сёлам Реженского уезда было сделано от земского суда следующее объявл
3. Педагогика 1983 ББК 88 Л47 Печатается по рекомендации Редакционноиздательского совета Ака
4. ИЗУЧЕНИЕ ХАРАКТЕРИСТИК ЛОГИЧЕСКИХ ЭЛЕМЕНТОВ ТТЛ 1
5. Тестовые вопросы по Теории электрической связи
6. Осенние гости Автор сценария- Щербакова Инна Викторовна музыкальный руководитель МД
7. На стол поставили две одинаковые бутылки наполненные равным количеством воды комнатной температуры
8. Часто бывает что выбор оказывается ошибочным и тогда человек пожинает плоды своего неправильного решения.
9. Оценка эффективности рекламной кампании на примере компании ООО
10. Модульна навчальна програма з дисципліни Педагогіка спорту для студентів V курсу освітньокваліфі
11. Государственная служба Начало карьеры СЮВитте
12. Тема 6. Риторика і мистецтво презентації Види публічного мовлення
13. Реферат на тему- Розвиток школи виховання і педагогічних ідей у середньовічній Європі в епоху Відроджен
14. Профилактика употребления наркотических и психотропных веществ
15. Силовые способности определяются мышечными напряжениями и соответствуют различным формам изменения
16. Дирижер
17. 1 Назовите основоположника объективного идеализма в Европейской философии- А Гераклит В Демокрит
18. 2013 XIX Кубок Любителей Бильярда Отборочный раунд Группа А ’
19. Создание методик измерений и испытание технических средств, применяемых в жилых, коммерческих зонах с малым энергопотреблением
20. Разработка программы в различных языках программирования Цель- Формирование практических умений по разр

Материалы собраны группой SamZan и находятся в свободном доступе

Будь умным!