Группирование данных и создание итоговой строки
ТАБЛИЧНЫЙ РЕДАКТОР MICROSOFT EXCEL
ЛАБОРАТОРНАЯ РАБОТА № 1
Предварительные сведения об электронных таблицах.
После активизации пакета на экране появляется основная таблица, столбцы которой помечены латинскими буквами, строки числами. Каждая ячейка имеет относительный адрес (типа А1) и абсолютный ($A$1). Ячейке можно присвоить имя, ввести в нее текст, число, дату или формулу.
При вводе текста можно выбрать шрифт, его размер и другие параметры, как это делается в WORD'е. Для размещения заголовков можно, выбрав пункты меню формат> ячейки> выравнивание>, включить режим переноса по словам. Для корректировки значений можно использовать пункты меню формат> ячейка> число. Первым символом формулы является знак «=». Аргументами формулы являются константы и адреса, или имена, ячеек. Для ввода арифметических операций используются знаки +, –, * (умножение), / (деление), ^ (возведение в степень). Например, в А1 введем =В1*В2+С1, нажмем на кнопку, помеченную зеленой галочкой. Если значение ячейки В1=2, В2=3, С1=10, то результат вычисления по этой формуле запишется в А1. При записи формулы можно использовать также многочисленные математические, статистические, финансовые и другие функции. Для ввода функций нажимаем на кнопку fx.
Задача 1. Создать таблицу из 10 строк, столбцы которой имеют имена «фио», «часы», «за час», «сумма». В первый столбец ввести фамилии, во второй и третий числа, а во вторую строку четвертого столбца формулу.
Операции с объектами.
В качестве объектов используются ячейки, столбцы и строки, а также прямоугольные блоки ячеек (таблицы данных) и пункты меню.
Для указания выполняемых операций используется клавиатура — управляющие клавиши Еnter (ввести), Еsc(отменить), клавиши модификации режима Shift, Ctrl, Alt, нажимаемые вместе с другими клавишами или одновременно с мышью, а также мышь и меню.
Любую ячейку можно активизировать, поместив в нее курсор мыши и нажав на левую кнопку. Для активизации столбца или строки необходимо активизировать первую ячейку и, нажав на Shift, переместить курсор мыши на последнюю из выделяемых ячеек. Для выделения блока аналогично поступаем с верхней левой и правой нижней ячейками.
Активизированные блоки (столбцы, строки) можно перемещать и копировать вместе с их содержимым. Для перемещения зацепляем курсором мыши за соответствующую сторону блока и, не отпуская нажатой левой кнопки мыши, перемещаем курсор. Для копирования одновременно нажимаем на Ctrl.
Задача 2. Переместить созданную таблицу, переместить отдельно столбец и строку. Копировать таблицу на новое место.
Перемещение объекта можно совершить также через буфер, для этого следует активизировать его, нажать Ctrl + Insert (копирование) или Shift + Del (перемещение), переместить курсор и нажать Shift + Insert, извлекая из буфера.
Задача 3. Переместить таблицу на новое место с помощью буфера. Копировать таблицу на новое место с помощью буфера.
Таблицу можно дополнять новыми столбцами и строками (вставка> столбец или строка) или удалять их содержимое (выделить и нажать Del) или их самих (правка> удалить).
Задача 4. Добавить новый столбец в таблицу с заголовком «сумма». Удалить столбцы и строки дубликатов таблицы.
Операцией, значительно расширяющей возможности Excel, является размножение содержания объекта. Для этого служит маркер заполнения, находящийся в правом нижнем углу выделенного объекта. Для размножения содержания объекта совмещаем курсор мыши с маркером и, не отпуская нажатую кнопку, перемещаем курсор.
Задача 5. Ввести в ячейку А1 число 1 и размножить.
Задача 6. Ввести в В1 число 1, а в В2 — 1,2. Размножить содержимое этих двух ячеек по столбцу.
Задача 7. Ввести в А1 слово «январь» и размножить по столбцу.
Задача 8. Ввести в С1 формулу =В1*В1 и размножить по столбцу.
Другой важной возможностью является присвоение имен ячейкам.
Задача 9. Перейти на Лист 2, выбрав соответствующую метку внизу. Ввести в ячейку А1 «», в А2 — «Ссуда», А3 — «Дата выдачи ссуды», А4 — «Дата возврата ссуды», А5 — «Процентная ставка», в А6 — «Сумма к возврату». В столбец D, начиная со второй строки, ввести последовательно краткие обозначения переменных S, D0, DV, p, SV. В столбец Е — значения переменных 200, 01.01.98, 01.05.99, 10%. Выделить столбцы D и Е. Далее с помощью действий вставка> имя>создать присвоить имена ячейкам столбца Е, используя обозначения столбца D, которые теперь можно использовать в формуле в ячейке Е6:
=S*(1+p)^((DV-D0)/365).
| 3. Создание и корректировка таблиц. Для создания таблицы сделаем следующее: | |
| — | перейдите на Лист 3; |
| — | увеличьте вдвое высоту строк 1 и 2 |
| — | в строку 1 введите «Отчет», а в столбцы строки 2 — «Поставщик», «Товар», «Кол. по контракту»,«Факт», «Остаток»; |
| — | Отцентрируйте заголовок «Отчет», активизировав для этого в первой строке ячейки с А до G, и нажмите кнопку панели управления с буквой «а» в центре; |
| — | выбрав формат>ячейки>шрифт, установите различные параметры шрифта; |
| — | выбрав формат>ячейки>выравнивание, установите флажок переносить по словам; |
| — | заполните три строки таблицы данными; |
| — | в первую ячейку столбца «остаток» введите формулу для подсчета остатка и размножьте на весь столбец; |
| — | внизу таблицы создайте новую строку и введите слово «Итого»; |
| — | Активизируйте ячейку столбца «Остаток» и нажмите кнопку a>Enter; |
| — | выделите первую строку после закрепляемого заголовка и выберите окно>закрепить область. |
| Построение таблицы закончено. |
Создавать и корректировать таблицы можно с помощью форм. Это особенно удобно для таблиц большого размера. В Excel предусмотрена возможность автоматического создания форм, связанных с данной таблицей.
Форма — это способ представления на экране отдельных строк текста. Форма позволяет добавлять новые строки в таблицу, удалять строки, просматривать таблицу построчно вниз или вверх, искать необходимые строки по предлагаемым критериям поиска.
Для создания формы достаточно поместить курсор внутрь таблицы и набрать данные>форма. Для последовательного просмотра строк таблицы (записей) используем кнопки «назад» и «далее». Для поиска нужных записей нажать кнопку «критерий», и после набора критерия поиска — кнопки «назад» или «далее». Для добавления новых строк (в конец таблицы) нажать кнопку «добавить», для удаления текущей строки — «удалить»
Задача 10. Добавить не менее 9 строк в таблицу, используя соответствующую форму. Удалить введенные ранее три строки. Испытать форму во всех возможных режимах. В процессе работы выяснить: как определить номер текущей записи, как стереть введенный ранее критерий поиска?
Консолидация данных.
Пусть имеются несколько однотипных таблиц, которые необходимо слить в одну таблицу. Чаще всего эти таблицы содержат входную информацию. Например, имеются результаты опроса, проведенного сотрудниками и представленные в виде таблиц, содержащих данные о профессии, возрасте и доходах опрашиваемых. Необходимо объединить эти сведения в общую таблицу, при этом количественные данные о каждой профессии, т.е. возраст и доход, должны объединяться с использованием одной из имеющихся функций. Например, нас могут интересовать сумма или среднее значение этих величин.
| Для выполнения консолидации: | |
| — | создайте три таблицы, тип которых указан выше; |
| — | Активизируйте ячейку, которая будет левым верхним углом таблицы; |
| — | выберите данные>консолидация; |
| — | в списке функция укажите требуемую функцию; |
| — | Активизируйте окно ссылка и наберите координаты первой области в окне ссылка, нажмите кнопку добавить; |
| — | повторите последний пункт для всех исходных таблиц; |
| — | установите флажок значение левого столбцаи нажмите ОК. |
Задача 11. Пусть имеются наряды на выполненные работы, в каждом из которых указаны фамилии и отработанные часы. С помощью операции консолидации построить консолидированную таблицу, используя три различные функции.
Сводные таблицы.
В ряде случаев необходимо представить данные, помещенные в одной таблице, в серии таблиц. Это называется представлением данных в различных разрезах. Например, таблицу ПОСТАВКИ (поставщик, потребитель, товар, количество) необходимо представить в разрезе поставщиков, т.е. в виде последовательности таблиц, в каждой из которых включены данные только по одному поставщику.
| Для построения таблиц необходимо: | |
| — | установить курсор в левом верхнем углу расположения будущей сводной таблицы; |
| — | вызвать «мастер сводных таблиц» выполнив данные>сводная таблица; |
| — | нажать кнопку далее и указать диапазон ячеек, занятый исходной таблицей; |
| — | нажать кнопку далее и создать макет сводной таблицы; |
| — | нажать кнопки далее и готово. |
После построения сводных таблиц на экране появится панель инструментов для управления сводной таблицей. С помощью кнопки панели «мастер сводных таблиц» можно изменить структуру таблиц (используя крайнюю левую кнопку панели).
Если исходные данные изменяются, то можно обновить результирующие данные, активизируя данные, которые надо обновить, нажав на кнопку «!» на панели «мастера сводных таблиц» или выбрав соответствующий пункт в меню, вызываемого нажатием правой кнопки мыши.
Задача 12. Создать таблицу ПОСТАВКИ, создать сводные таблицы, представляющие данные в различных разрезах. Изменить структуру таблицы, используя «мастер сводных таблиц». Изменить исходные данные и сводную таблицу.
Замечание. Чтобы создаваемая таблица была представительной, необходимо чтобы в ней содержалось m1m2m3 строк, где m1 — число различных значений поля «поставщик», а m2 и m3 — аналогичные значения для полей «потребитель», «товар».
Задача 13. Создать таблицу регистрации температур воздуха, состоящую из полей: месяц, число месяца, город, температура. Получить сводную таблицу данных о температурах в масштабе месяцев.
Сортировка данных.
Многие методы обработки данных и поиска работают эффективнее с отсортированными данными. В Excel имеется простой способ сортировки строк таблицы. Чтобы им воспользоваться, достаточно указать столбец, который будет ключом сортировки, и выбрать пункты меню данные>сортировка. Предусмотрена возможность задания одновременно до трех ключей сортировки.
Задача 14. Отсортировать таблицу ПОСТАВКИ по столбцу «поставщик».
Группирование данных и создание итоговой строки.
В ряде случаев бывает необходимым разбить таблицу на группы, включив в каждую группу все строки с одним и тем же значением одного из столбцов. Например, все данные о банковских вкладах желательно разбить на группы, включив в каждую группу данные об определенном виде вклада (депозит, облигация, срочный вклад и т. д.). В Excel имеется возможность указать столбец группировки и подсчитать итоговые данные для каждого значения из этого столбца.
Переместите таблицу на новый лист, используя буфер. Отсортируйте таблицу по полю группировки. Укажите это поле и пункты меню данные>итоги, вслед за тем выберите поле подсчета и требуемую операцию (суммирования, осреднения и т. п.).
Задача 15. В таблице ПОСТАВКИ просуммировать «количество» по каждому значению поля «поставщик».
Фильтрация данных.
Фильтр может быть создан в любом столбце таблицы. Для его создания необходимо активизировать часть столбца для размещения списка выбора фильтра, причем размещать его надо прямо поверх текста столбца. Затем переместить таблицу с данными на новый лист и выбрать пункты меню данные>фильтр> автофильтр. Этим создается фильтр, который можно использовать для поиска строк таблицы по запросам. Повторное выполнение указанных действий уничтожает фильтр.
Задача 16. Для таблицы ПОСТАВКИ в столбце «поставщик» создать автофильтр и реализовать поиск по всевозможным условиям. Обратите внимание на возможность использования символов «?» и «*».
9. Расширенный фильтр.
При расширенной фильтрации для отбора строк таблицы используется вспомогательная таблица (фильтр), где указываются некоторые из столбцов и строк исходной таблицы. Например, можно создать таблицу:
| Поставщик | Товар |
| ООО «Павлин» | мороженое |
| ЗАО «Энергия» | моторы |
Для этого скопируйте часть исходной таблицы данных, изменив ее содержание, если необходимо.
Установите курсор мыши в пределах исходной таблицы и выберите пункты меню данные> фильтр> расширенный фильтр. Введите ссылки на фильтр в открывшемся окне «расширенный фильтр» и нажмите кнопку ОК.
Задача 17. Создайте расширенный фильтр для своей таблицы и испытайте его для различных запросов и режимов.
Указанный выше фильтр наиболее простой из возможных. Прежде всего, в фильтре каждое поле исходной (фильтруемой) таблицы можно представить дважды, а в строке под ней могут быть использованы числовые значения поля со знаками неравенства (<, >, <=, <=). Это дает возможность записать неравенство 150 < количество < 250 в виде таблицы:
| Количество | Количество |
| >150 | <250 |
С помощью таблицы
| Количество | Количество | Товар |
| >150 | <250 | мороженое |
можно выделить записи о поставках мороженого в количестве, удовлетворяющем неравенству 150 < количество < 250. Это условие можно записать также с помощью следующего логического выражения (товар=’мороженое’)И(150<количество<250).
Можно создать таблицу-фильтр, содержащую несколько строк. Каждой строке соответствуют элементарные условия, объединяемые союзом «И», а условия соответствующие различным строкам объединяются союзом «ИЛИ». Например, таблица-фильтр
| Количество | Количество | Товар |
| >150 | <250 | мороженое |
| >200 | вафли |
определяет все записи, удовлетворяющие логическому условию ((150<количество<250) И (товар=’мороженое’)) ИЛИ ((количество >200) И (товар=’вафли’)), т. е. те записи, которые относятся к сделкам по поставке мороженного в количестве от 150 до 250 единиц или поставкам вафель в количестве превосходящем 200 единиц.
Задача 18. Создать фильтры, указанные выше, и профильтровать записи исходной таблицы ПОСТАВКИ.
Использование функций для работы с таблицами.
Для работы с таблицами (базами данных) в Excel предлагается несколько встроенных функций, с помощью которых можно подсчитать сумму элементов заданного столбца (поля) – БДСУММ, среднее значение – ДСРЗНАЧ, наибольшее и наименьшее значение – ДМАКС и ДМИН и другие. Способ обращения ко всем перечисленным функциям унифицирован. Он включает три аргумента: имя базы данных (таблицы), имя поля, по значениям которого происходят вычисления, и адрес таблицы-фильтра, задающей подмножество записей, по которым и происходит расчет. Например: