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

Заполнение ячеек формулой:

Задание 2.3 Ввод и редактирование формул:

1. В файле с названием D:\студенты\Ведомость.xls в ячейку Е2 ввести текст «Премия 20%», в ячейку F2 – «Премия 50%».

2. В ячейку Е3 ввести формулу =C3*20%, указав адрес ячейки С3 методом «наведи и щелкни», завершить ввод нажатием Enter.

3. Выделить ячейку Е3, протащить маркер заполнения вниз до ячейки Е8, отпустить кнопку мыши, формула введена во все ячейки.

4. В ячейку F3 ввести формулу =C3*50%, указав адрес ячейки С3 методом «наведи и щелкни», нажать Enter, выделить ячейку F3, протащить маркер заполнения вниз до ячейки F8.

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

6. Поочередно выделить ячейки с вычисленными значениями в столбцах Е и F, выявить закономерность формирования формул (номера строк при переходе от ячейки к ячейке вниз увеличиваются на 1).

7. Сохранить файл нажатием на кнопку на панели инструментов Стандартная.

Относительные и абсолютные ссылки

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

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

1. Начать работу с файлом Ведомость.xls. следующим образом.

В ячейку G2 ввести текст «Премиальный коэффициент», в ячейку G3 ввести 25%, определить тип данных в ячейке G3 как процентный.

2. В ячейку Н2 ввести текст «Премия», в ячейку Н3 ввести формулу =С3*G3, указав адреса ячеек С3, G3 методом «наведи и щелкни», завершить ввод нажатием Enter.

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

4. Сделать ссылку на ячейку G3 абсолютной.

Создание абсолютной ссылки:

5. Еще раз протащить формулу из ячейки Н3 на остальные ячейки столбца Н, теперь ошибок нет. Убедитесь, что в формулах в ячейках Н4:Н8 ссылки на столбец С остались относительными, а на ячейку G3 стали абсолютными. Поменяйте значение в ячейке G3 на 50% и убедитесь, что результаты в столбце Н изменились. Отмените операцию кнопкой на панели инструментов Стандартная.

6. Сохраните файл нажатием на кнопку на панели инструментов Стандартная.

Функции

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

a) Знака равенства «=«;

b) Названия функции, например СУММ, SIN;

c) Аргумента. Тип аргумента зависит от функции и может состоять из чисел, текста, логических величин (например, ИСТИНА или ЛОЖЬ), массивов, ссылок, формул и т.д.

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

Для задания функции можно использовать пиктограммы на панели инструментов (Автосуммирование), (Мастер функций) или кнопку «=« в строке формул . При вводе функции перед ее именем автоматически вводится знак «=«.

Задача: Создайте файл «Ведомость учета оклада» по образцу.

1. Начните работу с файлом Ведомость.xls. следующим образом.

В ячейку Н2 ввести текст «Премия», в ячейку В9 ввести «Итого». В ячейку Н9 поместить результат вычислений.

2. Выделить для размещения результата ячейку Н9, щелкнуть на панели инструментов пиктограмму Автосуммирование .

В ячейке Н9 и в строке формул появилась запись формулы, а ячейки Н3:Н8, ссылки на которые содержатся в формуле, обведены пунктирной линией. Мастер формул выделяет диапазон ячеек и использует его как аргумент, который заключен в скобки. Если необходимо изменить адреса ячеек в формуле, то это можно сделать в строке формул. Завершить ввод нажатием Enter.

3. По аналогии с п.2. упражнения заполнить ячейки Е9, F9 соответствующими формулами, используя функцию Автосуммирование.

4. В ячейке В10 ввести «Средний», в ячейке С10 вычислить средний по учреждению оклад, используя встроенную функцию СРЗНАЧ. Для этого выделить ячейку С10, щелкнуть на панели инструментов пиктограмму. На экране появится окно Мастера функций. Выбрать в категории «Статистические» функцию «СРЗНАЧ», нажать кнопку ОК или Enter.

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

В поле «Число 1» задан диапазон ячеек С3:С8 для расчета. Нажатие на кнопку в поле «Число 1» сворачивает окно, проверьте, тот ли диапазон включен в формулу, при необходимости исправьте прямо в поле «Число 1». Для того, чтобы обратно развернуть окно, достаточно нажать кнопку . Для завершения ввода нажать ОК или Enter.

6. В ячейке В11 ввести «Минимальный», в ячейке С11 вычислить минимальный по учреждению оклад, используя функцию МИН.

Выделить ячейку С11, запустить мастер функций пиктограммой . Выбрать функцию МИН в категории «Статистические», кнопкой свернуть окно, мышью выделить в таблице диапазон C3:C8, он будет обведен пунктирной рамкой. Для выбора данного аргумента нажать Enter. Развернуть окно кнопкой , для завершения ввода нажать ОК или Enter.

7. Сохранить файл.

Необходимо решить следующую задачу: создать экзаменационную ведомость, заполнить и рассчитать количество различных оценок.

1) Введите тексты заголовка и шапки таблицы по следующей технологии:

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

Содержимое ячеек:

В1- ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ; А3-Группа №; С3-Дисциплина; А5-№ п/п; В5-Фамилия, имя, отчество; С5-№ зачетной книжки; D5-оценка; Е5-Подпись экзаменатора (записи вводим полностью, без всяких сокращений).

2) Отформатируйте ячейки А1-Е1:

Выделите блок ячеек А1– Е1, щелкните правой кнопкой мыши по выделению, для вызова контекстного меню. В нем выберете команду Формат ячеек. На вкладке Выравнивание выберете опции: по горизонтали: по центру выделения; по вертикали: по верхнему краю. На вкладыше Шрифт нажмите кнопку размер и выберете 14пт и сделайте его полужирным.

3) Отформатируйте шапку таблицы.

Выделите блок ячеек А3:J5, где располагается шапка таблицы. Нажмите правую кнопку мыши на выделенном блоке ячеек и выберите команду Формат ячеек. На вкладке Выравнивание задайте параметры: по горизонтали: по значению; по вертикали: по верхнему краю; переносить по словам: поставить флажок, нажмите кнопку OK.

4)Установите нужную ширину столбцов.

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

5) Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10-15 строк. (имя отчество вводить только инициалы).

6) Присвойте каждому студенту порядковый номер.

Введите в ячейку А6 число 1. Установите курсор мыши в нижний правый угол ячейки А6, так, чтобы он приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемое количество ячеек, отпустив мышку выберите команду локального меню Заполнить.

7) После списка студентов в нижней части таблицы введите в ячейки столбцов А текст итоговых строк: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, Итого.

8) Объедините две соседние ячейки, где расположены итоговые надписи, покажем это на примере объединения двух ячеек, где расположена надпись Отлично. Выделите две ячейки: ячейку Отлично и ячейку находящуюся справа от нее. Нажмите правую клавишу мыши на выделении и выберите команду Формат ячеек. На вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку Ok. Проведите аналогичные действия с остальными итоговыми ячейками;

9) Заполните номера зачеток.

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

10) Заполните графу оценки вводя следующие оценки 5, 4, 3, 2, неявка.

11) Введите дополнительное кол-во столбцов, для подсчета кол-ва оценок, на каждый вид оценки, всего пять: в ячейке F5 – написать ОТЛИЧНО, в G5 – ХОРОШО, в H5 – УДОВЛЕТВ., в I5 – НЕУДОВЛ., в J5 – НЕЯВКА.

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

Ее суть: например, если студент Снегирев получил 5, то в ячейке F6 отображается 1, а в остальных вспомогательных столбцах G-J, в данной строке появится 0.

В ячейке F6 напишите =ЕСЛИ(D6=5;1;0); в ячейке G6 =ЕСЛИ(D6=4;1;0); в ячейке H6 =ЕСЛИ(D6=3;1;0); в ячейке I6 =ЕСЛИ(D6=2;1;0); в ячейке J6 =ЕСЛИ(D6=«неявка»;1;0);

13) Скопируйте эти формулы во все остальные ячейки дополнительных столбцов.

Для этого выделите блок ячеек F6:J6. Установите курсор в нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, перетащите ее до конца таблицы. Выберете в контекстном меню команду Заполнить значения.

14) Определите имена блоков ячеек по каждому дополнительному столбцу.

Рассмотрим на примере дополнительного столбца F. Выделите все значения столбца F (только числа, без названия столбцов). Выберите команду Вставка, Имя, Присвоить. В диалоговом окне в строке Имя введите слово ОТЛИЧНО и щелкните кнопкой Добавить. Проведите аналогичные действия с остальными столбцами, создайте еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.

15) Сделайте столбец F-J скрытым. Выделите столбцы F-J и выполните команду Формат, Столбец, Скрыть.

16) Подсчет количества оценок.

Установите курсор в ячейку ряда С, напротив ОТЛИЧНО. Выполните команду Вставка, Функция и в появившемся окне выберите: Категория Математические, функция Сумм. В следующем окне в строке Число1 установите курсор и выполните Вставка, Имя, Вставить. В появившемся окне выберете имя Отлично. Повторите аналогичные действия для подсчета кол-ва других оценок в ячейках С14-С17.

Подсчитайте Количество ИТОГО (количество студентов явившихся на экзамен) самостоятельно.

ВЫПОЛНИТЕ САМОСТОЯТЕЛЬНО:

1. Составьте табель успеваемости студентов по предметам (высшая математика, физика, информатика, физкультура, русский язык).

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

Постройте диаграмму успеваемости студентов, не менее 10 человек (по средним значениям).

2. Составьте расчет оклада сотрудников предприятия ООО «Альянс».

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

Посчитайте итоговую сумму оклада.

Укажите минимальную и максимальную сумму оклада.

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

1. Что такое функция?

2. Что такое формула?

3. В чем сходство и отличие в записи формул и функций в Ms Excel?

4. Сколько функций существует в Ms Excel?

5. С чего начинается работа с формулами и функциями в табличном процессоре?

6. Какими способами возможно создание и редактирование формул и функций в Ms Excel?

7. Из чего состоит запись каждой функции?

8. Что такое синтаксис функции? Для чего он необходим?

9. Опишите структуру задания функции.

10. Что такое аргументы функции?

11. Что может быть аргументом функции?