0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро подсчитать количество ячеек до изменения значения в Excel?

Как посчитать проценты в Excel

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

Нажмите на кнопку ниже, чтобы скачать файл Excel с примерами вычислений процентов в Excel:

Как посчитать проценты в Excel

Что такое процент?

Сотая часть называется процентом (лат. percent). Она обозначается символом «%». Он обозначает долю чего-либо по сравнению с целым. 25% от 500 рублей, например, равны 25 частям по 5 рублей каждая, или всего 25. 125 рублей.

Формула расчета процента от суммы выглядит следующим образом:

(из/всего) * 100 = Проценты (%).

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

Как посчитать проценты от суммы в Excel

Видео урок:

Следующая формула позволит вам рассчитать процент от суммы в Excel:

(доля/всего) * 100 = процент (%)

Но если использовать формат ячейки «Процентный», то для вычисления процента от числа достаточно поделить одно число на другое. Например, у нас есть яблоки, которые мы купили по 100 руб. на оптовом складе, а розничную цену выставим 150 руб. Для того чтобы высчитать процент того, сколько составляет закупочная цена от розничной, нам потребуется:

  • Составьте таблицу с ценами и добавьте колонку для вычисления суммы процентов:

расчет-цены-закупки-от-цены-продажи

  • В ячейке D2 создайте формулу, которая рассчитывает процент цены покупки от цены продажи:
  • Применить формат ячейки D2 «Процентный»:

Процент от суммы в Excel

Как посчитать процент от суммы значений таблицы Excel

Видеоурок:

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

Как посчитать проценты в Excel

Для этого нам необходимы:

  • Добавьте в таблицу столбец для расчета процентов;
  • Вставьте формулу в ячейку С2:

Расчет процента от суммы таблицы в Excel

Значки $ фиксируют колонку «B» и ячейку «9» для того, чтобы при протягивании формулы на все строки таблицы, Excel автоматически подставлял объем продаж каждого продавца и высчитывал % от общего объема продаж. Если не поставить значки «$», то при протягивании формулы, система будет смещать ячейку B9 на столько ячеек вниз, на сколько вы протяните формулу.

  • Распространите формулу на все ячейки таблицы, соответствующие строкам, содержащим фамилии продавцов:

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

  • На примере выше мы протянули формулу и получили значения в виде дробных чисел. Для того чтобы перевести полученные данные в проценты выделите данные левой клавишей мыши и смените формат ячеек на «Процентный»:
Читайте так же:
Используйте формулу или определенную функцию для автоматического заполнения текста на основе цвета заливки ячейки в Excel

Формат ячейки процентный

Как вычислить процент нескольких значений от суммы таблицы

Видеоурок:

В приведенном выше примере у нас был список продавцов и их объем продаж. Мы рассчитали долю каждого сотрудника в общем объеме продаж. Но что если у нас есть список повторяющихся продуктов с показателями продаж, и нам нужно рассчитать, сколько определенный продукт составляет в процентах от всех продаж?

Как посчитать проценты в Excel

Мы хотим определить долю томатов в продажах из приведенной выше таблицы (томаты показаны в нескольких строках). Для этого:

  • В правой части таблицы укажите продукт (томаты), для которого мы хотим рассчитать долю продаж:

Как посчитать проценты в Excel

  • Сменим формат ячейки E2 на «Процентный»;
  • В ячейку E2 вставим формулу с функцией СУММЕСЛИ, которая поможет вычислить из списка товаров Помидоры и суммировать их объем продаж, а затем поделить его на общий объем продаж товаров:
Как вычислить процент нескольких значений от суммы таблицы
Как работает эта формула?

Для расчетов мы используем формулу СУММЕСЛИ. Эта функция возвращает сумму чисел, заданных в качестве аргументов и удовлетворяющих критериям, заданным в формуле.

Синтаксис функции СУММЕСЛИ :

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

  • Диапазон — диапазон ячеек, для которых оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
  • Условие — критерий, проверяемый на указанном диапазоне ячеек, который определяет, какие ячейки суммировать;
  • Диапазон сумм — ячейки, которые нужно суммировать. Если этот аргумент не указан, функция использует аргумент range в качестве range_total.

Таким образом, в формуле =СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9 мы указали « $A$2:$A$8 » как диапазон товаров, среди которых функция будет искать нужный нам критерий (Помидоры). Ячейка « $E$1 » указана в качестве критерия и указывает что мы ищем «Помидоры». Диапазон ячеек « $B$2:$B$8 » обозначает какие ячейки нужно суммировать, в случае если искомый критерий был найден.

Как посчитать разницу в процентах в Excel

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

Формула используется, например, для вычисления процентной разницы между двумя числами А и В:

( B-A)/A = Процентная разница между двумя числами

На практике, при использовании этой формулы важно определить какое из нужных вам чисел является «А», а какое «В». Например, представим что вчера у вас было в наличии 8 яблок, а сегодня стало 10 яблок. Таким образом количество яблок относительно вчерашнего у вас изменилось на 25% в большую сторону. В том случае, если у вас вчера было 10 яблок, а сегодня стало 8 яблок, то количество яблок, относительно вчерашнего дня сократилось на 20%.

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

Как найти процент между двумя числами из двух колонок в Excel

Видеоурок:

Для товаров у нас есть цены предыдущего месяца и цены текущего месяца. Рассчитать изменение цен по сравнению с предыдущими периодами — наша задача.

Читайте так же:
Как вернуть день недели из даты в Excel?

Для расчетов нам понадобится формула:

Как посчитать проценты в Excel

  • Протяните формулу на все строки со значениями;
  • Измените формат ячейки для колонки «Изменение, %» на «Процентный».

Как найти процент между двумя числами из двух строк в Excel

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

Где B2 это первая строчка, B3 вторая строчка с данными. После ввода формулы не забудьте отформатировать ячейки с вычислениями как «Процентные». Выполнив все выше описанные действия, вы получите результат:

Как посчитать проценты в Excel

Если у вас есть необходимость вычислить изменения относительно какой-то конкретной ячейки, то важно зафиксировать ее значками «$». Например, если перед нами стоит задача вычислить изменения объема продаж относительно Января, то формула будет такой:

Как посчитать проценты в Excel

В приведенном примере показатели продаж за каждый месяц сравниваются с показателями продаж за январь.

Как прибавить/вычесть процент к числу в Excel

При расчете вам может понадобиться добавить процент к числу. Возьмем пример планирования расходов на отпуск. Чтобы рассчитать необходимую сумму денег на неделю отпуска, предположим, что в отпуске мы потратим на 30% больше, чем в обычную рабочую неделю. Вот формула, необходимая для расчета:

Например, мы хотим прибавить к числу «100» двадцать процентов, тогда формула будет следующая:

Если задача стоит вычесть 20% от числа «100», то формула выглядит так:

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

Как прибавить процент к числу в Excel

Еще больше полезных приемов в работе с функциями и списками данных в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

Как в Excel посчитать количество ячеек по цвету ячейки или цвету текста

Как подробно обсуждалось в видеоуроке, мы уже рассмотрели, как подсчитать количество ячеек/значений в Excel. Цель сегодняшней статьи — немного сузить рамки этой статьи. Если вам нужно подсчитать количество ячеек в зависимости от цвета ячеек или цвета текста, что вы будете делать?

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

Читайте так же:
Как в Excel транспонировать и объединять ячейки в один столбец с разделителем?

Для этих целей необходимо использовать очень простой макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам извлечь числовой код цвета заливки и затем по этому коду мы будем считать общее количество ячеек, используя приемы, описанные в статье как считать в Excel ячейки / значения

Итак, давайте начнем. Это можно сделать, перейдя в редактор Visual Basic:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

сделать пользовательскую функцию

В Excel 2007, 2010 и 2013 это делается по-разному. Перейдите в раздел «Разработчик» и выберите Visual Basic

Предупреждение. Excel 2007 автоматически включает панель инструментов разработчика, но в Excel 2010 и 2013 она должна быть включена. Функция макросов особенно полезна для пользователей, которые часто работают с макросами. Чтобы включить панель инструментов разработчика в Excel 2010 или 2013, необходимо выполнить команду Файл | Параметры | Настройка ленты, а затем установить флажок Разработчик в правой части окна

включение редактора Visual Basic

После того как откроется редактор Visual Basic, вставьте пустой модуль, выбрав Insert и затем Module

вставить модуль

И скопируйте в него текст простой функции:

Public Function ColorNom(Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

После этого закройте редактор VisualBasic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =ColorNom( A1 ), где A1 — это наша ячейка, в которой нам необходимо определить индекс цвета.

Пользовательская функция индекс цвета ячейки

После этого, в соответствии с цветом ячейки, можно легко подсчитать количество ячеек или значений. Воспользуйтесь нашей статьей о том, как подсчитать количество ячеек/значений в Excel

Если вам нужно подсчитать количество значений или сумму на основе цвета текста, вам нужно немного изменить код пользовательской функции.

Public Function ColorNom(Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

Читайте так же:
Как быстро разбить ячейку в таблицу в Excel?

=ColorNom(A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

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

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

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

Посчитать кол-во по цвету ячеек

Вычислим количество с помощью функции CALCULATE.

Вот как выглядят аргументы этой функции

=СЧЁТЕСЛИ( диапазон ; критерий )

=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )

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

Как подсчитать количество строк в массиве ТОЛЬКО ЕСЛИ сумма чисел для каждой строки> 0 в Excel?

В Excel у меня есть массив со 150 столбцами x 360 строками случайных чисел (например, от A2 до ET361).

Как подсчитать для каждого столбца (например, от ячейки B1 до ET1), сколько строк больше нуля в столбцах перед ним?

B1 необходимо рассчитать количество ячеек (от A2 до A361), которые> 0.
C1 должен вычислить количество строк (A2:B2, A3:B3, . A361:B361), где сумма каждой строки> 0.
D1 должен вычислить количество строк (A2:C3, . A361:C361), где сумма каждой строки> 0.

Я попытался использовать формулу COUNTIF, но она возвращает только количество ячеек, а не количество строк.
Я думаю, что мне нужны вложенные формулы ROWS() и IF()? Я также не хочу создавать еще одну матрицу 150 x 360 для решения этой проблемы, поскольку хочу сэкономить место в своем файле Excel.

Моя электронная таблица слишком сложна, если я использую макросы и VBA».

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

Мне нужно вычислить для каждой ячейки матрицы количество строк над ней, для которых сумма столбцов в каждой строке больше нуля. Решение Барри не будет работать в этом случае (я проверил), потому что формула «промежуточный итог» не работает для ячеек, в которых есть формула «промежуточный итог».

Есть ли другие альтернативы?

3 ответа 3

Хотя мне не удалось найти решение с помощью одной формулы (возможно, есть другая! ), я нашел то, что занимает гораздо меньше места в электронной таблице, чем еще одна матрица 150 x 360.

Чтобы получить счетчики для всех столбцов, необходимо рассчитать кумулятивные итоги для каждой строки для одного столбца данных, а затем использовать их в таблице данных («анализ «что если бы»).

Читайте так же:
Как в Excel объединить текст из разных ячеек в одну?

Отправной точкой является столбец вычислений для строк одного столбца данных.

На скриншоте ниже показано, как я отформатировал рабочий лист с 10 колонками данных.

Вспомогательная колонка

Я помещаю дополнительный столбец L справа от данных.

В ячейке L1 содержится СЧЕТ строк столбца, сумма которых больше нуля.

Для итогов по строкам вместо простой суммы по столбцам в каждой строке (опять же, только для столбца A) я использую интервальную сумму, возвращаемую функцией OFFSET. Эта функция имеет вид

Ячейка L3 содержит первое из выражений SUM(OFFSET(. )). Он вычисляет сумму строк для области, которая находится на 0 строк вниз от ячейки A2 и на 0 столбцов вправо, с высотой в 1 строку и шириной, равной значению в ячейке L2. В этом случае ячейка L2 имеет значение 1.

Эта формула копируется на 360 строк, в каждом случае рассчитывая сумму высоты строки в диапазоне 1 и ширины, определенной значением в ячейке L2.

Другими словами, если L2 изменить на 2, формулы в этом столбце будут вычислять суммы для каждой из 360 строк значений в столбцах A и B. Кроме того, в ячейке L1 будет отображаться количество строк в диапазоне от A2 до B361, сумма которых превышает 0.

Вычисление количества строк для одного столбца данных

База данных

Функциональность электронных таблиц Excel позволяет быстро определить влияние на расчеты изменения значения одного (или двух) входных данных для расчета. Он настраивается с помощью кнопки «What-If Analysis» на вкладке «Data Tools» ленты.

На прилагаемой иллюстрации показана конфигурация таблицы данных.

Будет создана таблица данных в диапазоне R1:S10. В верхней части таблицы, в ячейке S1, находится ячейка результата, для которой будут изменены входные данные. В этом случае ячейка результата содержит формулу =L1 , которая является просто ссылкой на формулу COUNTIF в верхней части вспомогательного столбца L.

Ранее я ввел значения «что-если» в ячейки R2:R10. Показанные значения — 1, 2, . 9 — представляют собой ширину диапазонов, которые вернет OFFSET. А «ячейка ввода столбца» — это ячейка L1 , ячейка, определяющая ширину строк, которые суммируются в колонке помощи.

Для простоты мы вводим ширину от 1 до 9 (эквивалентно столбцам «A», «A:B», «A:C» и т.д.), и таблица данных подсчитывает количество строк, сумма которых больше 0 для каждого из этих промежутков между столбцами.

Настройка таблицы данных

На последнем изображении показаны окончательные результаты. Таблица данных подсчитывала количество строк в каждом столбце входных данных, т.е. количество сумм строк (предыдущих столбцов), которые больше 0. Эти подсчеты возвращались в ячейки S2:S10 таблицы данных. Я перенес подсчеты в первую строку входных данных с помощью функции TRANSPOSE.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector