Упражнение 1. Создать таблицу «Расчет удельного веса документально проверенных организаций» и построить круговую диаграмму по результатам расчетов

Ключ к заданию:

Исходные данные:

Формула для расчета:

Удельный вес = Число проверенных организаций / Общее число плательщиков

В столбце «Удельный вес» установите процентный формат отображения чисел.

Для интервала ячеек Е8:Е12 постройте круговую диаграмму.

Проведите форматирование диаграммы:

· измените размеры;

· выполните заливку фона (командная вкладка Формат, Заливка фигуры)

· отформатируйте легенду диаграммы (командная вкладка Макет, Легенда, Дополнительные параметры легенды)

· проведите форматирование подписей данных (командная вкладка Макет, Дополнительные параметры подписей данных)

· измените вид диаграммы на гистограмму (предварительно диаграмму скопируйте и вставьте на свободное место)

Дополнительное задание

Создать таблицу «Расчет заработной платы». Построить гистограмму и круговую диаграмму по результатам расчетов.

Исходная таблица:

Для расчета использовать следующие формулы:

Премия = Оклад * 0,2

Итого начислено = Оклад + Премия

Подоходный налог = Итого начислено * 0, 13

Итого к выдаче = Итого начислено – Подоходный налог

Пример построенной гистограммы:

Пример построенной круговой диаграммы:

Лабораторная работа № 5

Обработка данных в электронных таблицах MS Excel

Цель работы: изучение информационной технологии обработки данных в MS Excel.

Теоретические сведения:

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

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

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

3. В заголовках столбцов списка данных не следует объединять ячейки.

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

5. Не рекомендуется создавать искусственно строку итогов

К обработке данных в электронных таблицах MS Excel относится: сортировка, фильтрация, создание итоговых строк, использование функций для работы с базами данных. (вкладка Данные)

Сортировка данных.

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

Сортировка в особом порядке.MS Excel позволяет сортировать данные в особом порядке. Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать:

1. Выбрать командную вкладку Данные, Сортировка.

2. В поле Порядок выбрать Настраиваемый список.

3. В поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter.

4. После ввода всех элементов нажать кнопку Добавить и закрыть окно Списки.

Фильтрация данных.

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

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

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

Диалоговое окно «Расширенный фильтр» выглядит следующим образом:

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

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

Задание условий с использованием логической операции ИЛИ.

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

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

название	количество
парта
	>9

результирующая таблица:

название	количество
парта
парта
парта
стол

Задание условий с использованием логической операции И.

Для этого условия в диапазоне критериев нужно расположить на одной строке:

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

название	количество
парта	>2

результирующая таблица:

название	количество
парта
парта

Задание условий с одновременным использованием логических операций И, ИЛИ.

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

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

название	количество
парта	>2
стол	<10

результирующая таблица:

название	количество
парта
стол
парта
стол

Создание итоговых строк.

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

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

· Операция:из раскрывающегося списка нужно выбрать функцию, используемую при подведении итогов. Наиболее часто используемая функция – это сумма. Также можно выбрать Количество, Среднее, Максимум, Минимум, Произведение и т.д.

· Добавить итоги по:нужно указать по какому (каким) полю (полям) подводить итоги.

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

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

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

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