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

Данные о сотрудниках

Данные об учете рабочего времени

Графы таблицы для заполнения ведомости зарплаты

Описание алгоритма решения задач.

I. Построить таблицы «Данные о сотрудниках», «Данные об учете свободного времени» и «Ведомости зарплаты» по приведенным данным.

· запустить табличный процессор MS Excel:

· нажать кнопку Пуск

· выбрать в Главном меню команды Все программы / MS Office / MS Office Excel 2003.

В открывающейся книге (Книга 1) Лист 1 переименовать в лист с наименованием Данные о сотрудниках:

· внизу окна Книги 1 щелкнуть два раза левой кнопкой мыши ярлык Лист 1

· на выделенном ярлыке набрать Данные о сотрудниках

· нажать клавишу Enter

На рабочем листе Данные о сотрудниках MS Excel создать таблицу:

· установить курсор мыши на ячейку А1 и щелчком выделить ячейку

· набрать заголовки таблицы в одну строку (каждый заголовок вводится в свою самостоятельную ячейку)

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

· в строке меню выбрать команду Формат / Ячейки

· в диалоговом окне Формат ячеек выбрать Выравнивание

· установить выравнивание по горизонтали и вертикали и установить перенос по словам

· для сохранения изменений нажать кнопку ОК (рис.1).

Рис.1 Выбор формата ячеек

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

Заполнить таблицу - Данные о сотрудниках исходными данными (рис.2):

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

· ввести текст или цифровые данные

· подтвердить набор нажатием клавиши Enter

· выделив столбец А, выбираем Формат - Ячейки - Число - Дополнительный - Таб. Номер

· выделив столбцы D и E, выбираем Формат - Ячейки - Число - Денежный

· сделать границы таблицы, нажав значок Границы на панели управления, предварительно выделив всю таблицу

Рис.2 Таблица «Данные о сотрудниках»

В открывающейся книге (Книга 1) Лист 2 переименовать в лист с наименованием Данные об учете рабочего времени:

· внизу окна Книги 1 щелкнуть два раза левой кнопкой мыши ярлык Лист 2

· на выделенном ярлыке набрать Данные об учете рабочего времени

· нажать клавишу Enter

На рабочем листе Данные об учете рабочего времени MS Excel создать таблицу:

· установить курсор мыши на ячейку А1 и щелчком выделить ячейку

· набрать заголовки таблицы в одну строку (каждый заголовок вводится в свою самостоятельную ячейку)

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

· в строке меню выбрать команду Формат / Ячейки

· в диалоговом окне Формат ячеек выбрать Выравнивание

· установить выравнивание по горизонтали и вертикали и установить перенос по словам

· для сохранения изменений нажать кнопку ОК.

Заполнить таблицу Данные об учете рабочего времени (рис.3):

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

· ввести текст или цифровые данные

· подтвердить набор нажатием клавиши Enter

· выделив столбец А, выбираем Формат - Ячейки - Число - Дополнительный - Таб. Номер

· сделать границы таблицы, нажав значок Границы на панели управления, предварительно выделив всю таблицу

Рис.3 Таблица «Данные об учете рабочего времени»

В открывающейся книге (Книга 1) Лист 3 переименовать в лист с наименованием Ведомости зарплаты:

· внизу окна Книги 1 щелкнуть два раза левой кнопкой мыши ярлык Лист 3

· на выделенном ярлыке набрать Ведомости зарплаты

· нажать клавишу Enter

На рабочем листе Ведомости зарплаты MS Excel создать таблицу:

· установить курсор мыши на ячейку А1 и щелчком выделить ячейку

· набрать заголовки таблицы в одну строку (каждый заголовок вводится в свою самостоятельную ячейку)

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

· в строке меню выбрать команду Формат / Ячейки

· в диалоговом окне Формат ячеек выбрать Выравнивание

· установить выравнивание по горизонтали и вертикали и установить перенос по словам

· для сохранения изменений нажать кнопку ОК.

Заполнить таблицу Данные о сотрудниках исходными данными (рис.4):

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

· ввести текст или цифровые данные

· подтвердить набор нажатием клавиши Enter

· сделать границы таблицы, нажав значок Границы на панели управления, предварительно выделив всю таблицу

Рис.4 Таблица «Ведомости зарплаты»

3.2 Формирование ведомости зарплаты

В столбец А вводим табельные номера и, выделив весь столбец выберем Формат - Ячейки - Число - Дополнительный - Таб. Номер

С помощью функции ПРОМОТР() заполнить столбец «Фамилия» помещаем курсор мыши в ячейку В2 в ячейке выбираем Вставка - Функция - выбираем функцию ПРОСМОТР для подтверждения нажать клавишу ОК (это был шаг 1) в появившемся окне Мастер функций - шаг 1а из 2 выбираем отметку искомое значение; просматриваемый вектор; вектор результатов для подтверждения нажать клавишу ОК в появившемся окно Аргументы функции заполняем соответственно. Для подтверждения нажимаем ОК размножить введенную в ячейку В2 формулу для остальных ячеек (с В3 по В8) данной графы

С помощью функции ПРОМОТР() заполнить столбец «Отдел», столбец «Отдел» заполнить аналогично столбцу «Фамилия» диалоговое окно Аргументы функции заполняем соответственно. Для получения результата в столбце «Сумма по окладу», используя функцию ПРОСМОТР(), по табельному номеру найти соответствующий оклад, разделить его на количество рабочих дней и умножить на количество отработанных дней. «Сумма по надбавке» считается аналогично. Данные берутся из графы «Надбавка» (для столбцов В, С, D, E, F, H, I выбрать Формат - Ячейки - Число - Денежный), помещаем курсор мыши в ячейку D2 и вводим =ПРОСМОТР('Данные о сотрудниках'! A2: A8; 'Данные о сотрудниках'! A2: D8; 'Данные о сотрудниках'! D2: D8) /ПРОСМОТР('Данные о сотрудниках'! A2: A8; 'Данные об учете рабочего времен'! A11: B17; 'Данные об учете рабочего времен'! B11: B17) *ПРОСМОТР('Данные о сотрудниках'! A2: A8; 'Данные об учете рабочего времен'! A11: C17; 'Данные об учете рабочего времен'! C11: C17), размножить введенную в ячейку D2 формулу для остальных ячеек (с D3 по D8) данной графы

в ячейку Е2 вводим =ПРОСМОТР('Данные о сотрудниках'! A2: A8; 'Данные о сотрудниках'! A2: E8; 'Данные о сотрудниках'! E2: E8) /ПРОСМОТР('Данные о сотрудниках'! A2: A8; 'Данные об учете рабочего времен'! A11: B17; 'Данные об учете рабочего времен'! B11: B17) *ПРОСМОТР('Данные о сотрудниках'! A2: A8; 'Данные об учете рабочего времен'! A11: C17; 'Данные об учете рабочего времен'! C11: C17), размножить введенную в ячейку E2 формулу для остальных ячеек (с E3 по E8) данной графы помещаем курсор мыши в ячейку F2 в каждой ячейки вводим =D2+E2 размножить введенную в ячейку F2 формулу для остальных ячеек (с F3 по F8) данной графы.

Заполнить столбец «Сумма зарплаты» рис. 5

Для столбца G выбираем формат Процентный и вводим число 13.

Заполнить столбец «Сумма НДФЛ» рис.6

помещаем курсор мыши в ячейку H2, в каждой ячейки вводим =F2*G2

размножить введенную в ячейку H2 формулу для остальных ячеек (с H3 по H8) данной графы

Заполнить столбец «Сумма к выдаче», помещаем курсор мыши в ячейку I2

в каждой ячейки вводим =F2-H2, размножить введенную в ячейку H2 формулу для остальных ячеек (с I3 по I8) данной графы. Таблица примет вид, показанный на рис.7

Таблица «Ведомости зарплаты»

3.3 Данные результатной таблицы

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

Данные результатной таблицы отсортировать по номеру отдела вставить новый Лист, для этого щелкнуть правой кнопкой между листами и выбрать добавить – Лист, выделить таблицу «Ведомость зарплаты» копировать таблицу на новый Лист 1 (только значения), выделить таблицу на Листе 1, выбрать в Главном меню Данные / Сортировка в появившемся окне в поле Сортировать по указать Отдел и Идентифицировать поля по подписям нажать кнопку ОК (рис.8)

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

Таблица примет вид, показанный на рис.8

Рассчитать итоговые суммы по отделам выбрать в Главном меню Данные / Сводная таблица, в окне Мастер сводных таблиц и диаграмм шаг 1 выбрать Сводная таблица нажать кнопку Далее в появившемся окне Мастер сводных таблиц и диаграмм шаг 2 выбрать диапазон А1: I8 и нажать кнопку Далее, в окне Мастер сводных таблиц и диаграмм шаг 3 выбрать отметку Поместить в новом листе, нажать кнопку Макет. В окне Мастер сводных таблиц и диаграмм - Макет перетащить поля соответственно. Построить круговую диаграмму, на Листе 1 выделить ячейки В2: В8, затем, удерживая Ctrl, выделить ячейки I2: I8, выбрать в Главном меню Вставка - Диаграмма и выбрать Круговую и нажать ОК. В появившемся окне Мастер диаграмм (шаг 2 из 4): источник данных диаграмм выбрать диапазон, и ряды в столбцах.

Для подтверждения нажать Далее. Мастер диаграмм - шаг 2 в появившемся окне Мастер диаграмм (шаг 3 из 4): источник данных диаграмм нажать Далее, в появившемся окне Мастер диаграмм (шаг 4 из 4): источник данных диаграмм. Поместить диаграмму на листе и выбрать Отдельном. В итоге диаграмма имеет вид, показанный на рис.9

Круговая диаграмма

4. Список использованной литературы

1. Гордеев А.В. Операционные системы: Учебник для вузов.2-е изд. - СПб.: Питер, 2007.

2. Основы информатики: Учеб. пособие / А.Н. Морозевич, Н.Н. Говядинова, В.Г. Левашенко и др.; Под ред.А.Н. Морозевича. - 2-е изд., испр. - Мн.: Новое знание, 2008.

3. Евсюков В.В. Экономическая информатика: Учеб. пособие – Тула Издательство «Граф и К», 2009.

4. Информатика в экономике: Учеб. пособие /Под ред. проф. Б.Е. Одинцова, проф.А.Н. Романова. - М.: Вузовский учебник, 2008.

Размещено на Allbest.ru