Поиск: Рекомендуем: Почему я выбрал профессую экономиста Почему одни успешнее, чем другие Периферийные устройства ЭВМ Нейроглия (или проще глия, глиальные клетки) Категории: Астрономия Биология География Другие языки Интернет Информатика История Культура Литература Логика Математика Медицина Механика Охрана труда Педагогика Политика Право Психология Религия Риторика Социология Спорт Строительство Технология Транспорт Физика Философия Финансы Химия Экология Экономика Электроника	Главная О нас Популярное ТОП Новые страницы Случайная страница Контакты FAQ ЗАКАЗАТЬ РАБОТУ Пример выполнения расчетов в таблице Excel. Лабораторная работа 2 Формулы. Адресация ячеек Цель работы Научиться использовать формулы и основные функции Excel. Научиться применять абсолютную и относительную адресацию ячеек. Задание Ознакомьтесь с теоретическими сведениями, приведенными ниже. Создайте новую книгу Excel и сохраните ее под именем вида «Excel. Л.р. 2 группа ФИО». Каждое задание выполняется на отдельном листе Excel. Листы следует переименовать в «Задание 1», «Задание 2» и т.д. В начале каждого листа в качестве заголовка указывать номер и название работы, номер задания, сведения о том, кто выполнил работу (ФИО, группа). Не используйте в заданиях вставку таблицы на лист или стили таблиц. Просто настройте заливку и границы, где это требуется. Задание 1. Выполните расчет количества и стоимости стройматериалов для ремонта прямоугольной комнаты, в которой есть одна дверь и одно окно. Размеры комнаты, двери и окна задайте произвольными. Вычислите: - сколько потребуется плитки на пол в квадратных метрах; - сколько потребуется рулонов обоев (53 см x 10,05 м) для оклейки стен, предусмотрев 15 % запас на обрезки; - сколько потребуется клея, если одной упаковки хватит на 5 рулонов; - сколько потребуется напольного плинтуса длиной 2,5 м. Обратите внимание, что приобрести можно только целое количество рулонов обоев, упаковок клея и плинтусов. Задайте цены каждому виду стройматериалов, вычислите их стоимость и общую стоимость ремонта. Самостоятельно продумайте, как разместить данные на листе, чтобы это было удобно. Все размеры и цены должны находиться в отдельных ячейках. Задание 2. Заполните формулами столбцы в таблице для расчета заработной платы. Для вычисления заработной платы нужно оклад умножить на ставку, а полученную сумму увеличить на премию. Ставка НДФЛ равна 13%. Месяц Год ФИО Оклад Ставка Премия Заработная плата НДФЛ К выплате июль 2016 Трегубенков И.А. 24 300,00 1,0 100% июль 2016 Солдатова А.К. 21 700,00 0,5 50% июль 2016 Семенова И.Н. 14 200,00 1,25 100% июль 2016 Новиков Е.В. 18 600,00 1,0 75% июль 2016 Лисичкин Д.Г. 9 300,00 0,8 50% август 2016 Трегубенков И.А. 24 300,00 1,0 100% август 2016 Солдатова А.К. 21 700,00 0,5 0% август 2016 Семенова И.Н. 14 200,00 1,5 100% август 2016 Новиков Е.В. 18 600,00 0,9 50% сентябрь 2016 Трегубенков И.А. 29 160,00 1,0 50% сентябрь 2016 Солдатова А.К. 26 040,00 0,5 80% сентябрь 2016 Семенова И.Н. 17 040,00 1,0 100% сентябрь 2016 Новиков Е.В. 22 320,00 0,9 80% октябрь 2016 Трегубенков И.А. 29 160,00 1,25 75% октябрь 2016 Солдатова А.К. 26 040,00 0,8 80% октябрь 2016 Семенова И.Н. 17 040,00 1,0 75% октябрь 2016 Корнеев А.В. 11 040,00 0,5 50% октябрь 2016 Новиков Е.В. 22 320,00 0,5 70% ноябрь 2016 Трегубенков И.А. 29 160,00 1,25 75% ноябрь 2016 Солдатова А.К. 26 040,00 0,8 50% ноябрь 2016 Семенова И.Н. 19 240,00 1,0 90% ноябрь 2016 Корнеев А.В. 11 040,00 0,5 50% ноябрь 2016 Новиков Е.В. 25 440,00 0,2 100% Добавьте в конец таблицы строки с заработной платой за декабрь: сотрудники и ставки те же, что и в ноябре, всем сотрудникам повысить оклад на 5%, премия у всех 100%. Под таблицей добавьте строку с итоговыми суммами по начисленной заработной плате, налогу и заработной плате к выплате. Отдельно найдите среднюю заработную плату, минимальный оклад и максимальную ставку. Задание 3. Найдите в Интернете текущий курс доллара, евро, фунта и японской йены. Переведите цены из долларов в рубли, а из рублей в остальные валюты и вычислите стоимость покупки в каждой валюте. Оформите ячейки соответствующими числовыми форматами.       Доллар Евро Фунт Йена           Курс валюты
Наименование	Количе­ство	Цена					Стоимость
		Руб.	$	€	£	¥	Руб.	$	€	£	¥
Телевизор	1		1100
Проектор	3		755
Мышь компьютерная	12		7,5
Клавиатура	4		12,3
Флеш-карта	25		3,1
Колонки	4		9,89
Принтер лазерный	2		273,8
Пачка бумаги	10		1,75
Картридж для принтера	1		34

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

Задание 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, то результат мгновенно пересчитается:

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

Функции

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

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

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

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

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

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

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

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

Адреса ячеек

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Продажи товаров

Наименование

Цена

Количество

Ед. изм.

Стоимость

Хлеб

21,50 ₽

1

шт.

Молоко

64,80 ₽

2

уп.

Масло

88,50 ₽

2

уп.

Сыр

512,00 ₽

0,254

кг

Сахар

45,20 ₽

5

уп.

Печенье

55,90 ₽

3

уп.

Апельсины

90,00 ₽

1,456

кг

Картофель

26,30 ₽

2,654

кг

Творог

98,70 ₽

2

уп.

Салфетки

22,20 ₽

1

уп.

Сок

75,60 ₽

6

уп.

Лимон

11,00 ₽

2

шт.

Вода столовая

  7,50 ₽

5

л

Обратите внимание, цене назначен денежный формат, и значок рубля отображается прямо в ячейке. Однако единицы измерения количества товара вынесены в отдельный столбец. Если написать в одну ячейку число и текст, то 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. Как получить справку по ошибке?

Дата добавления: 2018-10-18; Мы поможем в написании ваших работ!; просмотров: 1342 | Нарушение авторских прав

Лучшие изречения:

Начинать всегда стоит с того, что сеет сомнения. © Борис Стругацкий
==> читать все изречения...

1527 - | 1322 -