Практическая
работа № 20
Выполнение
расчетов в Excel
Цель:
научиться
выполнять расчеты в Excel
Сведения
из теории
Все
вычисления в программе MS Excel выполняются
с помощью формул. Ввод
формулы
всегда начинается со знака равенства
“=”,
затем располагают вычисляемые элементы,
между которыми стоят знаки выполняемых
операций:
+
сложение /
деление *
умножение
− вычитание ^
возведение в степень
В
формулах в качестве операндов часто
используют не числа, а адреса ячеек, в
которых хранятся эти числа. В этом случае
при изменении числа в ячейке, которая
используется в формуле, результат
пересчитается автоматически
Примеры
записи формул:
=(А5+В5)/2
=С6^2*
(D6-D7)
В
формулах используют относительные,
абсолютные и смешанные ссылки
на адреса ячеек. Относительные ссылки
при копировании формулы изменяются.
При копировании формулы знак $ замораживает:
номер строки (А$2 — смешанная ссылка),
номер столбца ($F25- смешанная ссылка) или
то и другое ($A$2- абсолютная ссылка).
Формулы
динамичны и результаты автоматически
изменяются, когда меняются числа в
выделенных ячейках.
Если
в формуле несколько операций, то
необходимо учитывать порядок выполнения
математических действий.
Автозаполнение
ячеек формулами:
-
ввести
формулу в первую ячейку и нажать Enter; -
выделить
ячейку с формулой; -
установить
указатель мыши в правый нижний угол
ячейки и протянуть в нужном направлении
(автозаполнение ячеек формулами можно
делать вниз, вверх, вправо, влево).
Использование
функций:
-
установить
курсор в ячейку, где будет осуществляться
вычисление; -
раскрыть
список команд кнопки Сумма (рис. 20.1) и
выбрать нужную функцию. При выборе
Другие
функции
вызывается Мастер
функций.
Рисунок
20.1 – Выбор функции
Задания
для выполнения
Задание
1 Подсчет котировок курса доллара
-
В
папке с названием своей группы создайте
документ MS Excel, имя задайте по номеру
практической работы. -
Лист
1 назовите по номеру задания; -
Создайте
таблицу подсчета котировок курса
доллара (рис. 20.2), столбец Дата
заполните с помощью автозаполнения,
столбцы Курс
покупки
и Курс
продажи
отформатируйте как числа с двумя знаками
после запятой.
Рисунок
20.2 – Образец таблицы (Лист 1)
-
Выполните
расчет дохода в ячейке D4.
Формула для расчета:
Доход
= Курс продажи – Курс покупки -
Сделайте
автозаполнение ячеек D5:D23
формулами. -
В
столбце «Доход» задайте Денежный
(р)
формат чисел; -
Отформатируйте
таблицу. -
Сохраните
книгу.
Задание
2 Расчет суммарной выручки
-
В
этом же документе перейдите на Лист
2. Назовите
его по номеру задания. -
Создайте
таблицу расчета суммарной выручки по
образцу, выполните расчеты (рис. 20.3). -
В
диапазоне B3:E9 задайте денежный формат
с двумя знаками после запятой.
Формулы
для расчета:
Всего
за день = Отделение 1 + Отделение 2 +
Отделение 3;
Итого
за неделю = Сумма значений по каждому
столбцу
-
Отформатируйте
таблицу. Сохраните книгу.
Рисунок
20.3 – Образец таблицы (Лист 2)
Задание
3 Расчет надбавки
-
В
этом же документе перейдите на Лист
3. Назовите
его по номеру задания. -
Создайте
таблицу расчета надбавки по образцу
(рис. 20.4). Колонке Процент
надбавки задайте
формат Процентный,
самостоятельно задайте верные форматы
данных для колонок
Сумма зарплаты и
Сумма надбавки.
Рисунок
20.4 — Образец таблицы (Лист 3)
-
Выполните
расчеты. -
Формула
для расчета -
Сумма
надбавки = Процент надбавки * Сумма
зарплаты -
После
колонки Сумма
надбавки добавьте еще одну колонку
Итого.
Выведите в ней итоговую сумму зарплаты
с надбавкой. -
Введите:
в ячейку Е10 Максимальная
сумма надбавки,
в ячейку Е11 − Минимальная
сумма надбавки.
В ячейку Е12
− Средняя
сумма надбавки. -
В
ячейки F10:F12
введите соответствующие формулы для
вычисления. -
Сохраните
книгу.
Задание
4 Использование в формулах абсолютных
и относительных адресов
-
В
этом же документе перейдите на Лист
4.
Назовите его по номеру задания. -
Введите
таблицу по образцу (рис. 20.5), задайте
шрифт Bodoni
MT, размер
12, остальные настройки форматирования
− согласно образцу. Ячейкам С4:С9 задайте
формат Денежный, обозначение «р.»,
ячейкам D4:D9
задайте формат Денежный, обозначение
«$».
Рисунок
20.5 — Образец таблицы (Лист 4)
-
Вычислите
Значения ячеек, помеченных знаком “?” -
Сохраните
книгу.
Контрольные
вопросы
-
Как
осуществляется ввод формул в Excel? -
Какие
операции можно использовать в формулах,
как они обозначаются? -
Как
сделать автозаполнение ячеек формулой? -
Как
вызвать Мастер функций? -
Какие
функции вы использовали в этой
практической работе? -
Расскажите,
как вы вычисляли значения Максимальной,
Минимальной и
Средней
сумм надбавки. -
Как
формируется адрес ячейки? -
Какие
ссылки на адреса называют относительными,
какие – абсолютными, какие – смешанными.
Приведите примеры. -
Где
в работе вы использовали абсолютные
адреса, а где – относительные? -
Какие
форматы данных вы использовали в
таблицах этой работы?
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
Для того, чтобы сделать наценку на товар, нужно воспользоваться следующей формулой:
Цена с наценкой = Исходная цена + Исходная цена * Процент наценки.
Например, товар был приобретён за 1500 рублей, а процент наценки равен 10.
Окончательная цена = 1500 + 1500 * 10% = 1500 + 1500 * 0,1 = 1650 рублей.
**
В программе Excel цену товара с наценкой можно посчитать по-разному.
1) Если процент наценки для каждого товара свой, то поступаем так:
-
В один столбец записывается исходная товара.
-
Во второй столбец записывается процент наценки для данного товара.
-
Затем пишем формулу, в которой перемножается исходная цена и соответствующий процент наценки, результат складывается с исходной ценой.
Пример:
Например, для 1 товара формула будет следующей:
=B2+B2*C2
При этом формат ячеек в столбце C нужно сделать процентным — для этого есть специальная кнопка на панели инструментов «Главная».
2) Если процент наценки одинаковый для всех товаров, то его можно записать в отдельной ячейке и сделать ссылку на неё абсолютной.
Также можно написать это значение непосредственно в формуле.
Пример:
Формула для 1 товара выглядит так:
=B2+B2*$C$10
В ячейке C10 записан процент наценки — чтобы сделать на неё абсолютную ссылку нажимаем на F4.
Затем копируем эту формулу в нижестоящие ячейки и получаем цену с наценкой для каждого товара.
Для того, чтобы посчитать наценку в процентах, нужно воспользоваться формулой:
Процент наценки = (Цена с наценкой — Исходная цена) / Исходная цена * 100%.
Например, товар был приобретён за 800 рублей, а продаётся за 1000 рублей.
Процент наценки = (1000 — 800) / 800 * 100% = 0,25 * 100% = 25%.
**
Вот пример реализации этой формулы в Excel:
В столбце B — исходная цена, в столбце C — цена с наценкой.
В столбце D будет процент наценки, формат всех ячеек этого столбца нужно сделать процентным.
Формула в ячейке D2 для 1 товара получится такой:
=(C2-B2)/B2
Так как у ячейки процентный формат, то умножать на 100% не нужно.
Теперь остаётся скопировать эту формулу во все ячейки столбца.
Логические функции в Excel
Наиболее важная логическая функция ЕСЛИ записывается так:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).
В логическом выражении функции ЕСЛИ могут использоваться числа, даты, ссылки на ячейки, а также знаки > (больше), = (не меньше), (не равно). Функция ЕСЛИ выполняется следующим образом.
1. Вычисляется логическое выражение, которое может иметь одно из двух значений: ИСТИНА или ЛОЖЬ.
2. В зависимости от результата вычисления логического выражения функция возвращает один из двух возможных результатов, которые записаны в аргументах значение_если_истина и значение_если_ложь.
Пример1. На предприятии выплачивается надбавка к зарплате за стаж в размере 20 % для работников, имеющих стаж 10 и более лет.
Пример расчетной таблицы приведен на рис. 1, где показан процесс ввода формулы в ячейку D3 с использованием строки формул. В этой формуле используется логическое выражение C3>=10, чтобы определить право работника на надбавку. Если логическое выражение будет иметь значение ИСТИНА, то сумма надбавки рассчитывается по формуле B3*20%, в противном случае результат функции будет равен нулю.
Рис. 1. Пример ввода логической функции ЕСЛИ
Для объединения нескольких условий в одно можно использовать логическую функцию И, которая возвращает значение ИСТИНА, если все входящие условия имеют значение ИСТИНА.
Например, условие «значение ячейки A1 должно быть больше 5 и меньше 10» записывается так: И(A1>5;A1
Последовательность выполнения
1. Создайте таблицу с исходными данными (см. рис. 1).
2. Сделайте активной нужную ячейку (в данном примере D3).
3. Выполните команду Формулы > Библиотека функций > Логические и выберите в списке функцию ЕСЛИ.
4. Введите аргументы функции. Следуя словесной формулировке решения, в поле Лог_выражение введите условие С3
8. Выполните автозаполнение созданной формулой остальных ячеек столбца D и рассчитайте значения для столбца Е. Проверьте правильность работы созданных формул и сохраните таблицу под именем Надбавка за стаж.
Финансовые функции в Excel
Финансовые вычисления включают в себя всю совокупность методов и расчетов, которые используются при принятии управленческих решений — от элементарных арифметических операций до сложных алгоритмов построения многокритериальные моделей, позволяющих получить оптимальные характеристики коммерческих сделок в зависимости от различных условий их проведения.
На данный момент стандартный курс финансовых вычислений включает в себя следующие основные темы:
— Логика финансовых операций (временная ценность денег, операции наращивания и
дисконтирования и т.п.);
— Простые проценты (операции наращивания и дисконтирования, налоги, инфляция,
замена платежей); сложные проценты (эквивалентность ставок, операции с валютой и
— Анализ эффективности инвестиционных проектов;
— Оценка финансовых активов.
Наличие такого широкого круга задач привело к тому, что программы расчета основных финансовых показателей были реализованы на уровне, доступном пользователю, который не обладает глубокими математическими знаниями. В Excel реализовано 15 встроенных и 37 дополнительных финансовых функций. В случае необходимости применения дополнительных финансовых функций необходимо установить надстройку Пакет анализа.
По типу решаемых задач все финансовые функции Excel можно разделить на следующие условные группы:
— Функции для анализа аннуитета и инвестиционных проектов;
— Функции для расчета амортизационных платежей;
— Функции для анализа ценных бумаг;
Функции каждой группы имеют практически одинаковый набор обязательных и дополнительных (необязательных) аргументов. В финансовой практике встречаются операции, которые характеризуются возникновением потоков платежей, которые распределены во времени. Потоки платежей, при которых выплаты денежных средств осуществляются равными суммами через одинаковые промежутки времени, называются обычным аннуитет. Такие потоки возникают при проведении кредитно-депозитных операций, формировании различных фондов, долгосрочной аренды, и т.д.
Количественный анализ таких операций сводится к вычислению следующих основных характеристик:
1) текущей величины потока платежей;
2) будущей величины потока платежей;
3) нормы доходности в виде процентной ставки;
Рассчитать надбавку за стаж с использованием функции ЕСЛИ
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Рассчитать надбавку к зарплате за стаж
Как рассчитать надбавку к зарплате за стаж, если стаж от 2 до 5 лет, надбавка составляет 2%, если.
Рассчитать стаж
у меня такая проблема. значить есть таблица. из ето таблицы я создал запрос Ф.и.о; должность; и.
Рассчитать значение функции y=f(x) при некотором значении «х» с использованием представления функции в виде ряда
Помогите с программами нужны готовые програмные коды. Темы циклы: 2) Рассчитать значение.
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Рассчитать стаж работы по должностям
Здравствуйте! Помогите пожалуйста с решением. Есть база данных(таблица) в Excel, со столбцами.
Рассчитать значение функции с использованием циклов while или do while
Здраствуйте, сейчас прохожу тему с итерационными циклами, надо написать программу, которая.
Рассчитать список тех учителей школы, которые преподают математику и информатику, указать их стаж работы
Рассчитать список тех учителей школы, которые преподают математику и информатику, указать их стаж.
Рассчитать значение функции с использованием оператора if / else и логических операций && и || (При необходимости)
Помогите, — пожалуйста написать программу, которая рассчитывает значение функции с использованием.
Skip to content
Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения.
- Чему равен процент от числа
- Как умножить число на процент
- Как увеличить столбец на процент
- Первоначальное значение до процентного изменения
- Считаем наценку в процентах
- Определяем маржу в процентах
Сколько составляет процент от числа.
Предположим, вы покупаете новый телевизор за 800 долларов, и за доставку с вас просят 4% от стоимости покупки. Вопрос в том, сколько вы должны заплатить сверх первоначальной цены? Другими словами, что составляет 4% от 800 долларов?
Поможет следующая формула:
Число * Процент = Результат
Предполагая, что цена записана в ячейке A2, а процент — в B2, приведенная выше формула превращается в =A2*B2 и возвращает 32.
Помните, что когда вы записываете в таблицу число, за которым следует знак процента (%), то оно интерпретируется Excel как сотая часть его значения (то есть, сразу делится на 100). Например, 4% фактически сохраняется как 0,04, и Excel использует это значение во всех формулах и расчетах.
Другими словами, формула =A2*11% эквивалентна =A2*0.11. Естественно, вы можете использовать десятичное число, соответствующее процентному соотношению, непосредственно в формуле, если это лучше подходит для ваших таблиц.
Как умножить число на процент.
Часто бывает необходимо умножить число какой-то процент. К примеру, речь идет об изменении текущей цены через какой-то промежуток времени (например, через месяц), чтобы добавить к ней определенный процент.
Чтобы увеличить число на определённый процент, используйте эту формулу:
= Число * (1 + %)
или
= Число * (100% + %)
Например, формула =A1*(1+20%) увеличивает значение в ячейке A1 на 20%.
Чтобы уменьшить на процент:
= Число * (1 — %)
Например, формула =A1*(1-20%) уменьшает значение в ячейке A1 на 20%.
В нашем примере, если A2 – действующая цена, а B2 — процент, на который вы ожидаете её увеличение либо уменьшение, то вот формулы, которые вы должны ввести в ячейку C2:
Умножить на процент: =A2*(1+B2)
Уменьшить на процент: =A2*(1-B2)
Кроме того, как вы видите на рисунке выше, процент может быть выражен и отрицательным числом, что означает уменьшение первоначального значения. В этом случае формула =A2*(1+B2) также отлично подходит.
Как увеличить или уменьшить весь столбец на процент.
Предположим, у вас есть столбец чисел, который вы хотите увеличить или уменьшить на определенный процент. Вы также хотите, чтобы обновленные числа были в том же столбце, а не добавлять новый столбец с формулами.
Вот пошаговая инструкция для решения этой задачи:
- Введите все числа, которые вы хотите увеличить или уменьшить, в какой-нибудь столбец (столбец А в этом примере).
- В пустой ячейке введите одну из следующих формул:
Число увеличиваем на процент: =1 + 40%
Число минус процент: =1 — 40%
Естественно, вы можете заменить 40% в приведенных выше формулах любым нужным процентом.
- Выделите ячейку с формулой (в нашем случае C1) и скопируйте ее, нажав
Ctrl + C. - Выберите диапазон ячеек, который нужно изменить, щелкните выделение правой кнопкой мыши и выберите «Специальная вставка…»
- В открывшемся диалоговом окне выберите «Значения» и «Умножить», нажмите «ОК».
И вот результат – все выбранные числа в столбце А умножены на 40%.
Таким же образом вы можете разделить столбец чисел на определенный процент. Просто введите нужное число в пустую ячейку и следуйте инструкциям выше. В Специальной вставке вместо «умножить» выберите «разделить».
Расчет первоначального значения с учетом процента изменения.
Например, вам предложили купить пылесос за 200 долларов, что на 40% ниже первоначальной цены. Вы хотите узнать, какова была первоначальная цена.
Поскольку 40% – это скидка, вы сначала вычитаете ее из 100%, чтобы узнать, какой процент вы действительно должны заплатить (100% — 40% = 60%). Теперь вам нужна формула для расчета исходной цены, то есть для определения числа, 60% которого равняется 200.
Формула выглядит следующим образом:
Число / Процент = Результат
Применительно к скидке от первоначальной цены в 40% можно применять любую из следующих формул:
=A2/(100%-B2) или =A2/(1-B2) или =A2/0.6 или =A2/60%
Как посчитать наценку в процентах.
Предположим, товар стоит 100 рублей. Мы знаем также, что на него была сделана наценка 10 рублей. Как посчитать эту наценку в процентах?
Ответ «10 процентов» будет неверным. В данном случае нельзя просто взять процент от итоговой цены. Ведь наценку мы делаем на первоначальную цену без наценки.
Принимаем итоговую цену за 100%. Тогда первоначальная цена будет составлять на 10% меньше, то есть 90%. В итоге формула расчета процента наценки будет такая:
=наценка / (цена — наценка) * 100%
А теперь запишем это на листе Excel:
Здесь у нас нет необходимости умножать на 100%, а просто применяем к ячейке C2 процентный формат:
=B2/(A2-B2)
Вы можете проверить расчёт: добавьте к 90 рублям 11,1% и получите как раз 100.
Как посчитать маржу в процентах.
Прежде всего, давайте определим разницу между понятиями наценки и маржи.
По абсолютному значению (в денежных единицах) они одинаковы. Рассчитываются они как разность между себестоимостью (или закупочной ценой) и ценой реализации товара.
А вот в процентах они совершенно разные.
Наценка всегда рассчитывается в процентах к себестоимости или цене закупки товара. Размер ее может быть от нуля до бесконечности (хоть 1000% накручивай, если купят).
%наценки = (цена – себестоимость)/себестоимость
Маржа показывает долю дохода в цене. Поэтому она должна быть больше 0, и всегда меньше 100%.
%маржи = (цена – себестоимость)/цена
Воспользуемся примером:
Как видите, уровень процентов наценки и маржи могут очень сильно отличаться.
Но при этом они связаны. Поэтому, к примеру, если мы знаем процент наценки, то легко посчитать маржу.
Если мы продали товара на 1000 рублей и применяли при этом наценку в 30%, то отсюда мы сначала можем найти себестоимость товара:
=сумма продаж / (100%+%наценки)
1000/(100%+30%)=769,23
Считаем маржу:
1000 – 769,23 = 230,77
Маржа в процентах: 230,77/1000 = 23,08%
А теперь как посчитать наценку, если знаем маржу? Используем те же цифры.
Сумма продаж — 1000 руб. Маржа – 230,77 руб.
Отсюда себестоимость 1000 – 230,77 = 769,23 руб.
Процент наценки 230,77/769,23 = 30%
Эти несколько примеров применения процентов, надеюсь, будут полезны. Если у вас есть вопросы – пишите в комментариях.
|
Формула для расчета надбавки за стаж с условиями |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
