Сводные таблицы в экселе задания и решения
Обновлено: 04.07.2024
В методическом пособии даются пошаговые рекомендации для выполнения практической работы. Пособие может быть использовано на уроках дисциплины "Информационные технологии в профессиональной деятельности" для специальности "Техническое обслуживание и ремонт автомобильного транспорта" и других специальностей.К методическим рекомендациям прилагается файл с данными для выполнения самостоятельной части работы.
Работа служит для получения практических навыков по изучению следующих тем:
- манипулирование данными, расположенными на разных листах рабочей книги;
- списки и операции со списками (фильтрация, сортировка);
- использование диалоговых окон для изменения информации в списках;
- структура таблицы (создание и удаление);
- консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;
- построение сводных таблиц.
| Вложение | Размер |
|---|---|
| методические рекомендации к практической работе | 111.5 КБ |
| файл с данными для самостоятельной работы | 68 КБ |
Предварительный просмотр:
СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ
И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ
Практическая работа служит для получения практических навыков по изучению следующих тем:
- манипулирование данными, расположенными на разных листах рабочей книги;
- списки и операции со списками (фильтрация, сортировка);
- использование диалоговых окон для изменения информации в списках;
- структура таблицы (создание и удаление);
- консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;
- построение сводных таблиц.
Цель работы: научиться объединять данные расположенные на разных листах,
формировать структурированные отчеты в табличной форме,
осуществлять поиск и фильтрацию данных в таблицах.
Основные сведения о списках, структуре рабочего
листа, консолидации и сводных таблицах
Список - это упорядоченный набор данных, база данных на рабочем листе. Столбцы списка называются полями, строки – записями. Ведение списка можно осуществлять в диалоговом окне (форме). Над списками можно выполнять такие операции, как фильтрация и сортировка.
В процессе сортировки списка переупорядочиваются строки в соответствии с содержимым одного, двух или трех столбцов.
Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию).
Структура таблицы наиболее полезна для создания итоговых отчетов, в которых не нужно приводить все детали. Один рабочий лист может иметь только одну структуру (горизонтальную, вертикальную или обе). Структура может иметь до восьми уровней вложения.
Консолидация – это комбинирование или накопление информации из двух или более рабочих листов в нескольких рабочих книгах. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой.
Сводная таблица - это динамический итог данных, содержащихся в базе данных. Создается сводная таблица только при помощи специального средства "Мастер сводных таблиц".
Выполнение практической работы
Перед вами стоит задача совместной обработки нескольких таблиц – списков, расположенных на разных листах рабочей книги.
1. Загрузить программу Excel.
2. На листе рабочей книги (Лист1) создать табл. 1 с исходными данными о заказе партий запчастей у некоторой фирмы на год:
Количество шт. в партии
3. Рассчитать цену одного экземпляра по каждому наименованию заказанной продукции путем ввода и последующего копирования формулы.
4. Переименовать "Лист1" в "Заказ". Для этого установить указатель на ярлык "Лист1", нажать правую кнопку мыши, в контекстном меню выбрать команду Переименовать и вместо прежнего имени листа Лист1 ввести новое имя Заказ .
5. Получить итоговую сумму по столбцу "Сумма". Для этого установить курсор в ячейку Е12 и нажать кнопку автосуммирования. Ввести сформированную формулу.
6. Создать структуру построенной таблицы для скрытия детальных числовых данных. Для этого установите курсор внутри таблицы и выполните команду Данные/Группа и Структура/Создание структуры. На экране структуры таблицы щелкните кнопку "-", чтобы скрыть столбцы с числами, а затем кнопку "+" для показа скрытой информации.
- Удалить структуру, выполнив команду Данные/Группа и Структура/Удалить структуру.
- Добавить к существующим листам рабочей книги еще один. Для этого установить указатель на один из ярлыков, нажать правую кнопку мыши и выбрать команду Добавить. В диалоговом окне Вставка выделить значок с названием "Лист" и нажать кнопку "Ok".
- Переименовать "Лист2", "Лист3", "Лист4" в "Январь", "Февраль", "Март" (табл. 2, 3, 4), так как они будут содержать информацию о реализации запчастей за первые три месяца года. Ввод данных осуществлять в соответствии с указаниями следующего пункта.
Январь Таблица 2
Февраль Таблица 3
10. Сгруппировать листы "Январь", "Февраль", "Март" для ввода общей для них информации. Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише . Для ввода индивидуальной для каждой таблицы информации листы разгруппировать путем выбора в контекстном меню команды Разгруппировать листы .
11. Ввести и размножить формулы для подсчета стоимости и итоговых сумм во введенные таблицы.
12. Использовать консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:
- добавить новый лист, переименовать его в "Квартал";
- выделить ячейку на новом листе "Квартал", начиная с которой будут размещены итоговые данные (например, А 1);
- выполнить команду Данные/ Консолидация ;
- в диалоге "Консолидация" выбрать в списке функций функцию Сумма ;
- в строку "Ссылка" ввести абсолютную ссылку на консолидируемые данные (например, Январь!$А$2:$С$12 ) и нажать кнопку "Добавить";
- повторить ввод и добавление данных для ввода всей консолидируемой информации;
- включить флажок "значения левого столбца";
- нажать кнопку "Ok".
13. Изменить некоторые данные в одном из консолидируемых листов, например количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?
14. Установить связанную консолидацию данных. Для этого
вставить новый рабочий лист, переименовать его в "Квартал1", активизировать ячейку начала формирования итоговой таблицы (например, А 1) , выполнить все положения пункта 12, добавив флажок "Создавать связи с исходными данными".
- В полученной структурированной таблице просмотреть скрытые данные, нажав кнопки "2" или "+".
- Изменить некоторые данные в одном из консолидируемых листов, например, количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?
- Построить сводную таблицу, информирующую о сумме проданных деталей по каждому наименованию отдельно. Для этого:
- активизировать рабочий лист "Заказ";
- вызвать мастер сводных таблиц, выполнив команду Данные/ Сводная таблица ;
- в окне "Мастер сводных таблиц – шаг 1 из 4" выбрать источник, откуда будут поступать данные для построения таблицы (первую из предложенных опций: "В списке или в базе данных Microsoft Excel") и щелкнуть по кнопке "Далее>";
- в окне шага 2 ввести область исходных данных для построения сводной таблицы, например, Заказ!$А$1:$F$11, и щелкнуть по кнопке "Далее>";
- в окне шага 3 для определения внешнего вида сводной таблицы требуется в макете сводной таблицы перетащить поле таблицы "Название" в поле макета "столбец", поле таблицы "Квартал" в поле макета "строка", "Сумма" должна быть расположена в поле "данные", а затем щелкнуть по кнопке "Далее>";
- в окне шага 4 установить переключатель "Новый лист" и нажать кнопку "Готово".
- Изменить исходные данные (сначала убрать, а затем добавить одну строку в исходную таблицу) и в контекстном меню (щелчком правой клавиши мыши на поле сводной таблицы) выбрать команду Обновить данные .
- Поменять местами строки и столбцы сводной таблицы. Для этого снова запустить Мастер сводных таблиц и в диалоге шага 3 повернуть макет таблицы на 90 градусов: в строках вывести названия изданий, а в столбцах – квартал, после чего завершить диалог.
- Открыть макет сводной таблицы (3 шаг) и перетащить поле "Название" в область макета "страница", закончить диалог и обратить внимание на изменения в сводной таблице, затем нажать кнопку "Отобразить страницы" панели инструментов "Сводная таблица".
21. Отсортировать данные таблицы "Заказ" по возрастанию цены. Для этого:
- сделать текущей ячейку поля "Цена";
- нажать кнопку инструментального меню "Сортировать по возрастанию".
22. Выполнить многоуровневую сортировку по двум ключам: сначала по цене, потом по названиям в порядке возрастания значений этих ключей. Для этого:
- установить курсор в область данных таблицы "Заказ";
- вызвать команду Данные/Сортировка ;
- в диалоговом окне в область "Сортировать по" ввести первый ключ сортировки "Цена"; в область "Затем по" ввести второй ключ сортировки "Название";
- нажать кнопку "Параметры. " и ознакомиться с возможными вариантами задания параметров сортировки;
- щелкнуть кнопку "Ok".
23. Выполнить подсчет промежуточных итогов по Количеству шт. в партии отдельно по кварталам, предварительно отсортировав данные таблицы "Заказ" по возрастанию номера квартала. Для этого:
- сделать текущей ячейку поля "Квартал";
- нажать кнопку инструментального меню "Сортировать по возрастанию";
- выполнить команду Данные/Итоги ;
- в диалоговом окне команды "Промежуточные итога" в области "При каждом изменении в" выбрать "Квартал", в области "Операция" выбрать "Сумма", в области "Добавить итоги по" выбрать " Количество шт. в партии ";
- щелкнуть кнопку "Ok".
24. Аннулировать промежуточные итоги таблицы "Заказ". Для этого:
- установить указатель мыши на таблицу "Заказ";
- выполнить команду Данные/Итоги ;
- в диалоговом окне команды "Промежуточные итоги" щелкнуть по кнопке "Убрать все".
25. Использовать форму базы данных для просмотра и корректировки записей таблицы "Заказ". Для этого:
26. Использовать форму базы данных для поиска в таблице "Заказ" информации о запчастях, Количество шт. в партии которых превышает 10000. Для этого:
27. Самостоятельно выполнить:
- сортировку данных таблицы "Заказ" по возрастанию значений поля "Номер";
- поиск в таблице "Заказ" информации о запчастях, заказанных в первом квартале с Количеством шт. в партии менее 20000.
28. Использовать автофильтр для вывода в таблице "Заказ" информации только о запчастях, цена которых более 5000 руб. Для выполнения этого задания необходимо:
- отметить область рабочего листа с данными и с заголовками;
- выполнить команду Данные/Фильтр/Автофильтр ;
- раскрыть список на поле "Цена", выбрать пункт "Условие" и ввести выражение "больше 5000";
- щелкнуть кнопку "Ok".
29. Отменить автофильтр, для этого выполнить команду Данные/Фильтр и снять пометку с позиции Автофильтр.
30. Использовать усиленный фильтр для получения данных о запчастях, цена которых менее 5000, заказанных в третьем квартале Количеством шт. в партии более 10000. Для этого:
- скопировать имена столбцов "Цена", "Квартал" и " Количество шт. в партии " в ту часть рабочего листа, которая не содержит данных для поиска: H1,I1,J1;
- в клетки H2,I2,J2 ввести критерии поиска ( 10000, Кв3);
- выполните команду меню Данные/Фильтр/Расширенный фильтр ;
- в диалоге "Расширенный фильтр" задать область, где находятся данные (интервал списка) и область, в которой заданы критерии поиска;
- в группе "Обработка" укажите, что фильтрация будет выполняться на месте;
- щелкните кнопку "Ok".
31. Сохранить рабочую книгу в файле с именем lab3.xls .
32. Для выхода из Excel выберите из меню команду Файл/Выход.
Задания для самостоятельной работы
Проанализируйте данные о произведённых ремонтных работах некоторого автосервиса, в качестве исходных данных используйте данные в файле copir/ 311-315_ИТ_в_ПД /ТО.xls. Результаты по каждому вопросу представьте на отдельном листе.
- Проанализируйте суммы ремонта по видам ремонта
Для этого существует несколько способов:
- Отсортируйте всю таблицу по столбцу вид ремонта.
- Выполните команду Данные/ Итоги просуммируйте Сумму ремонта при каждом изменении вида ремонта. Отобразите только итоги по видам ремонта
Постройте сводную таблицу, где в строках будут виды ремонта, а в области данных сумма ремонта, можно в область столбцов перенести фамилию мастера.
Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.
Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 20%, 3 разряд 30%, 4 разряд 40% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.
Методика выполнения работы
Открыть новую книгу.
Переименовать лист в Картотека .
Подготовить исходные данные (см. табл. 1)
Таблица 1.
Установить курсор в список, выполнить команду меню Вставка → Сводная таблица для вызова Мастера сводных таблиц и диаграмм.
Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel . Выбрать вид создаваемого отчета → Сводная таблица.
Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы.
Разместить поля в макете сводной таблицы:
Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов –
Разряд работающего, Значения – Тариф, О перация – Сумма.
Макет сводной таблицы представлен на рисунке 1.
Рис 1. Макет сводной таблицы
8. На ленте Конструктор выполнить команды: Общие итоги → Включить по столбцам ; Выбрать стиль сводной таблицы. На ленте Параметры : Сводная таблица → Параметры . В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек . Нажать кнопку ОК .
В сводной таблице (рис. 2) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.
Для преобразования сводной таблицы следует:
Установить курсор в область сводной таблицы.
Выполнить команду Параметры (Анализ – MS Excel 2013) → Формулы → Вычисляемое поле
для создания вычисляемого поля.
Рис. 2. Сводная таблица.
3. На рис. 3 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168.
(Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить .
Рис. 3. Создание вычисляемого поля
Установить курсор в область сводной таблицы.
С помощью кнопки Список полей на ленте Параметры откройте макет
сводной табл ицы для корректировки.
Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).
Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата .
На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 4):
Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный.
Нажать кнопку ОК.
Рис. 4. Задание параметров вычисляемого поля
9. Установить курсор в область сводной таблицы на поле Разряд работающего.
10. Создать вычисляемый объект Премия . Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 20%, 3 разряд – 30%, 4 разряд – 40%, 5 разряд – 50%.
На ленте Параметры в ыполнить команду Формулы → Вычисляемый объект (рис . 5) . Указать имя объекта – Премия.
Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы
Нажать кнопку Добавить.
Закрыть окно – кнопка ОК .
Рис. 5. Создание вычисляемого объекта
11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты , сумма вычетов это 13% от суммы заработка и премии.
Выполнить команду Формулы → Вычисляемый объект . Указать имя объекта – Вычеты (рис. 6).
Рис. 6. Создание вычисляемого объекта
В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:
Нажать кнопку Добавить.
Закрыть окно – кнопка ОК.
12. Выполнить команду Параметры → Формулы → Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 7).
Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню Формулы → Вывести формулы , вызывать поле/объект, внести изменения
Рис. 7. Вывод формул
13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.
14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета . Выбрать тип отчета.
15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма .
16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.
17. Сохранить рабочую книгу.
Если Вы считаете, что материал нарушает авторские права либо по каким-то другим причинам должен быть удален с сайта, Вы можете оставить жалобу на материал.Курс повышения квалификации
Дистанционное обучение как современный формат преподавания
Курс профессиональной переподготовки
Математика и информатика: теория и методика преподавания в образовательной организации
Курс повышения квалификации
Современные педтехнологии в деятельности учителя
- Курс добавлен 23.09.2021
- Сейчас обучается 47 человек из 23 регионов
указав свой предмет (категорию), класс, учебник и тему:
Номер материала: ДБ-160487
Не нашли то что искали?
Вам будут интересны эти курсы:
Оставьте свой комментарий
Рособрнадзор откажется от ОС Windows при проведении ЕГЭ до конца 2024 года
Время чтения: 1 минута
Минпросвещения будет стремиться к унификации школьных учебников в России
Время чтения: 1 минута
Руководители управлений образования ДФО пройдут переобучение в Москве
Время чтения: 1 минута
ЕСПЧ запретил учителям оскорблять учеников
Время чтения: 3 минуты
Минпросвещения разрабатывает образовательный минимум для подготовки педагогов
Время чтения: 2 минуты
Правительство предложило потратить до 1 млрд рублей на установку флагов РФ у школ
Время чтения: 1 минута
Подарочные сертификаты
Ответственность за разрешение любых спорных моментов, касающихся самих материалов и их содержания, берут на себя пользователи, разместившие материал на сайте. Однако администрация сайта готова оказать всяческую поддержку в решении любых вопросов, связанных с работой и содержанием сайта. Если Вы заметили, что на данном сайте незаконно используются материалы, сообщите об этом администрации сайта через форму обратной связи.
Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение администрации может не совпадать с точкой зрения авторов.
Задание 1. Консолидация данных по расположению
и по категориям
- Создать две таблицы расчета заработной платы за январь и за февраль.
- Выполнить консолидацию данных из двух таблиц по расположению и сравнить результат с образцом (Рис.1).
Рис.1. Пример консолидации данных по расположению
- Выполнить консолидацию данных по категориям и сравнить результат с образцом (Рис.2).
Технология работы
- В книге Spisok создайте два новых листа и назовите их Консол_распол и Консол_категор .
- Создайте на листе Консол_распол. таблицу расчета заработной платы (за январь (Рис. 9 контент 4.4., без столбца Премия)).
- Скопируйте созданную таблицу в другую область того же самого листа (Рис.1 , на рисунке некоторые столбцы скрыты).
Рис.2. Пример консолидации данных по категориям
- Измените в таблице значения заработной платы.
- В заголовке укажите месяц – февраль.
- Выполните консолидацию данных по расположению:
- установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A14 (Рис.1);
- выполните команду Данные/Консолидация;
- в диалоговом окне Консолидация выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца;
- установите курсор в поле Ссылка;
- выделите блок ячеек A5: H10 (заработная плата за январь);
- нажмите кнопку Добавить;
- в окне Список диапазонов появится ссылка на выделенный диапазон;
- установите курсор в поле Ссылка и очистите поле;
- выделите блок ячеек J5: Q10 (заработная плата за февраль);
- нажмите кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
- нажмите кнопку OK и сравните полученные результаты с образцом (Рис. 1).
- Скопируйте обе таблицы (заработная плата за январь и за февраль) с листа Консол_распол на лист Консол_категор
- Измените вторую таблицу (за февраль) так, как отображено на образце (Рис. 2):
- вставьте новый столбец Премия и заполните его данными;
- добавьте строку с фамилией Дятлов и соответствующими числами.
- Проведите консолидацию данных по категориям (Рис.2):
- установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A13;
- выполните команду Данные/Консолидация;
- выберите в диалоговом окне Консолидация из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца;
- установите курсор в поле Ссылка;
- добавьте в список диапазон A5: H10;
- нажмите кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
- установите курсор в поле Ссылка и очистите его;
- добавьте в список диапазон J5: R11;
- нажмите кнопку OK и сравните полученные результаты с данными на образце (Рис.2).
Задание 2. Построение сводных таблиц
Построить для таблицы (Рис. 1 практикум 4.4.) следующие виды сводных таблиц:
2. Исследовать и применить приемы решения задач с помощью списков и сводных таблиц:
3. Развивать самостоятельность при выполнении задания и анализе результатов работы с числовой информацией
4. Развивать мышление через сравнение и анализ методов обработки числовой информации
5. Воспитывать нормы работы за ПК (правовая культура): правила работы с вычислительной техникой, с информацией, правила техники безопасности
ПРИОБРЕТАЕМЫЕ УМЕНИЯ И НАВЫКИ:
1) Навыки работы с мышкой
2) Навыки работы с встроенными функциями
3) Навыки построения экономической м математической моделей
СРЕДСТВА: инструкционная карта, ПК, электронные таблицы EXCEL , опыт студентов, опыт преподавателя
НОРМА ВРЕМЕНИ : 6 часов
ТЕХНИКА БЕЗОПАСНОСТИ : ЗАПРЕЩАЕТСЯ:
· трогать разъемы соединительных кабелей,
· включать и выключать аппаратуру без указания преподавателя,
· прикасаться к экрану и тыльной стороне монитора,
· класть дискеты, книги, тетради, ручки и т.п. на клавиатуру и монитор.
При длительной работе за ПК необходимо соблюдать следующие санитарные правила :
· при продолжительности работы 1,5 – 2 часа делать перерыв 10 мин. через каждый час;
· в случае возникновения у работающего зрительного дискомфорта и других неблагоприятных ощущений целесообразно выполнять комплекс упражнений для глаз и туловища.
1) При появлении запаха гари немедленно прекратить работу, отключить питание ПК и сообщить об этом преподавателю.
2) Не пытайтесь самостоятельно устранять неисправности в работе аппаратуры.
Вы отвечаете за сохранность рабочего места .
ПЛАН РАБОТЫ:
1) Подготовительный этап
2) Практический этап:
ð Исследовательский этап
ð Исполнительский этап
3) Аналитический этап
4) Домашнее задание
Подготовительный этап: Ответьте устно на вопросы:
1. При вводе числовых данных вместо десятичной дроби вы получаете данные в формате ДАТА. В чем причина? Ваши действия в этом случае.
3. Какие правила следует выполнять при вводе формул?
4. Какие действия необходимо выполнить, что бы настроить перенос слов по слогам внутри ячейки?
5. Какие действия необходимо выполнить, для того чтобы объединить ячейки?
6. Какие действия необходимо выполнить для автозаполнения содержимым ячеек
Практический этап: Ознакомьтесь с теоретическим материалом и выполните предложенные упражнения.
Совокупность данных в виде таблиц полей и записей называется списком или базой данных. MS EXCEL понятия список и база данных взаимозаменяемы. Действия, выполняемые со списком: сортировка (упорядочивание по определенному признаку), фильтрация (выборка данных по условию), автоввод (возможность ускорить ввод повторяющихся элементов списка), проверка вводимых (контроль на соответствие допустимому типу и значению), получение промежуточных итогов, построение сводных таблиц, подбор параметров (выбор из множества решений наиболее оптимального), связывание объектов (зависимость данных одного листа от данных, расположенных на другом листе), консолидация (автоматическое получение итоговых результатов на основе данных, которые могут располагаться в разных местах), импорт и экспорт данных (объединение текстовых и графических объектов из других приложений на рабочих листах).
1. Исследуем приемы работы в режиме списка:
В программе предусмотрены возможности работы со списками как с простыми базами данных. Пользователь имеет возможность поиска данных по ключу, использовать различные фильтры, вести упорядочение списков по значению.
Исследуем возможности программы на примере таблицы лучших теннисистов.
Исследуем прием работы с формой . ФОРМА – своеобразный шаблон записи в списке, состоящий из нескольких полей.
1. Создайте на первом листе под именем «Теннис» таблицу по образцу.
2. Введите заголовок таблицы «15 лучших теннисистов мира»
3. Установите курсорную рамку на любой столбец (поле) таблицы (списка)
4. выполните команду Данные – Форма.
5. На экране появиться диалоговое окно - ФОРМА
6. Данное окно можно использовать для ввода новой записи в список (через кнопку ДОБАВИТЬ ) или удалять существующие в списке записи (через кнопку УДАЛИТЬ ).
7. Режим ФОРМЫ позволяет осуществить выбор данных из списка по критериям (через кнопку КРИТЕРИИ ). В значения критерия можно использовать символ * (указывает произвольное количество неизвестных символов) и ? (указывает один неизвестный символ). При поиске числовых значений можно применять операторы сравнения: >, <, =
8. выход из режима ФОРМЫ осуществляется через кнопку Закрыть или через пиктограмму Х
Упражнение 1 : (используя режим ФОРМА)
1. Добавьте в список запись: 16(21) Томас Мустер Австрия 1611
2. Удалите из списка запись: 12 (13) Марк Филлиппусис Австралия 1992
3. Осуществите поиск спортсменов из Испании:
§ Установите на полосе прокрутки бегунок в самое верхнее положение
§ нажмите кнопку Критерии
§ в поле Страна введите значение Испания
§ нажмите кнопку Далее
§ просмотрите содержимое списка по критерию выбора
4. Осуществите поиск спортсменов Испании, набравших не более 2000 очков (примените операторы сравнения)
5. Выполните поиск всех спортсменов, чьи имена начинаются на букву «С» из Франции
6.Выполните поиск всех спортсменов, чьи имена начинаются на букву «М» с набранными очками не менее 2700 очков
Исследуем прием работы Сортировки данных . Сортировка, или упорядочение, данных в списке может пригодиться для лучшего восприятия записей списка и удобного извлечения сведений. Сортировку в списке можно выполнить сразу по трем уровням.
1. установите курсорную рамку в область списка
2. выполните команду Данные – Сортировка
3. в появившемся диалоговом окне укажите область сортировки и ее параметры
4. выбрав в диалоговом окне команду Параметры… на экране появиться новое диалоговое окно:
В данном окне можно уточнить вид сортировки, задающее направление сортировки: по строкам или столбцам, а так же необходимость сортировки по первому ключу: дни недели, месяцы, числа
Упражнение 2 : Установите параметры сортировки таким образом, чтобы новая база приобрела следующий вид:
Исходная таблица.
Обратите внимание, что таблицу на основе которой будет построена Сводная таблица, непосредственно перед построением необходимо отформатировать как Таблицу. Для этого необходимо выделить исходную таблицу и в закладке Вставка нажать на кнопку Таблица.
Как построить сводную таблицу в Excel.
Сначала необходимо выделить исходную таблицу или диапазон данных. После этого в закладке Вставка выбрать Сводная таблица.
Появляется диалоговое окно Создание сводной таблицы.
В поле Таблица или диапазон можно выбрать диапазон данных или таблицу, на основе которых будет построена Сводная таблица. Это необходимо сделать, если это не сделано перед началом построения Сводной таблицы.
Далее выбираем, куда поместить непосредственно Сводную таблицу. На новый лист или На существующий лист. Как правило Сводную таблицу помещают на новый лист. Если ее поместить на существующий лист, то в этом диалоговом окне, в соответствующем поле Диапазон, можно указать место куда разместить Сводную таблицу.
Он содержит в себе следующие элементы:
- Поля для добавления в отчет. Здесь можно выбрать элементы исходной таблицы (диапазона данных), которые будут отображаться в Сводной таблицы. Чтобы выбрать нужный элемент, напротив него необходимо поставить галочку.
- Фильтры. Здесь находятся элементы, которые будут фильтровать данные, отображаемые в Сводной таблицы.
- Столбцы. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве столбцов.
- Строки. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве строк.
- Значения. Здесь находятся элементы, которые будут отображаться в Сводной таблице в качестве числовых данных.
После того, как выбраны (поставлены галочки) поля для добавления в отчет, они отобразятся в блоках (полях) Фильтры, Столбцы, Строки и Значения. Далее, их необходимо перетащить в соответствующие поля исходя их того, какую Сводную таблицу необходимо получить в результате.
Пункт Отложить обновление макета, позволяет настраивать обновление Сводной таблицы. Если галочка не стоит, настройки сделанные в блоке Поля сводной таблицы отобразятся в Сводной таблице сразу. Если галочка стоит, то только после того, как нажата кнопка Обновить.
Настройки сводной таблицы в Excel.
Итак, рассмотрим настройку Сводной таблицы на нашем примере. В первую очередь выберем поля для отображения в Сводной таблице. В нашем примере галочки нужно поставить напротив всех элементов.
После того, как галочки поставлены, элементы появились в блоках (областях) Фильтры, Столбцы, Строки и Значения.
И уже будет сформирована Сводная таблица.
Как это сделать.
Перетаскиваем элемент Магазин в поле Фильтры.
Наша таблица готова.
В ячейки В1 располагается фильтр. Если нажать на стрелочку фильтра в этой ячейки, появиться возможность выбрать одни или несколько магазинов, после чего в Сводной таблице отобразятся соответствующие этим магазинам продажи. Но предварительно нужно поставить галочку в пункте Выделить несколько элементов.
Самое главное при построение Сводной таблицы, исходя и задачи, правильно выбрать элементы поля для добавления в отчёт. После этого перетащить их в нужные блоки (области).
Если Поля сводной таблице закроются, вернуть их можно вызвав контекстное меню, нажав правой клавишей мыши в поле построенной Сводной таблицы и выбрав пункт: Показать список полей.
Читайте также: