Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул табличный процессор лишается основного своего функционала.
Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул.
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
Ссылки можно комбинировать в рамках одной формулы с простыми числами.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.
- Вручную заполним первые графы учебной таблицы.
- Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
- Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Ссылки в ячейке соотнесены со строкой.
Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).
Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:
- Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
- Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
- Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.
При создании формул используются следующие форматы абсолютных ссылок:
- $В$2 – при копировании остается постоянной ячейка B2;
- B$2 – при копировании неизменна строка под номером 2;
- $B2 – столбец В не изменяется.
Выполнение практической части:
Задание 1
1. Создать рабочую книгу.
Технология работы:
1 способ: Запуск программы:
Пуск → Все программы → Microsoft Office → Microsoft Excel 2010.
2. Переименовать рабочий лист.
Технология работы:
ü Установите указатель мыши на Лист 1 и вызовите контекстное меню, щелкнув правой клавишей мыши;
ü выберите в контекстном меню команду Переименовать;
ü введите в диалоговом окне новое имя листа: Фирма Консульт.
3. Создать на листе Фирма Консульт таблицу по образцу Список сотрудников фирмы «Консульт».
2 вариант Скопировать таблицу из предыдущей практической работы, добавить столбцы Должностной оклад, Стаж, Надбавка за владение иностранным языком, К выплате.
Технология работы:
Нам нужно добавить для начала столбец Должностной оклад между столбцами Должность и Дата приема на работу
ü Выделите весь столбец, содержащий Дату приема на работу;
ü Вызовите контекстное меню и выберите в нем пункт Вставить. В результате слева от выделенного столбца появится пустой столбец (с уже нарисованными границами)
ü В соответствующей ячейке добавленного столбца, заполните и отформатируйте по образцу шапку таблицы с соответствующим направлением текста и цветом заливки ячейки – Должностной оклад
4. Добавить и заполнить оставшиеся столбцы.
5. Выберите ширину добавленных столбцов таблицы в соответствии с образцом
6. Вычислить и вписать в отчет формулы:
· Должностной оклад (грн) (okl): начальник отдела — 15000 руб; аудитор — 14000 руб; консультант — 12000 руб
· средние, максимальные, минимальные значения для полей, которые обозначены х;
· Всего по полю К выплате.
· Размер Надбавки за владение иностранным языком (грн) в размере 25 %*okl при условии владения иностранным языком.
· поле К выплате как сумму должностного оклада и надбавки за владение иностранным языком.
Технология работы:
Технология выполнения задания приведена в видеоинструкции в отдельной ссылке.
7. Сохраните рабочую книгу, тип файла - xlsх
ü выберите команду Файл → Сохранить как;
ü имя файла - Пр№12_Иванов1_1ТОРАТ19 (указать вашу фамилию)
Задание 2
Создайте / переменуйте лист Вычисления
На листе Вычисления вычислить х по формуле
где a = 9, b = 3, c = 5, d =7.
| Переменная | Значение |
| а | 9 |
| b | 3 |
| c | 5 |
| d | 7 |
| х |
Технология работы:
Технология выполнения задания приведена в видеоинструкции в отдельной ссылке.
Контрольные вопросы:
1. Для чего используются формулы в Excel?
2. Что может включать в себя формула?
3. Какие операции в формулах имеют максимальный приоритет? Минимальный приоритет?
4. Опишите последовательность действий ввода формулы в ячейке?
5. Какие виды ссылок на ячейки используются в Excel?
6. Перечислите форматы абсолютных ссылок.
Отчет о выполнении практической работы выполняется в тетради и должен содержать:
1. Номер практической работы.
2. Тему работы.
3. Цель работы.
4. Ответы на контрольные вопросы.
5. Файл Excel с выполненным заданием.
Отчет (в виде фотографий (или скан-копий) тетрадных страниц, файл с таблицей Excel) отправить по электронной почте на адрес преподавателя Пожидаева Д.А. denis.pozhidaev.80@gmail.com тел. 071 362 51 91