Порядок работы

Порядок работы

1. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги.Назовите скопированный лист «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.

2. Измените значение Премии на 46 %, Доплаты — на 8 %. Программа произведет пересчет формул (рис. 8).

Рис. 8. Ведомость зарплаты за декабрь

3. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы в соответствии с рис. 9.

Рис. 9. Гистограмма зарплаты за декабрь

4. Проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь-декабрь.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Назовите скопированный лист «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».

6. Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 10. Для этого удалите в основной таблице (рис. 8) столбцы «Оклад» и «Премия», а также строку 4 с численными значениями % Премии и % Удержания и строку «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.

7. Вставьте новый столбец «Подразделение» (Вставка/Столбец) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу (рис. 10).

Рис. 10. Таблица для расчета итоговой квартальной заработной платы

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

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

В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид

= 'Зарплата декабрь'!F5 + 'Зарплата ноябрь'!F5 + 'Зарплата октябрь'!E5.

Аналогично произведите квартальный расчет «Удержания» и «К выдаче».

Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание информации соответствующих ячеек листов электронной книги.

9. В силу однородности расчетных таблиц зарплаты по месяцам, для расчета квартальных значений столбцов «Удержание» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейки Е5 и F5 (рис. 11).

10. Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D, Е и F. Таблица примет вид, как на рис. 11.

Рис. 11. Расчет квартального начисления заработной платы связыванием листов

Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям. Таблица примет вид, как на рис. 12.

Рис. 12. Вид таблицы начисления квартальной заработной платы после сортировки по подразделениям

11. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги (рис. 13). Задайте параметры подсчета промежуточных итогов: при каждом изменении в — Подразделение; операция — Сумма; добавить итоги по: Всего начислено, Удержания, К выдаче. Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

Рис. 13. Окно задания параметров расчета промежуточных итогов

Примерный вид итоговой таблицы представлен на рис. 14.

Рис. 14. Итоговый вид таблицы расчета квартальных итогов по зарплате

12. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

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

13. Исследуйте графическое отображение зависимостей ячеек друг от друга.

Для этого скопируйте содержимое листа «Зарплата октябрь» на новый лист. Копии присвойте имя «Зависимости». Откройте панель «Зависимости» (Сервис/Зависимости формул/Панель зависимостей) (рис. 15.) Изучите назначение инструментов панели, задерживая на них указатель мыши.

Рис. 15. Панель зависимостей

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

14. Сохраните файл «Зарплата» с произведенными изменениями.

Задание 8.

Каждый документ представляет собой рабочую книгу – набор листов-таблиц, разграфленных на строки и столбцы. Файлы имеют расширение xls. Сохранение, открытие, создание рабочей книги выполняется командами меню Файл.

Сохраните книгу в своей папке под именем ОТЧЕТ* (вместо * ввести свою фамилию). Закройте книгу. Найдите и откройте свою книгу.

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

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

Выключите панель Форматирование. Включите ее.

Под Панелями слева – поле Имени, справа – Строка формул, в которой отображается содержимое активной ячейки. При вводе данных в ней появляются две кнопки; крестик отменяет ввод (эквивалентно нажатию Esc), зеленая галочка подтверждает ввод (эквивалентно нажатию Enter). Перевод Строки формул в активное состояние выполняется в меню Вид.

Выключите Строку формул. Включите ее.

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

В самом низу окна расположена строка состояний.

Одна рабочая книга может состоять из 256 листов; по умолчанию они называются Лист 1, Лист 2 и т. д. В нижней части экрана левее горизонтальной полосы прокрутки находятся ярлычки рабочих листов. Щелчок по ярлычку листа активизирует этот лист. Щелчок правой кнопкой мыши по ярлычку вызывает контекстное меню для операций с листом (копирование, перемещение, удаление, добавление и т.д.). Чтобы переименовать лист, нужно вызвать на его ярлычке контекстное меню, выбрать команду Переименовать, ввести имя и нажать Enter.

Перейдите на Лист 2. Назовите его График функции.

Поставьте лист График функции первым в книге.

Командой Формат – Лист можно скрыть (отобразить) лист. Скройте лист График функции. Отобразите лист.

Лист содержит 256 столбцов и 65 536 строк. Над таблицей – буквенные обозначения столбцов, слева – номера строк. Пересечение строк и столбцов образует ячейки, которые служат для ввода данных. Адрес ячейки однозначно задается номером строки и столбца и отображается в поле имени. При указании адреса ячейки используются латинские буквы. Например, А1 – ячейка в левом верхнем углу листа. Можно перейти к другому стилю, когда и столбцы и строки указываются числами. В этом стиле R1С1 обозначает ячейку А1 (R (Row) – номер строки, С (Column) – номер столбца). Стиль меняют на вкладке Общие диалогового окна Сервис – Параметры.

Сравните, как задается адрес ячейки в разных стилях.

Задание 9. Выделение ячеек.

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

Установите курсор мыши на заголовок столбца С и щелкните по нему. Вы выделили столбец С. Если щелкнуть в другом месте рабочего листа, выделение снимется.

Установите курсор мыши на заголовок строки 2, нажмите левую кнопку мыши и перетащите указатель мыши на заголовок строки 4. Вы выделили строки 2, 3, 4. Снимите выделение.

Установите курсор мыши на пустой прямоугольник слева от заголовков столбцов выше заголовков строк. Щелкните левой кнопкой. Вы выделили весь лист.

Установите курсор мыши на ячейку С3. Нажмите левую кнопку мыши, перетащите ее указатель на ячейку Е8 и отпустите кнопку. Вы выделили область С3:Е8. Снимите выделение.

Установите курсор мыши на ячейку Е10. Удерживая клавишу Shift, нажимайте по очереди клавиши управления курсором. Происходит выделение диапазона ячеек в выбранном направлении.

Установите курсор мыши на ячейку А1. С помощью полос прокрутки выведите на экран ячейку Х40 и, удерживая клавишу, Shift щелкните по ней. Вы выделили область, охватывающую большой диапазон ячеек А1:Х40.

Выделите область В2:Е6. Удерживая клавишу Ctrl, выделите область С7:Н10. Вы выделили две несмежные области.

Задание 10. Диаграммы и графики.

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

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

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

Шаг 3. Параметры диаграммы. На вкладках этого окна выполняется оформление внешнего вида диаграммы.

Шаг 4. Размещение диаграммы. Следует выбрать вариант размещения диаграммы – внедренный в лист с таблицей данных или расположенный на отдельном листе.

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

Копирование, перемещение, удаление, изменение размеров диаграммы выполняется теми же приемами, что и любых других объектов MS Office.

На листе «Диаграмма» введите таблицу 5.3.

Выполните вычисления, используя функцию СУММ (категория Математические).

Постройте гистограмму, иллюстрирующую изменение спроса на мебель.

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

Введите таблицу, аналогичную таблице 5.5.

Выполните вычисления, используя функцию СРЗНАЧ (категория Статистические).

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

Задание 10. Контроль правильности вводимых значений.

При вводе данных не исключены ошибки. Эти ошибки можно отчасти предотвратить, если воспользоваться имеющимися в Excel средствами контроля. При их использовании предполагается, что вводимые значения должны удовлетворять некоторым наперед заданным условиям (все вводимые значения должны быть натуральными; принадлежать диапазону от 1 до 6; представлять время суток и т.п.).

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

Кроме того, можно контролировать данные с использованием логических функций, возвращающих значения ИСТИНА или ЛОЖЬ. Вводимое значение подставляется в формулу. Если функция возвращает значение ИСТИНА, то значение пропускается, иначе появляется сообщение об ошибке.

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

Выделить блок ячеек.
Выбрать команду Данные – Проверка.
В диалоговом окне Проверка вводимых значений щелкнуть по вкладке Параметры.
В списке Тип данных выбрать требуемый тип.
Для установки допустимых пределов необходимо использовать список Значение.
Щелкнуть по вкладке Сообщение для ввода и установить текст, который будет появляться при выборе ячейки. Этот текст должен указывать на то, какие данные можно вводить в ячейку.
Щелкнуть по вкладке Сообщение об ошибке и задать, какое сообщение будет появляться при вводе недопустимых значений.
Чтобы не допустить ввод неправильного значения, выбрать опцию Останов. Обратите внимание, что возможны и другие, более мягкие виды контроля (какие?).
Нажать кнопку ОК.

Замечание 1. При выборе типа данных Список вводится список допустимых значений. Список может быть введен непосредственно в поле Источник с использованием точки с запятой в качестве разделителя элементов списка. Если список длинный, то он создается на рабочем листе и на него устанавливается ссылка или указывается его имя.

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

Задание 11. Поиск в таблице.

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

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

Дана таблица, состоящая из двух или большего числа полей (столбцов);
Дано значение ключевого поля;
Требуется найти запись с соответствующим значением ключевого поля;
Требуется вернуть значение заданного поля в найденной записи.

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

Упорядочим поле поиска по возрастанию. Для установления соответствия необходимо найти два таких значения поля, что

значение 1≤ключ<значение 2.

В этом случае соответствующим будет значение «значение 1».

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

Для упорядоченных значений поля способ сравнения определяется аналогично.

Например, имеется телефонный справочник или, точнее, таблица с 4 полями:

№ телефона;

Фамилия;

Имя;

Отчество.

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

Для решения задачи поиска в таблице EXCEL предлагает несколько функций.

Функция ВПР()

Синтаксис функции:

ВПР(данное значение ключевого поля; таблица; номер поля, из которого возвращается результат; тип сравнения).

Функция имеет следующие особенности применения:

Поиск происходит в первом столбце таблицы.

Если «тип сравнения» = 0 (ЛОЖЬ), то сравнение подразумевает полное совпадение.

Если «тип сравнения» = 1 (ИСТИНА), то первый столбец должен быть отсортирован по возрастанию.

Если «тип сравнения» не указан, подразумевается, что он равен 1.

⇐ Предыдущая 1 23