Краткие теоретические сведения

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

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

Относительная, абсолютная и смешанная адресация

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

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

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

Относительная ссылка — автоматически изменяющаяся при копировании формулы ссылка, например A1. Относительная ссылка используется в формуле в том случае, когда она должна измениться после копирования.

Абсолютная ссылка — не изменяющаяся при копировании формулы ссылка, например $A$1. Абсолютная ссылка записывается в формуле в том случае, если при ее копировании не должны изменяться обе части: буква столбца и номер строки. Это указывается с помощью символа $, который ставится и перед буквой столбца и перед номером строки.

Смешанная ссылка — частично изменяющаяся при копировании ссылка, например $A1. Смешанная ссылка используется, когда при копировании формулы может изменяться только какая-то одна часть ссылки — либо буква столбца, либо номер строки. При этом символ $ ставится перед частью ссылки, которая должна остаться неизменной.

Правило копирования формул

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

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

Порядок копирования формулы из ячейки в диапазон:

ü выделить ячейку, где введена исходная формула;

ü скопировать эту формулу в буфер обмена;

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

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

Порядок копирования формул из одного диапазона в другой:

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

ü скопировать формулы из выделенного диапазона в буфер;

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

ü вставить формулы из буфера.

Основные понятия и правила записи функций.

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

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

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

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

В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах MS Excel можно использовать до семи уровней вложенности функций. Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.

Типы функций

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

Использование всех функций в формулах происходит по совершенно одинаковым правилам:

ü каждая функция имеет свое неповторимое (уникальное) «имя»;

ü при обращении к функции после ее имени в круглых скобках указывается список аргументов, разделенных точкой с запятой;

ü ввод функции в ячейку надо начинать со знака «=», а затем указать ее имя.

Задания и инструкции по выполнению

Задание 1 Расчет зарплаты за октябрь.   

1. Открыть программу электронных таблиц Microsoft Excel.

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

3. Создать таблицу по образцу.

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

ü при расчете Премии используется формула Премия = Оклад * % Премии, для этого в ячейке D5 ввести формулу = SD$4 * С5 (ячейка D4 используется в виде абсолютной адресации, для установки абсолютной адресации нажать клавишу F4) и скопировать формулу ав­тозаполнением.

ü для расчета Всего начислено используется формула Всего начислено = Оклад + Премия (осуществить щелчки по ячейкам и применить ав­тозаполнение).

ü для расчета Удержания используется формула Удержание = Всего начислено * % Удержания, для этого в ячейке F5 ввести формулу = $F$4 * Е5 ( применить ав­тозаполнение);

ü для расчета столбца К выдаче используется формула К выдаче = Всего начислено – Удержания (применить ав­тозаполнение).

5. Рассчитать итоги по столбцам, используя функцию СУММ

6. Рассчитать максимальный, ми­нимальный и средний доходы по данным колонки К выдаче, используя команды Формулы—Другие—Статистические (МИН, МАХ, СРЗНАЧ).

Задание 2 Расчет зарплаты за ноябрь.   

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

2. Скопировать содержимое листа Зарплата октябрь на новый лист (Главная—Копировать—Вставить)

3. Исправить название месяца в названии таблицы. Изменить значение Премии 27% на 32 %. Убедитесь, что программа произвела пере­счет формул.

4. Между колонками Премия и Всего начислено вставить новую колонку Доплата (Главная—Вставить—Вставить ячейки).

5. Рассчитать значе­ние доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты принять равным 5 %.

6. Изменить формулу для расчета значений колонки Всего на­числено.

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

7. Провести условное фор­матирование значений колонки К выдаче. Установить формат вывода:

ü значений между 7000 и 10000 — зеленым цветом шриф­та; меньше 7000 — красным;

ü боль­ше или равно 10000 — синим цве­том шрифта (Главная--Условное форматирование—Правила выделения ячеек).

8. Осуществить сортировку по столбцу Фа­милия в алфавитном порядке, по возрастанию, для этого следует выделить фрагмент с 5 по 18 строки таблицы (без итогов) и применить команду Данные—Сортировка-- сортировать по — Столбец В.

9. Создать к ячейке D3 комментарии Премия пропорцио­нальна окладу, для этого применить команду Рецензирование--Создать примечание, при этом в правом верх­нем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен рисунке.

Задание 3 Построение диаграмм.   

1. Построить круговую диаграмму на листе Зарплата за ноябрь по столбцам:

ü Фамилия и К выдаче;

ü Фамилия и Всего Начислено;

ü Фамилия и Премия.

Задание 4 Защита листа.   

1. Защитить лист Зарплата ноябрь от изменений, для этого следует использовать команду Рецензирование -- Защитить лист. Задать пароль на лист 123.

Задание 5 Применение условного форматирования

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

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

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

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

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

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

4. Построить круговую диаграмму начисленной сум­мы к выдаче всех сотрудников за октябрь месяц. Сохранить файл под именем ЗАРПЛАТА в своей папке.