Oc-windows.ru

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

Сумма произведений в Excel

Сумма произведений в Excel

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

В этой статье я покажу, как рассчитать сумму произведений в Excel различными способами: используя привычные «+» и «*», ссылаясь на определенные ячейки или с помощью функции СУММПРОИЗВ.

Возьмем следующий пример: умножим пары чисел, а затем просуммируем их самым простым способом.

Сумма произведений в Excel

Поставьте в ячейку «=» и начните вписывать значения, расставляя между числами знаки суммы и умножения. Эксель посчитает по законам математики – сначала умножит, а потом прибавит. Чтобы получить результат, жмите «Enter».

Сумма произведений в Excel

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

Сумма произведений в Excel

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

Для этого поставьте знак равно и выделяйте мышкой ячейки, расставляя между их адресами «+» или «*». Потом нажмите «Enter».

Теперь если я изменю в С1 число 24 на 3, результат в D6 будет пересчитан.

Сумма произведений в Excel

Для расчета суммы произведений в Эксель так же есть специальная формула – СУММПРОИЗВ. Ее использование перед предыдущими способами очевидны: автоматический расчет значений при их изменении, возможность задавать определенные условия, работа с большими массивами.

Примеры СУММПРОИЗВ в Excel, я приводила в отдельной статье, там же и рассказывала, как ей пользоваться.

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

Сумма произведений в Excel

Если у Вас таблица с большим количеством строк, то можно сделать и по-другому. В качестве аргументов для функции указывайте все столбцы полностью: (E:E;G:G). Теперь перемножаются 10 и 216, 5 и 114, 8 и 96 и так далее.

В аргументах к СУММПРОИЗВ можно задавать определенные условия. Например, чтобы посчитать, сколько мы заработали на яблоках, привезенных именно с Греции, нужна вот такая формула:

Сумма произведений в Excel

Для того чтобы растянуть формулу СУММПРОИЗВ потяните за привычный правый нижний угол ячейки. После этого обязательно нужно посмотреть, какие диапазоны используются в аргументах.

Сумма произведений в Excel

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

Сумма произведений в Excel

Представьте, что нам нужно перемножить и просуммировать E с G и F с G. Поэтому в J4 исправляем аргумент H на G.

Сумма произведений в Excel

Если Вы растянули формулу не на одну, а скажем 10 ячеек, то исправлять не совсем удобно. Поэтому лучше закрепить тот столбец, который используется в расчете постоянно. В примере это G и на него нужно сделать абсолютную ссылку. В первой ячейке с расчетами, у меня это I4, ставим перед G знак «$», чтобы получилось $G4:$G10. Теперь растягивайте. Из примера видно, что в аргументах столбец E сменился на F, а вот G так и остался.

Сумма произведений в Excel

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

Как посчитать строки в Экселе?

Посчитать количество строк можно с помощью функции «Автосумма» в Excel . Выделяем ячейку, в которой будет стоять цифра количества строк. Нажимаем стрелку у кнопки «Автосумма» на закладке Excel «Главная» и выбираем функцию «Число». Эта функция считает только заполненные строки.

Как в Экселе сделать сумму строк?

Вставить функцию СУММ можно с помощью кнопки «Автосумма», которая находится на вкладке «ГЛАВНАЯ» в группе «Редактирование». Для этого достаточно выбрать ячейку, где нужно автоматически посчитать сумму в строке и нажать на «Автосумму» и на клавишу «Enter».

Как посчитать количество отфильтрованных строк в Excel?

Если вы хотите подсчитать количество видимых элементов в отфильтрованном списке, вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ, которая автоматически игнорирует строки, которые скрыты с помощью фильтра. Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ может выполнять вычисления, как СЧЁТ, СУММ, МАКС, МИН, и многие другие.

Как в Экселе посчитать сумму выделенных цветом ячеек?

В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это диапазон, содержащий раскрашенные ячейки, которые Вы хотите посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красный.

Как посчитать количество строк в файле linux?

Если вы просто хотите узнать количество строк в текстовом файле, вы можете использовать команду wc с опцией ‘l’. В основном, она подсчитывает количество строк в файле.

Читать еще:  Ошибка «Служба аудио не запущена» на Windows 10

Как в Экселе посчитать сумму умножение?

Пример 1: умножаем число на число

  1. Встаем в любую свободную ячейку, затем: ставим в ячейке (или в строке формул) знак “=”; пишем первое число (множимое; ставим знак умножения – “*“; указываем второе число (множитель).
  2. После нажатия клавиши Enter получаем результат в ячейке с формулой.

Как в Excel сделать автоматический подсчет?

Автоматический пересчет формул в Excel и вручную

  1. Введите формулу на чистый лист (чтобы можно было проверить как работает данный пример).
  2. Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».
  3. Убедитесь, что теперь после ввода данных в ячейку (например, число 7 вместо 1 в ячейке A2 как на рисунке), формула не пересчитывает результат автоматически.

Как в Excel посчитать сумму всех ячеек?

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

Как суммировать только видимые ячейки в Excel?

Это нужно учитывать при выполнении вычислений:

  1. СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т. е. …
  2. ПРОМЕЖУТОЧНЫЕ. ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. …
  3. ПРОМЕЖУТОЧНЫЕ. …
  4. АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010.

Как посчитать сумму отфильтрованных ячеек?

Способ 2 – Сумма отфильтрованных значений

  1. Выделите столбцы с данными и выберите на ленте Данные (Data) – Фильтр (Filter). Я рекомендую выделять именно столбцы, т. …
  2. Замените формулу СУММА (SUM) на ПРОМЕЖУТОЧНЫЕ. …
  3. Выберите фильтром нужные названия или категорию, если имеется.

Как выделить только видимые ячейки в Excel?

Копирование только видимых ячеек

  1. Вы выберите ячейки, которые вы хотите скопировать для получения дополнительных сведений. …
  2. Щелкните Главная > Найти и выделить, а затем выберите пункт Выделение группы ячеек.
  3. Выберите параметр только видимые ячейки и нажмите кнопку ОК.
  4. Щелкните Копировать (или нажмите клавиши CTRL+C).

Как посчитать сумму ячеек одного цвета?

Чтобы посчитать ячейки одного цвета достаточно немного видоизменить функцию для подсчета суммы — вместо прибавления значения текущей ячейки (Sum = Sum + cell. Value) мы добавляем 1 (Sum = Sum + 1).

Как узнать код цвета ячейки Эксель?

Функция =ЦВЕТЗАЛИВКИ(ЯЧЕЙКА) возвращает код цвета заливки выбранной ячейки.

ИнтеллигенцияX

Окт 8, 2017 — Microsoft Office Написать комментарий

Ms Excel: как посчитать ячейки с текстом

excel 450x244 Ms Excel: как посчитать ячейки с текстом

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

В том числе, – для подготовки различного рода статистической отчетности.

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

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

Или же для быстрой проверки данных после их импорта в рабочую книгу MS Excel: после такой операции нередко часть числовых значений может быть воспринята программой как текст. Ситуаций много. А вот специальной функции для решения описанной задачи в программе Excel нет.

Но, как известно, безвыходных ситуаций не бывает. Рассмотрим два способа решения проблемы.

И сделаем это на таком примере: посчитать количество ячеек с текстом в диапазоне «A1:A20».

Первый способ.

В Excel есть две полезные функции: «Счёт()» – определяет в заданном блоке количество ячеек с числами, и «СчётЗ()» – служит для подсчета непустых ячеек в блоке. Скомбинировав две эти функции, для решения задачи можно предложить формулу «=СЧЁТЗ(A1:A20)-СЧЁТ(A1:A20)».

excel text count 327x350 Ms Excel: как посчитать ячейки с текстом

Второй способ – воспользоваться формулой массива.

Для этого сделайте так.

1) В свободную ячейку введите выражение «=Сумм(Если(Етекст(A1:A20);1))».
2) Нажмите «Ctrl+Shift+Enter».

В этой формуле «Етекст()» анализирует содержимое ячейки.

Если она содержит текст, результат функции равен «Истина».

Проверку этого значения выполняет функция «Если()».

В случае, когда «Етекст()» равен «Истина», функция «Если()» вернет значение «1».

А функция «Сумм()» просуммирует результат и определит количество ячеек с текстом (т.е. количество «единиц») в указанном диапазоне.

excel text count variant2 321x350 Ms Excel: как посчитать ячейки с текстом

Обратите внимание, что изначально функции «Етекст()», «Если()» могут работать только с одной ячейкой.

Чтобы распространить их действие на группу ячеек, мы и записали формулу в виде массива, нажав «Ctrl+Shift+Enter».

В этом случае Excel автоматически откорректирует наше выражение, и в строке формул оно будет выглядеть так: «<=Сумм(Если(Етекст(A1:A20);1))>».

Важно! Просто написать формулу «<=Сумм(Если(Етекст(A1:A20);1))>» и ввести ее в ячейку обычным способом (нажав клавишу «Enter») нельзя.

Вот, собственно, и все. Удачной работы!

Похожие записи:

Метки: excel admin Просмотров: 7 594 Запись опубликована в 22:59

Читать еще:  Как увеличить резкость фото онлайн

Подсчет разных значений в сводной таблице Excel (простое пошаговое руководство)

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

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

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

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

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

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

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и различными значениями

Разница между уникальным и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

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

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

Подсчет разных значений в сводной таблице Excel

Предположим, у вас есть данные о продажах:

исходные данные

Нажмите здесь, чтобы загрузить файл примера и делать все вместе со мной:

С этим набором данных вам нужно найти ответ на следующие вопросы:

  • Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
  • Сколько сотрудников продали принтер в 2019 году?

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

Если вы используете Excel 2013 или более поздние версии, в сводной таблице есть встроенная функция, которая быстро подсчитывает количество.

А если вы используете Excel 2010 или ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

В этой статье рассматриваются следующие методы:

  • Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
  • Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

Существует третий метод, он называет метод сводной таблицы в сводной таблице.

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (вам доступна встроенная функция).

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

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

Предположим, у меня есть набор данных, как показано ниже:

исходные данные

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

Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе. Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу.

Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

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

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

Добавляем вспомогательный столбец

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

Ниже приведены шаги, как сделать это:

  • Выберите любую ячейку в таблице.
  • Нажмите вкладку «Вставка».

Вкладка Вставка

  • Нажмите на кнопку Сводная таблица.
Читать еще:  Ошибка Steam Client not found: Что делать

Кнопка Сводная таблица

  • В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.

Создание сводной таблицы

  • Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Области сводной таблицы

Вы получите вот такую сводную таблицу:

Полученная сводная таблица

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Недостатки использования вспомогательного столбца

Хотя этот метод довольно прост, я должен выделить несколько недостатков, связанных с изменением исходных данных в сводной таблице:

  • Источник данных со вспомогательным столбцом не такой динамичный, как сводная таблица. Если изменится поставленная задача, вам придется вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
  • Поскольку вы добавляете больше данных в источник сводной таблицы (который также добавляется в сводный кэш), это может привести к увеличению размера файла Excel.
  • Так как мы используем формулу Excel, это может замедлить работу вашей книги Excel, если в данных тысячи строк.

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

В сводную таблицу добавлены новые функции в Excel 2013, которые позволяют получать количество различных значений.

В случае, если вы используете предыдущую версию, вы не сможете использовать этот метод (используйте метод, описанный выше).

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

Исходные данные

Ниже приведены шаги для получения количества разных сотрудников в сводной таблице:

  • Выберите любую ячейку в таблице.
  • Нажмите вкладку «Вставка».

Вкладка Вставка

  • Нажмите на кнопку Сводная таблица.

Кнопка Сводная таблица

  • В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и выбран новый рабочий лист.
  • Установите флажок «Добавить эти данные в модель данных».

Диалоговое окно Создание сводной таблицы

  • Нажмите ОК.

Приведенные выше шаги вставят новый лист с новой сводной таблицей.

Перетащите регион в область «Строки» и «Сотрудник» в область «Значения». Вы получите такую сводную таблицу:

Новая сводная таблица

В этой сводной таблице приводится общее количество сотрудников в каждом регионе (а не количество разных).

Чтобы получить подсчет разных значений в сводной таблице, выполните следующие действия:

  • Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
  • Нажмите на «Параметры полей значений».

Параметры полей значений

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

Окно Параметры поля значений

  • Нажмите ОК.

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

Число разных элементов

Некоторые вещи, которые нужно знать, добавляя свои данные в модель данных:

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

Что если вы хотите посчитать уникальные значения (а не разные значения)?

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

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

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

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

Вот формула для этого случая:

= ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИМН ($C$2:$C$1001; С2; $B$2:$B$1001; В2) / СЧЁТЕСЛИ ($C$2:$C$1001; С2) <1;0;1); ЕСЛИ (СЧЁТЕСЛИ ($С2:С$22; С2) > 1;0;1);0)

Приведенная выше формула проверяет, встречается ли имя сотрудника только в одном регионе или в нескольких регионах. Это делается путем подсчета количества появлений имени в регионе и деления его на общее количество появлений имени. Если значение меньше 1, это означает, что имя встречается в двух или более двух регионах.

Если имя встречается в нескольких регионах, формула возвращает 0, в противном случае возвращает единицу.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять-таки зависит от того, чего вы пытаетесь достичь.

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

Ссылка на основную публикацию
Adblock
detector
×
×