Пример выполнения расчетов в таблице

Цель работы

Научиться использовать формулы и основные функции Excel. Научиться применять абсолютную и относительную адресацию ячеек.

Задание

Ознакомьтесь с теоретическими сведениями, приведенными ниже.

Создайте новую книгу Excel и сохраните ее под именем вида «Excel. Л.р. 2 группа ФИО».

Каждое задание выполняется на отдельном листе Excel. Листы следует переименовать в «Задание 1», «Задание 2» и т.д. В начале каждого листа в качестве заголовка указывать номер и название работы, номер задания, сведения о том, кто выполнил работу (ФИО, группа).

Не используйте в заданиях вставку таблицы на лист или стили таблиц. Просто настройте заливку и границы, где это требуется.

Задание 1. Выполните расчет количества и стоимости стройматериалов для ремонта прямоугольной комнаты, в которой есть одна дверь и одно окно. Размеры комнаты, двери и окна задайте произвольными. Вычислите:

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

- сколько потребуется рулонов обоев (53 см x 10,05 м) для оклейки стен, предусмотрев 15 % запас на обрезки;

- сколько потребуется клея, если одной упаковки хватит на 5 рулонов;

- сколько потребуется напольного плинтуса длиной 2,5 м.

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

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

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

Задание 2. Заполните формулами столбцы в таблице для расчета заработной платы. Для вычисления заработной платы нужно оклад умножить на ставку, а полученную сумму увеличить на премию. Ставка НДФЛ равна 13%.

Месяц	Год	ФИО	Оклад	Ставка	Премия	Заработная плата	НДФЛ	К выплате
июль		Трегубенков И.А.	24 300,00	1,0	100%
июль		Солдатова А.К.	21 700,00	0,5	50%
июль		Семенова И.Н.	14 200,00	1,25	100%
июль		Новиков Е.В.	18 600,00	1,0	75%
июль		Лисичкин Д.Г.	9 300,00	0,8	50%
август		Трегубенков И.А.	24 300,00	1,0	100%
август		Солдатова А.К.	21 700,00	0,5	0%
август		Семенова И.Н.	14 200,00	1,5	100%
август		Новиков Е.В.	18 600,00	0,9	50%
сентябрь		Трегубенков И.А.	29 160,00	1,0	50%
сентябрь		Солдатова А.К.	26 040,00	0,5	80%
сентябрь		Семенова И.Н.	17 040,00	1,0	100%
сентябрь		Новиков Е.В.	22 320,00	0,9	80%
октябрь		Трегубенков И.А.	29 160,00	1,25	75%
октябрь		Солдатова А.К.	26 040,00	0,8	80%
октябрь		Семенова И.Н.	17 040,00	1,0	75%
октябрь		Корнеев А.В.	11 040,00	0,5	50%
октябрь		Новиков Е.В.	22 320,00	0,5	70%
ноябрь		Трегубенков И.А.	29 160,00	1,25	75%
ноябрь		Солдатова А.К.	26 040,00	0,8	50%
ноябрь		Семенова И.Н.	19 240,00	1,0	90%
ноябрь		Корнеев А.В.	11 040,00	0,5	50%
ноябрь		Новиков Е.В.	25 440,00	0,2	100%

Добавьте в конец таблицы строки с заработной платой за декабрь: сотрудники и ставки те же, что и в ноябре, всем сотрудникам повысить оклад на 5%, премия у всех 100%.

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

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

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

			Доллар	Евро	Фунт	Йена
Курс валюты
Наименование	Количе­ство	Цена	Стоимость
Руб.	$	?	£	¥	Руб.	$	?	£	¥
Телевизор
Проектор
Мышь компьютерная			7,5
Клавиатура			12,3
Флеш-карта			3,1
Колонки			9,89
Принтер лазерный			273,8
Пачка бумаги			1,75
Картридж для принтера

 

Выделите столбцы каждой валюты своим цветом. Вычислите итоговые суммы.

Задание 4. Выполнить табулирование значений y (x). Заполните:

1) столбец A номерами (1, 2, 3, 4, …);

2) столбец B значениями x от –100 до 100 с шагом 5 (–100, –95, …, 0, 5, 10, …100);

3) столбец C значениями y:

где a – ваш номер в группе по списку,

b, c, d – день, месяц и год (двумя цифрами) рождения.

Например, номер по списку 11, дата рождения 02.10.1996. Тогда a = 11, b = 2, c = 10, d = 96.

Разместите на листе над таблицей формулу y в формате Microsoft Equation. Значения a, b, c, d должны быть выписаны в отдельные ячейки. Под таблицей вычислите средние значения x и y.

Теоретическая часть

Формулы

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

Расчеты в Excel выполняются с помощью формул. Формула должна начинаться со знака “=”, а дальше записываются различные действия со значениями ячеек.

Например, вычислим сумму чисел, записанных в ячейках A2 и B2:

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

Если изменить исходные значения в ячейках A2 и B2, то результат мгновенно пересчитается:

Запись арифметических действий:

Действие	Запись	Пример	A2	B2	Результат
сложение	+	=A2+B2
вычитание	-	=A2-B2
умножение	*	=A2*B2
деление	/	=A2/B2
возведение в степень	^	=A2^B2

Функции

Для более сложных расчетов применяются функции. Библиотека функций находится на вкладке «Формулы» на ленте.

Также можно использовать кнопку слева от строки формул. При этом откроется окно «Мастер функций», где можно искать функции по названию и описанию, а также ознакомиться со справкой.

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

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

Некоторые часто используемые функции:

Функция	Примеры	Пояснение
СУММ(значение1; знчение2;...)	=СУММ(A2:B10) =СУММ(C4;C10;C12)	Суммирует все указанные значения (можно указать отдельные ячейки или диапазоны)
СРЗНАЧ(значение1; знчение2;...)	=СРЗНАЧ(A2:B10)	Вычисляет среднее значение
СЧЁТ(диапазон)	=СЧЁТ(G11:G44)	Считает количество значений в диапазоне. Пустые ячейки и текст не учитываются.
МИН(диапазон)	=МИН(A2:A100)	Находит самое маленькое число в указанном диапазоне
МАКС(диапазон)	=МАКС(B4:B20)	Находит самое больше число в указанном диапазоне
ОКРУГЛ(число; знаков_после_запятой)	=ОКРУГЛ(B14; 0) =ОКРУГЛ(0,123789; 2)	Округляет число до указанного числа знаков после запятой (0 – до целых)
ОКРУГЛВВЕРХ(число; знаков_после_запятой)	= ОКРУГЛВВЕРХ (B14; 0)	Округляет число вверх, т.е. до большего значения
ОКРУГЛВНИЗ(число; знаков_после_запятой)	= ОКРУГЛВНИЗ (B14; 0)	Округляет число вниз, т.е. до меньшего значения
КОРЕНЬ(число)	=КОРЕНЬ(B8)	Извлечение квадратного корня из числа

Суммирование и несколько других самых популярных функций можно найти на вкладке «Главная» под кнопкой суммы:

Функции могут быть вложенными, например, извлечь квадратный корень, а потом округлить результат до целых:

=ОКРУГЛ(КОРЕНЬ(A5);0)

Адреса ячеек

A1, A2, B1, B2 и т.д. – это адреса ячеек: буквами обозначается номер столбца, цифрами – номер строки. После столбца Z идут столбцы AA, AB, AC, … AZ, BA, BB, …, ZZ, AAA, AAB, …

Несколько соседних (смежных) ячеек образуют диапазон, который записывается через двоеточие, например, A2:B5 (читается «с A два по B пять»).

Несмежные ячейки можно записывать через точку с запятой, например, B8; C12; D7.

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

Варианты адресов ячеек:

Пояснение	Запись	Примеры
Ячейка – буквы столбца и номер строки слитно	адрес	A9 C14 FG432
Диапазон ячеек – верхняя левая и правая нижняя ячейки через двоеточие	адрес_начала:адрес_конца	A1:C12 D6:G39 B:B (весь столбец) 4:4 (вся строка)
Несколько несмежных ячеек – через точку с запятой	адрес1;адрес2;адрес3 …	A1;C1;E1 A1;B2:B6
На другом листе – имя листа (если есть пробелы, то в кавычках), восклицательный знак, адрес ячейки	Лист!адрес	Лист1!A2 ‘Задание 1’!C12:E15
В другой книге – имя книги в квадратных скобках, потом лист), восклицательный знак, потом ячейка	[книга]Лист!адрес	[книга1.xlsx]Лист1!G34

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

Адрес может быть абсолютным и относительным.

Относительный адрес – это обычная запись. При копировании ячейки Excel запоминает не сам адрес, а его положение относительно текущей ячейки. При вставке или автозаполнении относительный адрес меняется. Например, на скриншоте выше в формуле будет скопировано не A2, а «на 0 строк вниз и на 4 столбца влево», не C2, а «на 0 строк вниз на и 2 строки влево». Если скопировать формулу из E2 в E3, то A2 и C2 превратятся в A3 и C3.

Абсолютный адрес копируется «как есть» и не меняется при вставке. Чтобы сделать адрес абсолютным, нужно поставить значки доллара $ перед столбцом и/или строкой, например, $A$5.

Чтобы быстро поставить $ используйте клавишу F4 при наборе формулы.

Применение абсолютной и относительной адресации на практике рассмотрено в примере ниже.

Ошибки вычислений

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

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

Наиболее часто встречаются ошибки:

#ДЕЛ/0	Деление на 0	Деление на пустую ячейку (пустая ячейка – это тоже 0)
#ЗНАЧ!	Ошибка в значении	В расчеты вместо числа попала ячейка с текстом
#ССЫЛКА	Неправильная ссылка на ячейку	При копировании относительный адрес оказался за границами листа
#ИМЯ?	Недопустимое имя	Неправильно записано имя функции или адрес ячейки, например, перепутаны русские и английские буквы

 

Пример выполнения расчетов в таблице

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

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

Продажи товаров
Наименование	Цена	Количество	Ед. изм.	Стоимость
Хлеб	21,50 ₽		шт.
Молоко	64,80 ₽		уп.
Масло	88,50 ₽		уп.
Сыр	512,00 ₽	0,254	кг
Сахар	45,20 ₽		уп.
Печенье	55,90 ₽		уп.
Апельсины	90,00 ₽	1,456	кг
Картофель	26,30 ₽	2,654	кг
Творог	98,70 ₽		уп.
Салфетки	22,20 ₽		уп.
Сок	75,60 ₽		уп.
Лимон	11,00 ₽		шт.
Вода столовая	7,50 ₽		л

Обратите внимание, цене назначен денежный формат, и значок рубля отображается прямо в ячейке. Однако единицы измерения количества товара вынесены в отдельный столбец. Если написать в одну ячейку число и текст, то Excel будет считать ее текстом и выполнять вычисления с такой ячейкой нельзя (ошибка #ЗНАЧ!).

Стоимость

Вычислим стоимость (= цена * количество). Сначала введем формулу в ячейку E4.

После ввода формулы нужно обязательно нажать Enter, чтобы подтвердить ее.

Во всех остальных ячейках ниже формула должна быть точно такая же, только вместо 4 строки в адресах B4, C4 будет 5, потом 6 и т.д.

Весь столбец можно заполнить сразу с помощью автозаполнения. Нужно:

1) выделить ячейку с формулой (E4);

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

3) захватить мышью квадратик и потянуть до конца столбца.

При этом все адреса в формулах сместились вниз, т.е. в направлении, в котором мы «тянули» формулу.

Так работает относительная адресация.

Примечание – тот же результат можно получить, если скопировать ячейку, потом выделить весь столбец в таблице и вставить. Но если копировать текст ячейки в режиме редактирования, то относительная адресация не сработает.

Причем мы потратим на расчет почти столько же времени, даже если таблица будет огромной – с тысячами или десятками тысяч строк. Формулу все равно достаточно ввести один раз. Удобно, не правда ли?

Далее настроим стоимости денежный формат и добавим под таблицей строку итогов с формулой суммы.

При использовании кнопки автосуммы Excel сам «угадывает» диапазон суммирования. Если вводить формулу вручную, то диапазон нужно выделить мышью.

Скидка

Добавим еще один расчет: вычислим стоимость со скидкой 10 %.

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

Для удобства сделаем два столбца: скидка в рублях и стоимость со скидкой.

Скидка = 10% * Стоимость

При этом 10% не нужно делить на 100, т.к. Excel и так считает, что 10%=0,10.

Стоимость со скидкой = Стоимость – Скидка

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

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

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

В нашем решении есть недостаток: скидка обычно величина непостоянная и часто меняется. Например, если скидка будет не 10%, а 5%, то придется опять переписать формулу и выполнить автозаполнение. А главное, на листе не видно, какая сейчас назначена скидка.

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

Примечание – Мы добавили две новых строки для скидки, и формулы в ячейках опять изменились автоматически: в стоимости B4*C4 превратилось в B6*C6 и т.д.

Подставим в формулу скидки вместо 10% адрес ячейки B3.

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

Мы намеренно допустили здесь ошибку, чтобы лучше понять, как это работает.

Давайте посмотрим, какая формула получилась в F8 (содержит ошибку #ЗНАЧ).

Как видите, из-за относительной адресации ячейка B3 сместилась вниз и вместо 10% в формулу попало слово «Цена». Excel не знает, как умножить слово «Цена» на 177 руб., поэтому выдает ошибку. То же самое получилось и в других формулах, только туда вместо 10% попали разные числа, поэтому ошибку Excel не выдал.

Значит, для ячейки B3 нужно использовать абсолютную адресацию.

Вернемся в ячейку F6 и добавим значки $ к адресу B3 (горячая кнопка F4).

Повторим автозаполнение – теперь все правильно, $B$3 осталась $B$3.

Теперь для изменения скидки достаточно ввести в B3 новое значение:

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

Округление

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

Например, при скидке в 1 % от суммы 21,5 руб., сумма скидки составит 0,215. При этом в ячейке в денежном формате отобразится 0,22 руб., но в расчетах все равно будет использоваться 0,215, т.е. фактически никакого округления до копеек нет!

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

Поэтому добавим в формулу скидки округление до 2 знаков после запятой. Для этого воспользуемся функцией ОКРУГЛ. У нее два аргумента: округляемое значение (наша формула скидки) и число знаков после запятой (2).

Не забудем выполнить автозаполнение для всей таблицы.

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

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

Это округление вниз, функция ОКРУГЛВНИЗ (не путайте с ОКРВНИЗ, это разные формулы!). Аргументы у нее такие же, как и у ОКРУГЛ, но результат всегда округляется в меньшую сторону.

Проверим результат для скидки 10%.

В теоретической части в таблице есть и третий вариант округления – ОКРУГЛВВЕРХ. Он понадобится вам для выполнения заданий, но в этом примере его нет.

Контрольные вопросы

1. Что такое адрес ячейки?

2. Чем отличается адрес A1:B5 от A1;B5?

3. Как указать название листа в адресе ячейки?

4. Как возвести значение в степень?

5. Где находится библиотека функций?

6. Что такое аргументы функции? Какие аргументы есть у функции СУММ? ОКРУГЛ?

7. Чем отличаются ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ?

8. Что такое абсолютная и относительная адресация ячеек?

9. Что означает ошибка #ЗНАЧ!? ошибка #ИМЯ??

10. Как получить справку по ошибке?

Поделиться:

Воспользуйтесь поиском по сайту: