Практическая работа 13. Основные приемы работы с электронной таблицей Microsoft Excel. Абсолютная и относительная адресация при работе с формулами

ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ С ЭЛЕКТРОННОЙ ТАБЛИЦЕЙ MICROSOFT EXCEL.

Структура окна Excel

Окно Excel содержит все стандартные элементы, присутствующие в большинстве окон программ Windows:

- строка заголовка – содержит название программы (Excel) и, возможно, название открытого документа; также в окне заголовка содержатся кнопки сворачивания, разворачивания и закрытия окна Excel;

- строка меню – список названий меню команд Excel; щелчок на названии меню раскрывает соответствующие меню команд;

- строка состояния – содержит подсказки к выделенным командам, описания состояния Excel, указания и т.п.

- панели инструментов – строки, содержащие пиктограммы наиболее часто используемых команд Excel; так же как в Word, с помощью команды Вид/ Панели инструментов можно вывести на экран или убрать с экрана отдельные панели инструментов;

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

- вызвать команду Вид/Панели инструментов, пункт Настройка;

- щелкнуть на кнопке Создать;

- ввести название для новой панели;

- в окне Настройка перейти на вкладку Команды;

- для выбора нужной пиктограммы выбрать категорию, к которой относится пиктограмма;

- перетащить мышью пиктограмму из окна Настройка на новую панель инструментов;

- после заполнения панели всеми необходимыми пиктограммами закрыть окно Настройка.

Выводить на экран и убирать с экрана пользовательские панели инструментов следует так же, как и встроенные.

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

- в окне Настройка перейти на вкладку Панели инструментов;

- выбрать панель инструментов из списка;

- щелкнуть на кнопке Удалить.

Помимо упомянутых, имеется строка, присутствующая только в окне Excel – строка формул (см. ).

В строке формул отображается информация, вводимая или содержащаяся в ячейках таблиц Excel[1]. Левая часть строки формул – поле имени. В поле имени отображается адрес активной ячейки таблицы (см. далее).

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

Для переключения между рабочими листами используются ярлычки листов (Лист1, Лист2…). Для того чтобы начать работу с каким-либо листом, этот лист надо активизировать, т.е. щелкнуть мышью на его ярлычке.

Каждый рабочий лист содержит 256 столбцов и 65536 строк. Строки имеют заголовки в виде чисел от 1 до 65536. Столбцы именуются латинскими буквами: A, B, C…Z, AA, AB, AC…AZ, BA, BB… Последний столбец имеет заголовок IV. Заголовки строк и столбцов указаны в координатных строках в окне документа.

На пересечении строк и столбцов находятся ячейки. Каждая ячейка таблицы имеет адрес, состоящий из имени столбца и номера строки: A1, B20, GK527 и т.п.

В таблице всегда имеется одна активная (или текущая) ячейка. Активная ячейка отмечается табличным курсором. Для активизации какой-либо ячейки достаточно выполнить щелчок на данной ячейке. Можно также переместить на данную ячейку табличный курсор клавишами управления курсором.

Если требуется переключиться на ячейку, расположенную на большом расстоянии от текущей, удобнее всего это делать с помощью поля имени:

- щелкнуть в поле имени;

- удалить адрес текущей ячейки;

- ввести адрес ячейки, которую нужно активизировать;

- нажать < Enter>.

Ввод данных в ячейки

Перед тем, как ввести данные в какую-либо ячейку, эту ячейку следует активизировать.

В отличие от ввода текста в документ Word, информация в ячейку таблицы Excel вводится в 2 этапа:

I этап – набор информации на клавиатуре; при этом вводимая информация отображается и в самой ячейке, и в строке формул; на данном этапе информации в ячейке ещё нет;

II этап – ввод информации в ячейку (завершение ввода).

До тех пор, пока не выполнен II этап, Excel находится в режиме ввода. Большинство команд при этом недоступны.

Есть несколько способов завершения операции ввода:

- нажать < Enter>;

- щелкнуть на кнопке Ввод в строке формул (см. Ошибка! Источник ссылки не найден. );

- щелкнуть на любой другой ячейке таблицы;

- переместить табличный курсор клавишами управления курсором.

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

Ячейки в Excel могут содержать следующие типы данных:

- числа;

- текст (последовательность до 32000 любых символов);

- формулы.

Формулы вводятся в ячейки, когда необходимо выполнить какие-либо вычисления. Формулы всегда начинаются со знака равенства (=). Операция умножения обозначается знаком “*”, операция деления – знаком “/”. Например: =3*24, 17+4/(59-12, 43). Тип данных “дата” в Excel отсутствует. Однако, даты можно вводить в ячейки(например, в форме 21/03/00). Введенная дата преобразуется в число[2] по следующему принципу:

1 января 1900 года = 1

2 января 1900 года = 2

…

1 января 2000 года =36526

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

В Excel существуют несколько приёмов, которые могут ускорить процесс ввода информации в ячейки. К их числу относятся следующие:

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

2) Режим Фиксированный десятичный; данный режим работы очень удобен, если требуется вводить большое количество чисел с одинаковым числом десятичных разрядов (например, денежные величины). В этом режиме Excel автоматически вводит десятичный разделитель. Включение режима Фиксированный десятичный:

- выбрать команду Сервис/Параметры, вкладку Правка;

- установить флажок Фиксированный десятичный формат при вводе;

- в соседнем числовом поле указать количество десятичных разрядов.

3) Автозаполнение; используется для ввода одинаковых данных в несколько соседних ячеек. Выполнить эту операцию можно следующим образом:

- ввести информацию в какую-либо ячейку;

- установить курсор мыши на нижний правый угол ячейки так, чтобы появился черный крестик (маркер заполнения);

- при нажатой левой кнопке перемещать мышь вдоль строки (или столбца); при этом вокруг ячеек будет формироваться пунктирная рамка;

- когда рамка охватит все ячейки, в которые надо ввести одинаковую информацию, отпустить клавишу мыши.

Проверка вводимых данных

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

Для задания ограничений на вводимые данные следует:

- выделить ячейки, для которых создаются ограничения;

- вызвать команду Данные/Проверка;

- на вкладке Параметры в поле Тип данных выбрать данные, допустимые для выделенных ячеек (целые числа, даты и т.п.);

- в поле Значение выбрать операцию сравнения (больше, меньше, между и т.д.);

- в поля Минимум и Максимум ввести предельные допустимые значения.

Списки

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

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

Для просмотра существующих и создания новых списков нужно выполнить команду Сервис/Параметры и перейти на вкладку Списки.

Создание нового списка:

- щелкнуть в поле Элементы списка;

- ввести элементы списка по одному в строке; после ввода каждого элемента (кроме последнего) нажимать < Enter>;

- после ввода последнего элемента щелкнуть на Добавить.

- Для удаления списка следует на вкладке Списки выделить список и щелкнуть на Удалить.

- Ввод списка в ячейки таблицы осуществляется следующим образом:

- ввести в ячейку один элемент списка (любой! );

- применить к данной ячейке Автозаполнение.

Числовые ряды

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

Ввод числового ряда (арифметическая прогрессия) можно осуществить следующим образом:

- в две соседние ячейки ввести первые два элемента числового ряда;

- выделить обе ячейки;

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

Можно создавать ряды мерных чисел (с текстовой частью), например:

1 кг, 2 кг, 3 кг …

10 руб, 20 руб, 30 руб …

КОМТЭК 95; КОМТЭК 96; КОМТЭК 97…

Для создания таких рядов нужно соблюдать два условия:

- элемент ряда обязательно должен либо начинаться, либо заканчиваться числом;

- между числом и текстом вводить пробел, если элемент ряда начинается с числа.

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

- ввести в ячейку первый элемент числового ряда;

- выделить ячейки, в которые должен быть введен числовой ряд, включая ячейку с первым числом;

- вызвать команду Правка/Заполнить/Прогрессия;

- в окне Прогрессия можно выбрать тип прогрессии (Арифметическая или Геометрическая) и шаг прогрессии.

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

Ввод формул

Как уже упоминалось, при вводе формул первым символом должен быть знак равенства (=). Если нужно выполнить вычисления с данными, содержащимися в ячейках, то в формуле нужно указывать адреса этих ячеек.

Самый удобный способ ввести в формулу адрес ячейки – выполнить на данной ячейке щелчок. Можно также ввести адрес ячейки с клавиатуры.

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

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

Автосуммирование

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

Для того чтобы воспользоваться этой возможностью следует:

- активизировать ячейку, в которую нужно поместить формулу суммирования;

- щелкнуть на кнопке Автосумма на панели инструментов Стандартная (см. Ошибка! Источник ссылки не найден. ); вокруг ячеек, которые Excel собирается просуммировать, появляется «бегущая дорожка»;

- если «бегущая дорожка» охватывает не те ячейки, которые требуется просуммировать, выделить нужные ячейки;

- любым способом завершить ввод.

Редактирование данных

Вставка и удаление ячеек

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

Вставка ячеек:

1) выделить ячейки, вместо которых нужно вставить новые ячейки;

2) выполнить команду Вставка/Ячейки;

3) выбрать направление перемещения выделенных ячеек (вправо или вниз).

Удаление ячеек:

1) выделить ячейки, которые надо удалить;

2) выполнить команду Правка/Удалить;

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

Вставка и удаление ячеек, также как в случае со строками и столбцами, не изменяет размеров рабочего листа.

Поиск информации в таблице

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

В окне данной команды в поле Что надо ввести информацию, содержащуюся в искомой ячейке. В списке Область поиска выбрать тот слой ячеек, который должен просматривать Excel при поиске. Слой «Формулы» –информация, которая была введена в ячейку. Слой «Значения» – примерно та информация, которая отображается на экране.

Работа с листами

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

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

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

Переименование листа:

- сделать двойной щелчок на ярлычке листа;

- удалить имя;

- ввести новое имя;

- нажать < ENTER>.

Вставка нового листа:

- активизировать лист, перед которым надо сделать вставку;

- из меню ярлычков выбрать команду Добавить;

- на вкладке Общие выбрать вариант Лист;

- нажать ОК.

Удаление листа:

- активизировать лист, подлежащий удалению;

- из меню ярлычков выбрать команду Удалить;

- для подтверждения удаления нажать ОК.

Перемещение листа:

- активизировать лист;

- поместить на ярлычок листа курсор мыши;

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

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

Для выполнения многих действий (например, удаления листов) можно выделять несколько листов.

Чтобы выделить соседние листы:

- активизировать первый выделяемый лист;

- при нажатой клавише < Shift> щелкнуть на ярлычке последнего выделяемого листа.

Чтобы выделить несмежные листы, следует при нажатой клавише < CTRL> щелкать на ярлычках выделяемых листов.

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

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

Информацию с одного рабочего листа можно скопировать на несколько рабочих листов одновременно:

- выделить лист, содержащий информацию, и листы, на которые нужно вставить копию;

- выделить ячейки с копируемой информацией;

- выполнить команду Правка/Заполнить/По листам;

- выбрать копируемый слой (Форматы, Содержимое, Все).

КОНТРОЛЬНЫЕ ВОПРОСЫ

· Как создать панель инструментов?

· Как активизировать ячейку таблицы с помощью поля имени?

· Как выйти из режима ввода информации?

· Какие типы данных существуют в Excel?

· Как узнать, какая информация содержится в ячейке?

· Что такое Автозаполнение?

· Как задать ограничения для данных, вводимых в ячейки?

· Как создать пользовательский список? Как ввести список в таблицу?

· Как ввести в таблицу числовой ряд?

· Для чего предназначена функция Автосумма?

· Как отредактировать информацию в ячейке?

· Как вставить несколько копий информации одной командой?

· Как скопировать информацию из ячеек без использования буфера обмена?

· В каких случаях команды вставки и удаления столбцов могут не выполняться?

· Как в большой таблице найти определенную информацию? Как указать, в каком слое находится эта информация?

· Какие операции можно выполнять с листами?

· Как скопировать информацию на несколько листов одновременно?

ПРАКТИЧЕСКОЕ ЗАДАНИЕ

Общие приемы работы

1) Вывести на экран панели инструментов «Стандартная» и «Форматирование»; остальные - убрать с экрана.

2) Создать новую панель инструментов; присвоить ей имя «Пользовательская»; вставить на созданную панель 5-6 пиктограмм из различных категорий команд.

3) Убрать с экрана, а затем восстановить строку формул и строку состояния.

4) Используя поле имени выполнить перемещение в ячейки DY127, AI500; F341; ЕЕ65536; IV256; A1.

5) В ячейки Листа2 ввести любые числа и опробовать все описанные в пособии способы завершения операции ввода.

6) В ячейку D5 ввести текст: «Московский Государственный Социальный Университет».

7) Проверить содержимое ячеек D5 и Е5 по строке формул.

8) Ввести в ячейку Е5 текст «АиТ».

9) Проверить содержимое ячейки D5 по строке формул.

10) В ячейку D6 ввести число 954835621939. Сравнить информацию в ячейке D6 с отображением информации на экране.

11) В ячейку D7 ввести формулу: =2*2. Сравнить информацию в ячейке D7 с отображением информации на экране.

12) Ввести текст «Москва» в ячейку С10,
«Петербург» – С11,
«Минск» – С12.
В ячейку C13 ввести текст «Москва» путем выбора элемента из списка.

13) Включить режим «Фиксированный десятичный» с двумя знаками после запятой. Ввести числа 2149, 485, 51362, 2, 58 в ячейки столбца F. Отключить режим «Фиксированный десятичный».

14) Выполнить выделение фрагментов таблицы: строка, столбец, несколько строк, несколько столбцов, несколько ячеек, несмежные ячейки, вся таблица.

15) Установить ограничения на вводимые данные:
А15: D15 - « > 0»;
А16: С20 - « > 0; < 10»;
А21: D22 - «> 10; < 20».
Проверить работу режима проверки вводимых данных.

16) В любую пустую ячейку ввести произвольный текст. Применить к ячейке функцию Автозаполнение.

17) В любую пустую ячейку ввести произвольное число. Применить к ячейке функцию Автозаполнение.

18) Создать пользовательский список, включающий любую последовательность текстовых элементов.

19) Ввести в ячейки Листа2:
весь список, начиная с 1-го элемента;
часть списка, начиная с любого элемента, кроме 1-го;
заполнить большее число ячеек, чем имеется элементов
в списке.

20) В ячейки Листа2 ввести несколько вариантов числовых рядов: с арифметической и геометрической прогрессией, с разными шагами прогрессии.

21) Ввести несколько вариантов последовательностей дат, используя разные элементы последовательностей и разные значения шага.

22) Ввести несколько вариантов числовых рядов с текстовым элементом.

Ввод данных

1. На Лист1 ввести данные по образцу на Ошибка! Источник ссылки не найден.

A	B	C	D
Персональный бюджет на будущий год
Ежемесячные поступления
Основная зарплата
Зарплата по договорам
Проценты по вкладам
Итого
Расходы
Транспорт
Питание
Коммунальные платежи
Досуг
Одежда
Косметика
Итого
Баланс

2. Используя работу со списками, ввести в ячейки В2: Н2 названия месяцев с января по июль.

3. Установить условия на вводимые данные:
B3: H5 – «> 0»;
B11: H11 – “< 1000”;
B13: H13 – “< 500”.

4. В ячейку B8 ввести число 320. Используя Автозаполнение, скопировать число из ячейки B8 в ячейки C8: H8.

5. Заполнить ячейки B3: H5; B9: H13.

6. Используя операцию сложения (+), ввести формулы в ячейки B6 и C6. Используя Автозаполнение, скопировать формулу из C6 в D6: H6. Просмотреть содержимое ячеек D6: H6.

7. Используя функцию Автосумма, ввести формулы в ячейки B14: C14. Используя Автозаполнение, скопировать формулу из C14 в D14: H14. Просмотреть содержимое формул D14: H14.

8. Ввести формулу в ячейку B15. Используя Автозаполнение, скопировать формулу из B15 в C15: H15.

9. В ячейку I2 ввести текст «Итого». Используя функции Автосумма и Автозаполнение, ввести формулы в ячейки I3: I15.

10. Сохранить рабочую книгу в личной папке под именем «Персональный Бюджет».

Редактирование данных

1. На Листе2 отредактировать информацию в нескольких ячейках.

2. На Листе1 изменить величины расходов или доходов в нескольких месяцах. Проверить, как влияет изменение исходных чисел на итоговые данные.

3. На Листе2 выполнить копирование информации из произвольного блока ячеек:
с использованием буфера обмена;
без использования буфера обмена;
в несколько несмежных блоков ячеек;

4. Скопировать информацию из нескольких ячеек Листа1 на Лист2.

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

6. В рабочей книге «Персональный бюджет» на Листе2 выполнить перемещение информации из нескольких ячеек:
с использованием буфера обмена;
без использования буфера обмена.

7. Переместить информацию из нескольких ячеек Листа2 книги «Персональный бюджет» на Лист1 новой рабочей книги.

8. На Листе2 книги «Персональный бюджет»:
вставить один столбец;
вставить два столбца;
вставить одну строку;
вставить три строки;
удалить несколько строк;
удалить несколько столбцов.

9. Ввести любую информацию в какую-либо ячейку последнего столбца Листа2. Активизировать ячейку A1.

10. Выполнить операцию вставки столбца.

11. Очистить ячейку с информацией в последнем столбце рабочего листа.

12. Активизировать ячейку A1.Выполнить операцию вставки столбца.

13. Ввести любую информацию в какую-либо ячейку последней строки рабочего листа.

14. Активизировать ячейку A1.Выполнить операцию вставки строки.

15. Очистить ячейку с информацией в последней строке рабочего листа.

16. Активизировать ячейку A1.Выполнить операцию вставки строки.

17. Выполнить вставку ячеек в заполненной области Листа2:
со смещением заполненных ячеек вправо;
со смещением заполненных ячеек вниз.

18. Удалить несколько ячеек в заполненной области Листа2:
со смещением ячеек вверх;
со смещением ячеек влево.

19. В ячейку A200 Листа2 ввести формулу: =5*2000.

20. В ячейку A400 Листа2 ввести число 10000.

21. Переместиться в ячейку A1.

22. Выполнить поиск числа 10000:
в слое «значения»;
в слое «формулы».

23. На Листе2 выполнить замену повторяющихся фрагментов информации:
в режиме «глобальная замена»;
в режиме «пошаговая замена»

24. В случае необходимости внести изменения в таблицу «Персональный бюджет»:
отредактировать информацию в ячейках;
вставить недостающие строки и столбцы.

Работа с листами

1) Присвоить имена листам
Лист1 - Бюджет;
Лист2 - Черновик.

2) Вставить несколько листов в книгу так, чтобы в ней содержалось 10-12 листов.

3) Выделить несколько листов:
расположенных подряд;
расположенных в произвольном порядке.

4) Выделить все листы рабочей книги. Снять выделение.

5) Поменять местами несколько листов в рабочей книге. Восстановить порядок листов так, чтобы их номера следовали от 1 до 10 (12).

Ход работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу расчета заработной платы по образцу (см. рис.). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

Рис. Исходные данные для Задания

3. Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премия = Оклад х % Премии, в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации

Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т. е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия. При расчете Удержания используется формула

Удержание = Всего начислено х % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * Е5. Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

4. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория — Статистические функции).

5. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис.

Краткая справка

Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

6. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис.).

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

7. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.

8. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по формуле Доплата = Оклад х % Доплаты. Значение доплаты примите равным 5 %.

9. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

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

10. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10 000 — зеленым цветом шрифта; меньше 7000 — красным; больше или равно 10 000 — синим цветом шрифта (Формат/Условное форматирование) (рис.).

Рис. Копирование листа электронной книги

11. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы — без итогов, выберите меню Данные/Сортировка, сортировать по — Столбец В) (рис.).

Рис. Условное форматирование данных

12. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рис.

13. Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист (рис.), сделайте подтверждение пароля (рис.).

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

Рис. Конечный вид зарплаты за ноябрь

Рис. Защита листа электронной книги

Рис. Подтверждение пароля

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).

14. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

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

Задание. Сделать примечания к двум-трем ячейкам.

Задание. Выполнить условное форматирование оклада и премии за ноябрь месяц:

до 2000 р. — желтым цветом заливки;

от 2000 до 10 000 р. — зеленым цветом шрифта;

свыше 10 000 р. — малиновым цветом заливки, белым цветом шрифта.

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

Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».

Закрепление материала

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

Задача.

Задача. Дано наименование нескольких основных продуктов питания и их стоимости на начало года. Принимая условно, что каждый месяц определенных товаров на сколько-то процентов дорожает товар, найти их стоимости в конце месяца.

[1] Если строка формул отсутствует на экране, ее можно вывести командой Вид/Строка формул.

[2] На экране дата тем не менее отображается в форме даты.

12 3 4