Вычисление итоговых данных в списках

Создадим документ следующего вида:

Для создания такого документа необходимо выполнить следующие действия:

1. Создайте новую книгу. Для этого откройте вкладку Файл/Создать

2. Выделите ячейки A-F. Выполните команду Главная / (Ячейки) Формат / Ширина столбца. Задайте ширину столбца 13 см и нажмите кнопку ОК.

3. В первой строке выделите ячейки A-F.

4. На панели инструментов Главная (Выравнивание) выполнить команду Объединить и поместить в центре и напечатайте заголовок(размер шрифта 14 пунктов)

5. Задайте строкам 3-7 нужный вид. Для этого выделите ячейки A3:E7. На панели инструментов Главная/(Шрифт) выполните команду Границы и выберете нужные границы.(Или на панели инструментов Главная (Шрифт) выполнить команду Границы и выберете нужные.)

6. Напечатайте названия колонок. Для этого на панели инструментов Главная/Шрифт задайте тип шрифта Полужирный, формат абзаца По правому краю и напечатайте названия колонок.

7. Аналогично напечатайте названия строк.

8. Введите значения ячеек таблицы.

9. Вставьте функции в ячейки E4 - E6. Для этого выделите ячейку E4, выполните команду Формулы/(Библиотека функций)Вставить функцию, в диалоговом окне выберите функцию СУММ нажмите кнопку ОК, в окне Число1 задайте ссылки на ячейки B4: D4 и нажмите кнопку ОК. Аналогично вставьте функции в ячейки E5 - E6, задав нужные ссылки на ячейки. (Или выделите ячейки B4:D4 и выполните команду Формулы/(Библиотека функций)Автосумма/Сумма. Аналогично выполняется и для остальных ячеек.)

10. Вставьте функции в ячейки B7 - E7. Для этого выделите ячейку B7, выполните команду Формулы/(Библиотека функций)Вставить функцию, в диалоговом окне выберите функцию СУММ нажмите кнопку ОК, в окне Число1 задайте ссылки на ячейки B4: В6 и нажмите кнопку ОК. Аналогично вставьте функции в ячейки C7 - E7, задав нужные ссылки на ячейки.

11. Сохраните документ на диске С: в папке Учебные документы в файле с именем Функция.

Многоуровневая сортировка данных

В Microsoft Excel 2010 можно производить многоуровневую сортировку, когда совпадают одно или несколько полей.

Создадим документ следующего вида и выполним сортировку по трем ключам в следующем порядке (по убыванию): музеи театры, магазины.

1. Создайте этот список с данными.

2. Выполните команду Данные / (сортировка и фильтр) Сортировка.

3. В выпадающем списке Сортировать по выбрать Музеи, установить Сортировка - Значение и Порядок - По убыванию. Далее нажать кнопку Добавить уровень в выпадающих списках. Затем по выбрать, соответственно, Театры и Магазины. Установить переключатели в позиции По убыванию. Нажать кнопку OK.

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

Графическое представление данных списка

Откройте файл Сортировка данных. Представим в графическом виде данные по количеству магазинов в округах, создав документ следующего вида.

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

2. Выполните команду Вставка / (диаграммы) Круговая диаграмма.

3. Выберите тип диаграммы - круговая объемная.

4. Поместите диаграмму в нужное место (желательно, чтобы она не закрывала таблицу).

5. Выполните команду Макет / (подписи) Легенда / Нет.

6. Выполните команду Макет / (подписи) Подписи данных / Дополнительные параметры подписей данных.

7. Уберите флажок со строки Значения и установите его в строке И мена категорий. Нажмите кнопку Закрыть.

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

9. Сохранитеполученный документ на диске С: в папке Учебные документы.

Использование фильтров

Общие сведения

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

Чтобы установить фильтр, нужно нажать на любую ячейку списка с данными и выполнить команду Данные / (Сортировка и Фильтр) Фильтр. После этого вид списка изменится. У каждого столбца появится дополнительная кнопка, нажатие которой приводит к открытию перечня условий фильтрования соответствующего поля. Эти условия представляют собой список названий полей в соответствующем столбце, а также ряд общих условий: Все, первые 10, и т.д. Применив одно из условий мы изменим вид списка.

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

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

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

Чтобы завершить работу с автофильтром необходимо еще раз выполнить команду Данные/(Сортировка и Фильтр)Фильтр.

Фильтрация

Создадим таблицу следующего вида для изучения приемов фильтрации.

1. Заполните первую строку таблицы полужирным шрифтом Arial 12 пунктов.

2. Выделите таблицу и выполните команду Главная/(Ячейки)Формат/ Автоподбор ширины столбца.

3. Выделите таблицу и установите все границы. Для этого выполните команду: Главная/(Шрифт)/Границы/Все границы.

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

1. Установите курсор на ячейку списка, содержащую данные и выполните команду Данные/(Сортировка и фильтр)Фильтр.

2. Нажмите на значок в ячейке с именем поля Фамилия и выберите Текстовый фильтр/Настраиваемый фильтр. Выберите в левом верхнем поле диалога логическую функцию больше, и установите значение функции в правом верхнем поле: В, после чего нажмите ОК.

3. Нажмите на значок в ячейке с именем поля Отчество и выберите Текстовый фильтр/Настраиваемый фильтр. Выберите в левом верхнем поле диалога логическую функцию не начинается с, и установите значение функции в правом верхнем поле: Р; установите переключатель на И, в левом нижнем поле выберите: не содержит, и установите значение функции: Е, после чего нажмите ОК.

4. Нажмите на значок в ячейке с именем поля Адрес и выберите Текстовый фильтр/Настраиваемый фильтр. Выберите в левом верхнем поле диалога логическую функцию не содержит, и установите значение функции в правом верхнем поле: 30, после чего нажмите ОК. Получаем список следующего вида.

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

1. В каждую ячейку введем данные.

2. В ячейку С10 введем формулу =сумм (С2:С9). Введем формулы в ячейки D10:H10. Для этого выделим ячейку С10 и, нажав левой кнопкой мыши на ее правый нижний угол, перемещаем мышь до H10.

3. Левой кнопкой мыши выделим все ячейки, с помощью правой кнопки мыши выберем Формат ячеек.

4. Во вкладке Граница выбираем внешние и внутренние.

5. Во вкладке Заливка выберем нужный цвет.

6. Во вкладке Шрифт выберем начертание – полужирный, размер 10.

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

8. Щелкнем по стрелочке в столбце Город. Появится список всех введенных нами городов. Выберем строку “Москва”, будут показаны только результаты работы московских агентов.

Создадим диаграмму для этой таблицы.

1. Выделим ячейки A1:H10.

2. Выберем команду Вставка/(Диаграмма)Гистограмма

3. Во всплывшем окошке выбираем Гистограмма с группировкой.

 

 

4. Во вкладке Макет/Подписи выберем Название диаграммы. Во всплывающем окошке выберем над диаграммой. Введем название: «количество проданных деталей».

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

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

7. Например, выберем только Московских клиентов, получим диаграмму следующего вида:

Вернув таблице исходный вид (чтобы все строки были видны), добавим примечания в 10 строку, в ячейках C10:H10.

1. Левой кнопкой мыши выделим ячейку С10,.

2. с помощью правой кнопки мыши выберем Вставить примечание и введем примечание.

3. Аналогично вставим примечания в остальные ячейки десятой строки.

Задания для самостоятельной работы.

1. Создайте документ следующего вида. Сделайте сортировку по Общему баллу.

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

Используя Фильтр оставьте только иногородних студентов.

3. Создайте список с формулами и диаграммой следующего вида.

Четвертый урок