Задание 2.Адресация ячеек. Формулы Задание
Выполните указанные в задании расчеты, используя формулы Excel. Также выполните итоговые расчеты по таблице.
Указания к выполнению
Создайте копию листа «Задание 1» и назовите его «Задание 2».
Для этого кликните правой кнопкой по ярлыку листа и выберите «Переместить/Скопировать»:
Через это окно можно копировать листы и в другие открытые книги.
Заполните формулами первую строку таблицы.
Excel – это мощный инструмент для вычислений. Все введенные в ячейки данные могут быть использованы для последующих расчетов.
Расчеты в Excel выполняются с помощью формул. Формула должна начинаться со знака “=”, а дальше записываются различные действия со значениями ячеек, например:
Арифметические действия:
|
Пример
|
Результат
|
сложение
|
=A2+B2
|
18
|
вычитание
|
=A2-B2
|
14
|
умножение
|
=A2*B2
|
32
|
деление
|
=A2/B2
|
8
|
возведение в степень
|
=A2^B2
|
256
|
квадратный корень
|
=корень(A1)
|
4
|
A2, B2 – это адреса ячеек.
Варианты адресов ячеек:
|
Шаблон
|
Примеры
|
ячейка
|
адрес
|
A9
C14
FG432
|
диапазон ячеек
|
адрес_начала:адрес_конца
|
A1:C12
D6:G39
B:B (весь столбец)
4:4 (вся строка)
|
на другом листе (кавычки ставятся, если в названии листа есть пробелы)
|
Лист!адрес
|
Лист1!A2
“Задание 1”!C12:E15
|
в другой книге
|
[книга]Лист!адрес
|
[книга1.xlsx]Лист1!G34
|
Адреса ячеек не обязательно вводить с клавиатуры – достаточно кликнуть по нужной ячейке во время набора формулы. При этом она обводится цветной рамочкой и подсвечивается таким же цветом в формуле:
Расчет суммы покупки в примере:
Теперь размножим эту формулу на весь столбец. Сделать это можно несколькими способами.
Способ 1 – Растягивание
Выделите ячейку с формулой и наведите курсор на черный квадратик в ее нижнем правом углу. Курсор при этом примет вид черного крестика. Зажмите левую кнопку мыши и, не отпуская ее, потяните за этот квадратик. Таким образом растяните ячейку на весь нужный диапазон.
Растягивать можно как по вертикали, так и по горизонтали.
Способ 2 – Автоматическое растягивание
Также наведите курсор на черный квадратик в углу выделения ячейки. Зажмите Shift и дважды щелкните на этом квадратике. Этот способ сработает, только если рядом уже есть заполненная таблица, чтобы Excel знал, на сколько растягивать ячейку.
Способ 3 – Копирование
Скопируйте ячейку с формулой (Ctrl + C), выделите все ячейки, куда ее нужно поместить и нажмите «Вставить» (Ctrl+V).
При любом из этих способов все адреса ячеек в формулах сместятся вместе с ячейкой:
Это называется относительной адресацией ячеек.
Более сложные расчеты можно выполнить с помощью различных функций. В Excel есть функции для суммирования данных, проверки условий, действий с матрицами, вычисления процентов по кредитам, редактирования текста и многие другие. Полный их перечень можно увидеть на вкладке «Формулы», или нажав на кнопку возле поля ввода формулы.
Некоторые часто используемые функции:
Функция
|
Примеры
|
Пояснение
|
СУММ(значение1; знчение2;...)
|
=СУММ(A2:B10)
=СУММ(C4;B1:B10;5)
|
Суммирует все указанные значения (можно указать отдельные ячейки или диапазоны)
|
СРЗНАЧ(значение1; знчение2;...)
|
=СРЗНАЧ(A2:B10)
|
Вычисляет среднее значение
|
СЧЁТ(диапазон)
|
=СЧЁТ(G11:G44)
|
Считает количество значений в диапазоне. Пустые ячейки и текст не учитываются.
|
ЕСЛИ(условие; значение_если_выполняется; значение_если_нарушается)
|
=ЕСЛИ(A2>10;A2;A4)
=ЕСЛИ(A2=B2;”равны”;”не равны”)
=ЕСЛИ(A2=”да”;G2; H2)
|
Выбор одного из двух вариантов значений – на случай выполнения и на случай нарушения условия
|
СЧЁТЕСЛИ(диапазон;условие)
|
=СЧЁТЕСЛИ(A5:A89;”>9”)
=СЧЁТЕСЛИ(A5:A89;0)
=СЧЁТЕСЛИ(A5:A89;”<”&B9)
|
Считает количество значений в диапазоне, удовлетворяющих условию. Если нужно сравнение с ячейкой, то пишется знак сравнения в кавычках, амперсанд & и ячейка.
|
МЕСЯЦ(дата)
|
=МЕСЯЦ(02.03.04)
=МЕСЯЦ(A2)
|
Номер месяца для указанной даты (от 1 до 12)
|
ОКРУГЛ(число; знаков_после_запятой)
|
=ОКРУГЛ(B14; 0)
=ОКРУГЛ(0,123789; 2)
|
Округляет число до указанного числа знаков после запятой (0 – до целых)
|
Можно писать названия функций маленькими буквами. Нельзя ставить пробелы в формуле и писать Е вместо Ё.
Рассмотрим функцию ЕСЛИ, использующуюся для проверки условий. В примере лабораторной работы нужно предоставить скидку в 10%, если сумма покупки превышает 5000 руб. и в 5%, если сумма покупки превышает 1000 руб. Исходные данные для расчета находятся в ячейках B7; B8 (% скидки); F7; F8 (с какой суммы начинается скидка) и в столбце G (сумма покупки).
Получим формулу:
=ЕСЛИ(G11>=F8;B8*G11;ЕСЛИ(G11>=F7;B7*G11;0))
Обратите внимание – когда данные вводятся в виде процентов, делить их на 100 не нужно.
Однако, если мы растянем эту формулу на весь столбец, то получим неверный расчет. Дело в том, что ячейки B7; B8; F7; F8 не должны смещаться при растягивании формулы (должны быть одинаковыми для всех строчек). Иными словами, их адрес должен быть абсолютным.
Для этого в адрес ячейки вставляется знак $ (ввести с клавиатуры или нажать F4 на клавиатуре, когда курсор стоит на нужном адресе):
=ЕСЛИ(G11>=$F$8;$B$8*G11;ЕСЛИ(G11>=$F$7;$B$7*G11;0))
В зависимости от задачи, можно зафиксировать только строку или столбец адреса, например $F9, H$56.
Чтобы найти нужную вам функцию или понять, как она работает, пользуйтесь справкой Excel. Для каждой функции там есть подробные объяснения и примеры. Нужные вам функции вы быстро запомните, а не нужные все равно быстро забудете.
Добавьте итоговые расчеты.
В примере логично подсчитать общее количество проданных книг, сумму предоставленных книг и общую сумму продаж.
Используйте функцию суммирования диапазона ячеек СУММ. Можно использовать автосумму (вкладка «Главная» →  ). Формулы можно растягивать и по строкам.
|
