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

Цель работы:

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

Задание 4.1.

Перемещение по рабочему листу. Выделение интервалов ячеек.

Запустите MS Excel. Ознакомьтесь с видом меню, панелей инструментов и видимой части рабочего листа. Попробуйте перемещаться по рабочему листу с помощью клавиатуры и мыши (используйте бегунок и стрелки в полосах прокрутки). Переместитесь в ячейки с адресом С8 и H15 разными способами. Вернитесь в ячейку А1.

Выделите с помощью мыши столбец D (щелкните мышью по имени столбца). Аналогично выделите строку 5. Выделите столбцы C, D и E, удерживая нажатой левую кнопку мыши, а затем строки с 4 по 10 включительно.

Выделите интервал ячеек C4:F10. Добавьте к выделенным ячейкам интервал B12:D15 (для этого нажмите клавишу Ctrl и держите ее нажатой).

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

Выделите те же области рабочего листа с помощью клавиатуры, учитывая следующее:

Ctrl + <пробел> – выделение столбца;

Shift + <пробел> – выделение строки;

F8 – расширение выделения включено/выключено;

Shift + F8 – добавление выделения включено/выключено;

Ctrl + Shift + <пробел> – выделение всего рабочего листа.

Задание 4.2.

Создание и форматирование рабочей книги представленной на рисунке 4.1.

Рекомендации по выполнению:

1. Создайте книгу и сохраните ее в своей папке под именем Лабораторная работа 4.

На листе 1 сформируйте таблицу (рис. 4.1).

Рис. 4.1. Таблица для форматирования

При вводе значений столбца № п/п используйте функцию автозаполнения:

• введите в ячейку А3 значение 1, в ячейку А4 значение 2;

• выделите ячейки А3:А4;

• установите указатель мыши в правый нижний угол выделенного диапозона (появится черный крестик), нажав левую клавишу мыши и не отпуская ее, протяните вниз до значения 10 (рис. 4.2).

Рис. 4.2. Использование функции автозаполнения

2. Изменение ширины столбцов и высоты строк.

Для того чтобы содержимое ячейки полностью помещалось в ней можно подобрать соответствующую ширину столбца. Для этого выделите его и выполните команду Главная (Ячейки) → Формат → Автоподбор ширины столбца. Попробуйте переместить границу столбцов в области заголовков столбцов и проанализируйте изменения. Что будет если дважды щелкнуть на границе маркеров столбцов?

Установите ширину первой строки равную 25, для этого выполните команду Главная (Ячейки) → Формат → Высота строки и установите нужную высоту.

3. Добавление строк.

Добавьте в начало таблицы строку для заголовка. Щелкните по строке, перед которой будет добавлена новая строка. Выполните команду Главная (Ячейки) → Вставить → Вставить строки на лист.

4. Объединение ячеек.

Выполните объединение ячеек A1:F1 Для этого выделите этот интервал и нажмите кнопку Главная (Выравнивание ) → Объединить и поместить в центре ( ).

Попробуйте убрать объединение ячеек и объединить их снова.

Введите текст заголовка таблицы в объединенную ячейку: Ведомость начислений сотрудникам организации.

Увеличьте ширину строки заголовка с помощью маркера строки.

5. Форматирование ячеек.

Отцентрируйте заголовок по вертикали:

• выделите заголовок;

• выполните команду Главная (Выравнивание ) → вкладка Выравнивание: по горизонтали – по центру; по вертикали – по центру.

Установите высоту строки заголовка таблицы – 30.

Установите числовой формат с количество десятичных знаков, равным 2, для интервала ячеек C17:F17 (команда Главная (Число ) → вкладка Число).

Установите выравнивание по центру (по горизонтали, по вертикали) для ячеек A2:F3.

Объедините ячейки A2:A3, B2:B3, C2:E2, F2:F3, A14:B14, A15:B15, A16:B16, A17:B17.

В ячейке F2 выполните перенос текста по словам: Главная (Выравнивание) → Перенос текста ( ).

Для изменения параметра шрифта воспользуйтесь командой Главная (Шрифт) → вкладка Шрифт или кнопками на панели инструментов страницы Главная (Шрифт).

Задайте обрамление для заголовка двойной линией, а для остальной таблицы – одинарной, цвет – произвольный (используйте команду Главная (Шрифт ) → вкладка Шрифт, укажите нужные границы обрамления).

Выделите цветом ячейки A2:F3, C4:C13, D4:D13, E4:E13 (используйте команду Главная (Шрифт ) → вкладка Заливка).

6. Задание параметров страницы.

На странице Разметка страницы выполните следующие действия:

• активизируйте переключатель Альбомная для изменения ориентации страницы (область Параметры страницы → Ориентация);

• сформируйте верхний колонтитул (область Параметры страницы → Печатать заголовки → вкладка Колонтитулы → кнопка Создать верхний колонтитул…):

  • в области В центре (рис. 4.3) – введите свою фамилию;

  • в области Справа – вставить дату ( ).

Рис. 4.3. Формирование верхнего колонтитула

• сформируйте нижний колонтитул (область Параметры страницы → Печатать заголовки → вкладка Колонтитулы → кнопка Создать нижний колонтитул…):

  • в области Слева (рис. 4.4) – вставить имя файла( ).

  • в области Справа – вставить номер страницы ( ).

Рис. 4.4. Формирование нижнего колонтитула

• настройте поля книги (область Параметры страницы → Поля → Настраиваемые поля...):

  • верхнее, нижнее – 2,5;

  • левое, правое – 2.

Осуществите предварительный просмотр документа: Файл → Печать.

На рис. 4.5 представлена отформатированная таблица.

Рис. 4.5. Таблица Ведомость начислений

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

Задание 4.3.

Создайте на листе 2 таблицу (рис. 4.6), выполнив необходимое форматирование и переименовав лист с таблицей в Удержания.

Задайте для листа Удержания те же параметры страницы (колонтитулы и поля), что и для листа Начисления.

Рис. 4.6. Таблица Ведомость удержаний

Задание 4.4.

Создайте на листе 3 таблицу (рис. 4.7), выполнив необходимое форматирование и переименовав лист с таблицей в Ведомость.

Рис. 4.7. Таблица Итоговая ведомость

Задайте для листа Ведомость те же параметры страницы (колонтитулы и поля), что и для листа Начисления.

9. Сохранить книгу Лабораторная работа 4.

Задание 4.5.

Вычисление стандартных функций.

Рассчитать заработную плату сотрудникам предприятия: оклад, премия, отпускные, всего начислено. Кроме того, определить максимальное, минимальное и среднее значение по столбцам. Заработная плата сотрудников условная.

Рекомендации по выполнению:

1. Откройте файл Лабораторная работа 4 и сохраните его под именем Лабораторная работа 5.

2. Произведем расчет итоговых начислений сотрудников предприятия:

• выделить ячейку F4 (значение «Всего начислено» для Иванова);

• обратиться к Мастеру функций, для этого выполнить команду Формулы (Библиотека функций) → Вставить функцию или нажать кнопку Вставить функцию в строке формул ( );

• в окне Мастер функций - шаг 1 из 2 в поле Категория выбрать Математические, в поле Выберите функцию – СУММА и нажать ОК (рис. 4.8);

Рис. 4.8. Окно выбора категории и функции

• в окне Аргументы функции в поле Число 1 выделить диапазон C4:E4 и нажать ОК;

• скопировать содержимое ячейки F4 в диапазон F5:F13.

3. Рассчитать значения Итого (функция СУММ), Максимальная (функция МАКС), Минимальная (функция МИН), Средняя (функция СРЗНАЧ) для столбца Оклад, выбирая в качестве аргумента функций диапазон С4:С13. После введенных формул таблица примет вид рис. 4.9 в режиме отображения формул (Формулы (Зависимости формул) → Показать формулы ( )).

Рис. 4.9. Таблица в режиме отображения формул

Вернуться в режим отображения значений (отменить режим Показать формулы).

4. Аналогично рассчитать значения Итого, Минимальная, Минимальная, Средняя для столбцов Премия, Отпускные, Всего начислено (можно воспользоваться копированием формул из диапазона С14:С17 в диапазон D14:F17).

Задание 4.6. Вычисление стандартных функций.

Рассчитать удержания из заработной платы сотрудников предприятия: аванс – 40%, пенсионный налог – 1 %, профсоюзные взносы – 1 % от начисленной суммы; итого удержано – сумма всех удержаний по сотруднику.

Рекомендации по выполнению:

1. На листе Удержания выделить ячейку С4 (значение аванса для Иванова), ввести знак =, перейти на лист Начисления и выделить ячейку F4 (Всего начислено для Иванова). Не возвращаясь на лист Удержания в строке формул после адреса ячейки =Начисления!F4 ввести *0,4 и нажать Enter.

Произойдет автоматический возврат на лист Удержания и в ячейке С4 появится значение. Формула расчета аванса будет иметь вид =Начисления!F4*0,4

Скопировать значение ячейки С4 в диапазон С5:С13.

2. На листе Удержания выделить ячейку D4 (значение пенсионного налога для Иванова), ввести знак =, перейти на лист Начисления и выделить ячейку F4 (Всего начислено для Иванова). Не возвращаясь на лист Удержания в строке формул после адреса ячейки =Начисления!F4 ввести *0,1 и нажать Enter.

Произойдет автоматический возврат на лист Удержания и в ячейке D4 появится значение. Формула расчета пенсионного налога будет иметь вид =Начисления!F4*0,1

Скопировать значение ячейки D4 в диапазон D5:D13.

3. На листе Удержания выделить ячейку E4 (значение профсоюзных взносов для Иванова), ввести знак =, перейти на лист Начисления и выделить ячейку F4 (Всего начислено для Иванова). Не возвращаясь на лист Удержания в строке формул после адреса ячейки =Начисления!F4 ввести *0,1 и нажать Enter.

Произойдет автоматический возврат на лист Удержания и в ячейке Е4 появится значение. Формула расчета пенсионного налога будет иметь вид =Начисления!F4*0,1

Скопировать значение ячейки Е4 в диапазон Е5:Е13.

4. Рассчитать значение Итого удержано, как сумму всех удержаний (аванс, пенсионный фонд, профсоюз), для каждого сотрудника используя функцию СУММ:

• выделить ячейку F4 используя Мастер функция ввести формулу =СУММ(C4:E4);

• скопируйте формулу ячейки F4 в диапазон ячеек F5:F13.

5. Рассчитать значения Итого (функция СУММ), для столбца Аванс, выбирая в качестве аргумента функций диапазон С4:С13. Формула для расчёта будет иметь вид: =СУММ(C3:C12).

Скопировать формулу в диапазон ячеек D14:F14.

В режиме формул результат будет выглядеть следующим образом (рис. 4.10).

Рис. 4.10. Расчет удержаний сотрудников предприятия

Задание 4.7.

Вычисление стандартных функций.

Рассчитать зарплату сотрудникам предприятия. Сформировать итоговую ведомость выплат.

Рекомендации по выполнению:

1. На листе Ведомость выделить ячейку С3 (значение начислений для Иванова), ввести знак =, перейти на лист Начисления и выделить ячейку F4 (Всего начислено для Иванова) и нажать Enter.

Произойдет автоматический возврат на лист Ведомость. Формула в ячейке С3 будет иметь вид =Начисления!F4

Скопировать значение ячейки С3 в диапазон С4:С12.

2. На листе Ведомость выделить ячейку D3 (значение удержаний для Иванова), ввести знак =, перейти на лист Удержания и выделить ячейку F4 (Итого удержано для Иванова) и нажать Enter.

Произойдет автоматический возврат на лист Ведомость. Формула в ячейке D3 будет иметь вид =Удержания!F4

Скопировать значение ячейки D3 в диапазон D4:D12.

3. Значение Итого к выдаче (тыс. руб.) рассчитать как разность полей Всего начислено (тыс. руб.) и Всего удержано (тыс. руб.):

• выделить ячейку Е3 и ввести в нее формулу =C3-D3;

• скопировать значение ячейки Е3 в диапазон Е4:Е12.

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

В режиме формул результат будет выглядеть следующим образом (рис. 4.11).

Рис. 4.12. Расчет итоговых выплат сотрудникам предприятия

Сохранить файл с именем Лабораторная работа 4-Ведомость.