Загрузка...Дополнительные вычисления в сводных таблицах
Дополнительные вычисления в сводных таблицах
Интересный факт: часто встречаю пользователей, которые хорошо владеют инструментом сводных таблиц, но при этом не знают о такой их возможности, как дополнительные вычисления в сводных таблицах. Такие вычисления доступны в Excel 2010–2016, а в Excel 2007 дополнительные вычисления «спрятаны» в параметрах поля и их гораздо меньше.
Например, у нас есть простая таблица Excel по продажам вот с такими данными:
Предположим, нам нужно построить несколько отчетов:
- Процентная структура продаж.
- Продажи нарастающим итогом.
- Продажи с темпами роста.
Разберем, как создать такие отчеты с помощью дополнительных вычислений в сводных таблицах.
1. Процентная структура продаж
Чтобы с помощью сводных таблиц определить процентную структуру продаж, нужно сделать несколько простых действий.
Шаг 1. Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм — Доходы (если вы не знаете, как создать сводную таблицу, посмотрите статью «Как построить сводную таблицу в Excel»).
Шаг 2. Щелкаем правой кнопкой мыши по любому числу в сводной таблице и выбираем раздел:
Дополнительные вычисления → % от общей суммы. В появившемся меню доступно несколько способов вычисления процентов:
а) % от общей суммы – рассчитывается к итоговой сумме, от «угла».
Если переместить данные по Городам в область строк, а Товары в столбцы, мы увидим, что общий процент считается как по строкам, так и по колонкам, и сумма процентов равна 100%.
б) % от суммы по столбцу или по строке.
Если требуется рассчитать структуру продаж, например, только по Городам, выбираем % от суммы по столбцу. Если только по товарам, соответственно – по строке.
в) А если нужно видеть структуру продаж и по товарам, и по городам? Не проблема! Нужно выбрать % от суммы по родительской строке.
Тогда процент рассчитается от суммы группы, а не от общего итога. А сумма процентов внутри группы будет равна 100%.
Шаг 3. Все, конечно замечательно, НО хотелось бы рядом с процентами видеть суммы. И это тоже не проблема! Открою маленький секрет: в область значений сводной таблицы мы можем несколько раз перетащить один и тот столбец. Для этого просто захватываем мышкой нужное поле и несколько раз перетаскиваем его в область сумм.
В сводной таблице появится несколько одинаковых столбцов значений, к которым можно применить разные дополнительные вычисления.
2. Продажи нарастающим итогом
В сводной таблице можно показать суммы доходов нарастающим итогом по месяцам. Это делается также с помощью инструмента дополнительных вычислений.
Шаг 1. Постройте сводную таблицу. В строки поместите Города, в столбцы — Месяцы.
Шаг 2. Правой кнопкой мыши по любому числу, выберите Дополнительные вычисления → С нарастающим итогом в поле.
Шаг 3. В открывшемся окне выбираем, что нарастание нужно по Месяцам и все готово!
Можно выбрать, относительно какого поля будет идти нарастание – строк и столбцов, городов или месяцев. В нашем случае выбран вариант нарастающего итога по месяцам. Кстати, столбец Общий итог пустой, потому что нарастающий итог рассчитан в декабре.
3. Темпы роста
Настроим отчет, в котором будут темпы роста, рассчитанные в сводной таблице.
Шаг 1. В новую сводную таблицу добавляем в строки Города, в столбцы Месяцы. В область значений – два одинаковых столбца Доходы.
Когда в области Значений появляется более двух полей, в столбцах появляется «виртуальное» поле «∑ Значения», которое определяет размещение данных в сводной таблице – по строкам или столбцам. Переместите «∑ Значения» в область строк.
Шаг 2. Щелкаем правой кнопкой мышки по числам одного из полей сводной таблицы и выбираем Дополнительные вычисления → Приведенное отличие. Указываем Базовое поле «месяцы», элемент – «назад».
Январь будет пустым, потому что перед ним нет других данных. Это место можно занять спарклайнами. Чтобы их добавить, перейдите в меню Вставка → Спарклайны → График.
Как прибавить НДС 20% к сумме: формула
Налог на добавленную стоимость – это один из ключевых показателей, с которым приходится работать предпринимателям, бухгалтерам, финансистам и сотрудникам налоговой. Вычисления с НДС можно провести и при помощи обычного калькулятора, однако это достаточно трудозатратно. Excel готов облегчить жизнь всем тем, кто регулярно сталкивается с расчётом величины налога. Разберёмся, как прибавить НДС 20% к сумме формулой в Excel.
Рассчитываем из налоговой базы
Для начала определимся, как вычислять размер из налоговой базы. Чтобы выполнить эту операцию, нужно взять облагаемую базу и умножить её на ставку. На данный момент в России ставка составляет 20% (в связи с последними изменениями).
Определяем размер налога
Для определения величины мы используем такие формулы:
- общая: База налогообложения * 0,2.
- Для Excel: ячейка с налоговой базой * 0,2.
На практике Excel применяют для того, чтобы сразу высчитать значения для нескольких показателей. Составим таблицу – в первой будет отражена база, во второй – размер НДС, в последней – величина совместно с НДС. Чтобы использовать формулу, проводим следующую последовательность действий:
- Ставим курсор в первую пустую ячейку с размером налога, а затем пишем знак «=».
- Выделяем первую область, в которой содержатся данные для расчёта, ставим знак «*» после появившегося адреса, пишем значение 0,2.
- Нажимаем [knopka]Enter[/knopka], а затем берём значение за угол и растягиваем на всю таблицу.
Если таблица размещена в левом верхнем углу, то формула примет следующий вид: =A2*0,2 (можно увидеть в строке состояния).
Чтобы выставить денежный формат (начнёт учитывать копейки), нужно выделить всю область, нажать на ней правой кнопкой мыши, перейти в «Формат ячеек» и установить параметр «Денежный».
Прибавляем 20% к сумме
Показатель рассчитан, но этого недостаточно. Нужно прибавить полученный итог к тому, который был изначально. Последовательность действий в данном случае будет практически аналогичной:
- Устанавливаем курсор в первую ячейку столбца «Сумма с НДС» и прописываем «=».
- Кликаем по А2 с базой налогообложения и ставим после адреса знак «+».
- Кликаем по В2 (в которой содержится высчитанная по формуле величина) и применяем комбинацию.
- Берём С3 за угол и переносим на весь оставшийся столбец для того, чтобы формула автоматически применилась на другие.
В нашем случае она выглядит так: =А2+В2.
Показатели рассчитаны, размеры таблицы можно увеличивать до необходимых.
Рассчитываем от итогового размера
Часто пользователи сталкиваются с ситуациями, когда величина дана уже с прибавленным налоговым показателем, и нужно определить его размер (извлечь из итогового значения).
Определяем величину налога
Сначала нужно вывести размер из итогового числа.
В подобной ситуации нужно применять следующие формулы:
- общая: Итог с НДС / 120% * 20%;
- для Excel: ячейка с суммой / 1,2 * 0,2.
Чтобы было проще, таблицу менять мы не будем. Лишь возьмём за условие, что изначально заполнен только раздел «Сумма с НДС» — остальные пусты:
- Устанавливаем указатель в первую область второго столбца и вводим формулу «=С2/1,2*0,2».
- Нажимаем на [knopka]Enter[/knopka].
- Растягиваем на весь столбец.
Высчитываем базу
Всё, что осталось сделать – это определить сумму, которая была обложена НДС. Нужно будет применить вычитание:
- Кликаем по началу пункта «База налогообложения».
- Пишем «=», кликаем по первой области третьего столбца с итогом, пишем «-» и жмём на ячейку второго столбца (выделенный объём налога).
- Закрепляем формулу, растягиваем результат на весь столбец.
Определяем размер с НДС по базе
Если отдельный показатель величины налогового платежа не нужен, можно сразу прибавить НДС 20% и получить итог. Таблица будет иметь два столбца – «База» и «Величина с НДС».
- общая: База + База * 20% либо База * 120%
- Excel: ячейка с базой * 120%.
Для расчёта нужно:
- Поставить курсор на искомое значение.
- Вписать «=», нажать на ячейку с данными, прописать «*1,2».
- Принять формулу, растянуть столбец.
Итоговая формула такова: =А2*1,2.
Видеоинструкция
Ознакомившись с видеоинструкцией, вы быстрее сможете настроить Excel под расчёт и прибавление объёма НДС.
Заключение
Как видно, Excel позволяет автоматизировать операции расчёта и сэкономить огромное количество времени. Буквально в течение нескольких минут можно составить простейшую таблицу с формулами и модернизировать её под свои нужды, а затем использовать для любых значений.
Формула для расчета НДС в Excel
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Перед тем как посчитать НДС в Excel, вспомним что это такое и каким вообще данный налог бывает.
Как следует из названия, налог на добавленную стоимость — это косвенный налог, начисляемый на прибавочную (добавленную) стоимость товара (или услуги) в соответствии с утвержденными налоговыми ставками (согласно налоговому кодексу РФ).
В России налоговые ставки для расчета НДС бывают 3 видов в зависимости от типа товара: 18% (применяется по умолчанию, стандартная ставка для большинства товаров), 10% и 0% (сниженная ставка для определенных категорий товаров).
С 2019 г. ставку НДС в России с 18% подняли до 20%, учтите это при расчете налога в новых периодах.
Если вам нужно вычислить НДС не для России, а к примеру, для Украины, Белоруссии или Казахстана, то в этих странах действуют следующие налоговые ставки (по другим странам данные можно посмотреть здесь):
- Украина. 20% (стандартная), 0% (сниженная);
- Белоруссия. 20% (стандартная), 10%, 0,5% или 0% (сниженная);
- Казахстан. 12% (стандартная).
В общем и целом, величина ставки в зависимости от страны может меняться, но смысл налоговых платежей остается один и тот же.
Поэтому в последующих примерах мы будем рассматривать расчеты только для российской системы налогообложения, но в случае необходимости формулы легко скорректировать (изменив размер ставки) и для любой другой страны.
Теперь перейдем к рассмотрению различных способов расчета НДС в Excel.
Способ 1. Ручной ввод формул
Давайте подумаем какие вообще есть варианты подсчёта формулы налоговых отчислений.
У нас есть 3 варианта — мы можем либо отдельно рассчитать налог, либо добавить, либо вычесть.
В итоге, после выбора варианта подсчета, полученный результат можно вручную ввести в поле ввода с помощью формулы.
Рассмотрим каждый из трех вариантов более подробно.
Выделение НДС
Предположим, что у нас есть сумма S (без учета налога) и мы хотим выделить для нее величину налога на добавленную стоимость.
Для выделения налога нам нужно эту сумму умножить на налоговую ставку, т.е. S * 18%:
Рассмотрим еще один вариант, пусть в сумму S уже включен налог.
В этом случае, так как в начальной величине S содержится 18% ставка, поделив сумму на 118% и умножив на 18% мы получим выделенный налог на добавленную стоимость, т.е. (S / 118%) * 18%:
Прибавление НДС
Начальные условия те же самые, для суммы S рассчитаем полную сумму с учетом налога. Чтобы прибавить НДС необходимо к сумме (100%) добавить выделенный налог (18%), другими словами умножить ее на 118%, т.е. S * 118%:
Вычитание НДС
Начальные условия — нам известна сумма S, но с тем условием, что в ней уже учтен налог.
Так как итоговая сумма составляет 118% (как в примере выше), то чтобы вычесть НДС и получить сумму без учета налога, нам необходимо разделить ее на 118%, т.е. S / 118%:
Как видим у нас есть 3 варианта формулы расчета, в зависимости от того, что именно и как нужно посчитать.
Способ 2. Пользовательская функция
Поэтому в качестве ещё одного подхода к подсчету формулы, напишем пользовательскую функцию, которая объединяла бы все варианты расчета и автоматизировала расчеты, чтобы уменьшить вероятность ошибки, к примеру, при наборе формулы.
Как мы уже выяснили, расчет зависит от 3 параметров: исходной суммы (для которой будем производить расчет), размера ставки (18%, 10% или 0%) и типа расчета (выделение, вычитание или прибавление).
Поэтому в качестве аргументов функции и зададим указанные параметры.
Переходим в окно редактора Visual Basic (сочетание клавиш Alt + F11), добавляем новый модуль (через меню Insert -> Module) и затем копируем туда код нашей функции:
Все способы расчета процента в Microsoft Excel – формулы и примеры использования
Рассчитывать проценты и выполнять действия с ними удобнее всего в программе Excel в пакете Microsoft Office, поскольку все, что требуется – это указать значения и нужную формулу.
Процент – это сотая часть целого числа, которая на бумаге обозначается знаком % или десятичными дробями (70% = 0,70). Стандартное выражение для расчетов процента – Целое/Часть*100, но благодаря Excel вручную ничего считать не придется.
Как посчитать проценты в Excel
Простота работы с программой заключается в том, что пользователю достаточно ввести значения целого и его части (или выбрать из введённых ранее данных), и указать принцип вычисления, а расчеты Excel произведет самостоятельно. В Excel процент вычисляется так — Часть/Целое = Процент, а умножение на 100 происходит автоматически, когда пользователь выбирает процентный формат:
- кликнуть на ячейке правой кнопкой мыши – Формат ячеек – Процентный;
- или Верхнее меню – вкладка Главная – Число – выбрать из выпадающего меню нужный формат.
Для проведения вычислений возьмем расчет выполнения рабочего плана:
- В таблице три столбика — с продуктом, планом выполнения и фактическим результатом.
- В D2 надо вписать «=C2/B2».
- Заранее следует столбец D перевести в Процентный формат.
- Скопировать выражение по всему столбику используя маркер заполнения (растянуть на весь столбец).
Программа самостоятельно рассчитает процент выполнения плана для каждого продукта.
Процент от числа
В Excel можно вычислить цифру, зная только ее долю: %*Часть = Целое. Допустим, необходимо вычислить, что составляет 7% от 70. Для этого:
- ввести «=7%*70»;
- нажать Enter и получить необходимый результат.
Если расчет идет при работе с таблицей, то вместо ввода чисел надо давать ссылки на нужные клетки. Стоит быть внимательным, при расчете формат должен быть Общим.
Процент от суммы
При работе с данными часто требуется посчитать % от итоговой суммы, при этом может быть два варианта: итог записан в одной ячейке или разбросан по всей таблице. Рассмотрим первый вариант:
- для расчета используется стандартное выражение «Часть/Целое*100», но с одной поправкой – ссылка на нужную ячейку с итоговой суммой должна быть абсолютной – при записи перед ней ставиться знак $;
- итоговое значение записано в ячейке В10. Расчет для столбика С будет следующей – «=B2/$B$10»;
- далее надо растянуть с помощью маркера заполнения выражение на весь столбик, поскольку ссылка была абсолютной ($B$10), то программа рассчитает все продукты самостоятельно.
Если же данные разбросаны по всей таблице, то необходимо использовать формулу СУММЕСЛИ — она складывает значения, которые отвечают заданным параметрам, в примере – заданные продукты. Формула для примера будет выглядеть так – «=СУММЕСЛИ (диапазон критерий; диапазон сложения)/общая сумма»:
- в столбце А написаны названия продуктов – это диапазон значений;
- в столбе В – количество всех продуктов – это диапазон сложения;
- в ячейке Е1 надо вписать критерий – название рассчитываемого фрукта, а итог записан в ячейке В10;
- выражение выглядит так: «=СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10».
Таким образом считается каждый параметр, т.е. продукт.
Расчет изменения в процентах
Сравнение двух долей также возможно с помощью Эксель. Для этого можно просто найти значения и вычесть их (из большего меньшее), а можно воспользоваться формулой прироста/уменьшения. Если надо сравнить числа А и В, то формула выглядит так «(B-A)/A = разница». Рассмотрим пример расчета в Excel:
- В колонке А вписан товар, в колонке В и С его цена за август и сентябрь соответственно.
- Изменение в долях будет рассчитываться в колонке D.
- В ячейке D2 вписать формулу «=(С2/В2)/В2».
- Растянуть формулу на весь столбец с помощью маркера автозаполнения.
Если же расчетные показатели располагаются в одном столбце для конкретного товара за длительный период времени, то метод расчета изменится:
- в столбце В приведены данные за каждый месяц продаж;
- в столбце С надо посчитать изменения. Формула будет такой – «=(B3-B2)/B2»;
- если числа надо сравнить с конкретным показателем, то ссылку на нужную ячейку надо сделать абсолютной. Например, надо сравнить каждый месяц продаж с январем, для этого вписать в расчетную ячейку формулу «=(B3-B2)/$B$2».
Положительные значения указывают на прирост, а отрицательные – на уменьшение.
Расчет значения и общей суммы
Зачастую, необходимо определить итоговую сумму зная только долю. В Excel это можно сделать двумя способами. Рассмотрим покупку ноутбука предположив, что он стоит 950 долларов. Продавец говорит, что это цена без учета налога НДС, который составляет 11%. Итоговую наценку можно узнать, сделав расчеты в Эксель:
- необходимая формула «Общая сумма * % = Значение»;
- в клетке С2 вписать «=A2*B2»;
- итоговая наценка будет составлять 104,50 долларов.
Второй способ расчета рассмотрим на другом примере. Допустим, при покупке ноутбука за 400 долларов продавец говорит, что цена посчитана с учетом 30% скидки. Узнать начальную цену можно так:
- Скидка в 30% — это снижение цены, поэтому надо узнать какую часть оплачивает покупатель: 100%-30% = 70%.
- Чтобы найти цену 70% от которой равны $400 надо использовать выражение «Часть/% = Общая сумма».
- В таблице в столбике А указано значение, в столбике В – процент, а в ячейке С2 — итоговая сумма.
- В клетку С2 вписать выражение «=А2/В2».
Начальная стоимость составит 571,43 долларов.
Как изменить значение на величину процента
Часто приходиться увеличивать или уменьшать итоговое число на какую-то его долю, например, необходимо увеличить ежемесячные затраты на 20%, 30% и 35%:
- в столбике А указаны ежемесячные затраты, в столбике В – доля увеличения, а в столбике С – итоговая сумма;
- выражение для расчета такое – «=Затраты*(1+%)». С ссылками на клетки – «=A1*(1+20%)».
Программа посчитает итог самостоятельно по всему столбику, если протянуть выражение с помощью маркера заполнения. Выражение для уменьшения суммы такое же, только со знаком минус – «=Значение*(1-%)».
Операции с процентами
С долями можно совершать такие же операции, как и с обычными числами: сложение, вычитание, умножение. Например, в Excel можно посчитать разницу в показателях между продажами компании с помощью команды AВS, которая возвращает абсолютную величину числа:
- в столбике А – итог продаж за 2011 год, а в столбике В – за 2012 год. Необходимо найти в клетке В3 разницу между этими двумя числами;
- изменить формат клетки В3 на Процентный;
- вписать в клетку «=(В2/А2)/AВS(А2)», т.е. разделить продажи второго года на продажи первого и вернуть абсолютное значение последнего показателя;
Разница между показателя составит 23 %.
Можно также прибавить (или вычесть) процент к числу — рассмотрим действие на примере планирования отпуска:
- Допустим, что неделя отпуска будет больше в расходах на 30%, чем обычная неделя, а неделя после отпуска меньше привычных на 30%.
- Выражение будет выглядеть так «= Еженедельные расходы * (Еженедельные расходы + %)».
- В клетке В2 указаны обычные еженедельные расходы, в В3 – отпуск, а в В4 – после отпуска.
- В клетке В3 вписать «=В2+(В2*30%)», а в клетке В4 «=В2-(В2*30%)».
Программа самостоятельно произведет вычисления и результаты будут 26000 рублей для отпускной недели и 14000 рублей после отпуска соответственно.
Умножение числа на долю осуществлять в Эксель намного проще, чем вручную, поскольку достаточно указать необходимое значение и процент, а программа все посчитает сама:
- В таблице указаны продукты и сумма покупки. Надо к сумме прибавить еще 6% надбавки.
- В клетке F3 надо вписать формулу «=Е3*6%».
- Для программы знак % означает, что она должна умножить введенное значение на 0,06 и в клетке F3 появиться результат умножение – 54.
Все суммы можно быстро пересчитать, если растянуть формулу на весь столбец F.
Чтобы вычесть долю, необходимо указать в ячейке А1 цифру, а в клетке А2 – процент. Расчеты произвести в клетке В1, вписав формулу «=А1-(А1*А2)».
Считать и выполнять действия с процентами в программе Excel очень просто, достаточно правильно работать со значениями и математическими формулами.
Проценты в Excel
Говоря об эксель, нельзя не рассказать как вычислять проценты в Excel, т.к. это часто используемая операция и ее автоматическое исполнение просто необходимо для обработки наших данных.
Сегодня мы разберемся как определить, какой процент одно число составляет от другого, как прибавить определенный процент к числу и как его соответственно вычесть, используя возможности эксель.
Это мы будем делать двумя способами и Вы определите, какой из них Вам наиболее удобен.
В прошлых статьях мы рассмотрели эксель для начинающих, познакомившись с самой программой, узнали как проводить формат ячеек, использовать основные формулы эксель, а так же, как объединять ячейки.
Проценты в Excel
— Процент от числа
— Как прибавить процент к числу
— Как вычесть процент из числа
Начнем с самой простой операции, узнаем как вычислить, какой процент составляет одно число от другого.
Возьмем число 1500 и узнаем сколько процентов от него составляет число 300
Для 1 способа воспользуемся школьными знаниями и составим пропорцию:
Соответственно наш Х =300*100/1500
Эту формулу мы и применим в данном случае для этого вычисления , при это все ячейки находятся в общем формате представления чисел
Процент в Excel рис 1
2 способ нахождения процента от числа:
Возьмем те же числа 1500 и 300 и в ячейке, где будет наш результат меняем формат представления на %, при этом там автоматически будет происходить умножение на 100
Нам нужно будет написать формулу = 300/1500
Процент в Excel рис 2
Так же вычисляется и процент от любого числа:
Число 1500 — нужно узнать сколько будет от него 20%
1 способ: Формула будет следующая: 1500*20/100
Процент в Excel рис 3
2 способ: делаем форматирование ячейки, где будут находиться проценты в этом формате и тогда формула будет уже проще: 1500*20
Процент в Excel рис 4
Как прибавить процент к числу
По аналогии будет два способа, только формула немного другая, из двух действий/
Нужно прибавить к числу 1500 например 20% , для этого сначала вычисляем эти 20% от числа, а затем их к нему прибавляем: 1500*20/100 и затем этот результат прибавить к 1500.
Процент в Excel рис 5
Вторым способом, представляем ячейку с процентами в виде % отражения (форматирование — Число -%) и тогда формула имеет следующий вид
Процент в Excel рис 6
Как вычесть процент из числа
по сути измениться только знак с плюса на минус и мы будем иметь следующую формулу:
= =A2-(A2/100*B2) и результат 1200.
Процент в Excel рис 7
Второй способ так же аналогичен предыдущему при сложении, но тоже со знаком минус.
И так, сегодня Вы научились вычислять проценты Excel и теперь всегда сможете этосделать, если что-то забылось, обращайтесь к этой статье.
Подробнее смотрите в видео:
Для тех, кто желает и готов изучить свой компьютер более детально и использовать его для решения других своих задач — мой видео курс «Как стать уверенным пользователем компьютера за 3 недели»
