Как вычислить в excel: Вычисление вложенной формулы по шагам

Содержание

как подсчитать сумму по категориям – Вадим Стеркин

Недавно я на практике столкнулся с задачей подсчитать сумму расходов по разным категориям и хочу сегодня поделиться с вами несколькими вариантами решения. Допустим, исходный список расходов выглядит так:

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

Вообще, в списке явно просматриваются две категории – магазины и кафе. Поэтому имело бы смысл добавить третий столбец «Категория» и указать ее в каждой строке (как сейчас сделано в файле, который вы найдете в конце статьи). Но на скорую руку можно обойтись и без этого.

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

Теперь к делу!

Способ 1 – Сводная таблица

Выделите ячейки с расходами (кроме итога) и выберите на ленте Вставка (Insert) – Сводная таблица (Pivot Table).

Увеличить рисунок

Сводная таблица откроется в новом листе. Дальше в Excel 2013 все элементарно — установите флажки полей для отображения, как показано в правой части рисунка ниже.

Увеличить рисунок

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

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

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

Я знал о сводных таблицах, но фильтры навели меня на мысль о том, нельзя ли обойтись без сводной таблицы. Оказалось, что можно!

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

На исходном листе:

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

Вуаля! Общая сумма расходов в кафе как на ладони.

Вводя формулу SUBTOTAL, вы могли заметить, что у нее 11 функций, причем название каждой повторяется дважды (1 и 101, 2 и 102, и т.д.)

«Сотенные» функции учитывают скрытые строки — проверьте их работу самостоятельно.

Способ 3 – Форматировать как таблицу

Спасибо читателю Сергею, напомнившему в комментариях об этом простом способе. Он подробно описан в статье Николая Павлова (MVP Excel) Умные таблицы. Кстати, они тоже используют формулу SUBTOTAL 🙂

Книга Excel для экспериментов ниже, и да, можно поиграть прямо тут или на SkyDrive! Кстати, язык Excel в данном случае будет соответстовать языку вашего браузера.

А что вы в последний раз считали в Excel? 🙂 Напишите в комментариях!

как вычислить и возвести в квадрат, формула и мастер функций

Правильно посчитать возведение в степень при помощи экселя довольно просто

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

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

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

Видео по вычислению степени в экселе

Определение понятия, математический синтаксис, вычисление по формуле

Под возведением числа A в степень n понимается, что A будет умножено само на себя n раз. A называется основанием, n — показатель — отображает, сколько раз основание должно быть умножено само на себя. В данном случае n показывает, в какую степень должно быть возведено основание. 2», что означает 4 возвели в квадрат. Для работы с числовыми величинами необходимо, чтобы в ячейках был установлен формат «числовой». Его можно выбрать в диалоговом окне «Формат ячейки».

Использование мастера функций

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

Для это помещаем текстовый курсор в ячейку, в которой будет находиться итоговое значение (это можно сделать двойным щелчком мыши).
Выбираем «Вставка», «Функция» (или одновременное нажатие клавиш Ctrl+F2).
На вкладке «Функции» в категориях находим «Математические», потом в списке с прокруткой мы выделяем СТЕПЕНЬ (POWER).
«Далее».
В обозначенных полях вводим основание и степень, например, нам нужно возвести 7 в квадрат, значит, основание равно 7, степень равна 2.
«OK».

В ячейке записывается полученный результат.

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

Оставить комментарий ВКонтакте

Как автоматически вычислять в Excel

Автоматический калькулятор в Excel контролирует, когда и как пересчитываются формулы.

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

Рисунок 1. Параметры расчета в Excel

Использовать автоматические расчеты в Excel эффективно, есть 3 важных соображения, которые мы должны понимать;

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

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

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

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

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

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

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

Точность — относится к степени точности вычислений в Excel.

Excel вычисляет и сохраняет с точностью до 15 важных цифр.

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

Автоматический расчет в Excel

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

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

Однако мы можем изменить это поведение на остановить вычисления Excel .

Для изменения параметров расчета Excel;

На ленте нашего рабочего листа щелкните вкладку «Формулы», а затем. В группе «Расчет» нажмите «Параметры расчета» и выберите любой из доступных вариантов:

Рисунок 2. Варианты расчета в Excel

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

Параметр « Автоматически, кроме таблиц данных » указывает автовычисление Excel на автоматический пересчет любых зависимых формул, за исключением таблиц данных.

Обратите внимание, однако, что эта опция отключит вычисления в Excel только для таблиц данных, в то время как обычная таблица Excel будет выполнять автоматические вычисления в Excel .

Включение опции «Вручную» отключит расчеты в Excel . Открытые рабочие листы будут пересчитаны только тогда, когда мы принудительно Excel для пересчета .

Как заставить Excel пересчитать

Когда мы открываем рабочий лист и Excel не пересчитывает наши вмененные формулы, это, скорее всего, означает, что автоматический расчет Excel выключен .

Мы можем либо включить автоматический калькулятор в Excel (см. рис. 2 выше), либо заставить Excel выполнить перерасчет.

Чтобы пересчитать вручную, откройте наш рабочий лист, обновите все значения данных, а затем щелкните вкладку «Формулы»> группу «Расчет», а затем нажмите кнопку «Рассчитать сейчас»;

Рис. 3. Кнопка «Рассчитать сейчас» в Excel

Мгновенное подключение к Excel Expert

В большинстве случаев проблема, которую вам нужно будет решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Как рассчитать средние значения в Excel (7 простых способов)

Как рассчитать средние значения в Excel

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

СРЕДНИЙ
СРЕДНЯЯ
СРЕДНЕЕСЛИ
МЕДИАНА
РЕЖИМ
Бонус: вложение функций СРЕДНИЙ, БОЛЬШОЙ и МАЛЕНЬКИЙ

СРЗНАЧ

Наиболее общепризнанным средним является среднее арифметическое, и Excel использует функцию СРЗНАЧ для его нахождения. Функция Excel AVERAGE используется для генерации числа, представляющего типичное значение из диапазона, распределения или списка чисел. Он рассчитывается путем сложения всех чисел в списке, а затем деления суммы на количество значений в списке.

Загрузите бесплатно файл среднего значения в формате Excel!

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

Введите адрес электронной почты

Синтаксис

Функция СРЗНАЧ в Excel проста. Синтаксис:

  =СРЗНАЧ(число1, [число2],. ..)

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

Функция СРЗНАЧ может обрабатывать до 255 аргументов, каждый из которых может быть значением, ссылкой на ячейку или диапазоном. Требуется только один аргумент, но, конечно, если вы используете функцию СРЗНАЧ, скорее всего, у вас есть по крайней мере два.

Приведенная ниже формула вычисляет среднее значение чисел 100, 95 и 80.

  =СРЗНАЧ(100,95,80)

Для вычисления среднего значения в ячейках B2, B3, B4 и B5 введите:

  =СРЗНАЧ(B2:B5)

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

Чтобы вычислить среднее значение несмежных (несмежных) ячеек, просто удерживайте клавишу Control (или клавишу Command — для пользователей Mac) во время выбора.

При вводе ссылок на ячейки непосредственно в ячейку или строку формул несмежные ссылки разделяются запятыми. Например:

.
  =СРЗНАЧ(B2:B5,B8)

Примечания

При использовании функции СРЗНАЧ помните следующее.

Текстовые значения и пустые ячейки игнорируются.

Слово «Болен» в ячейке B6 (ниже) приводит к тому, что функция СРЗНАЧ полностью игнорирует эту ячейку. Это означает, что средний балл в ячейке I6 был рассчитан с использованием значений в диапазоне от C8 до H8, а общая сумма была разделена на 6 испытуемых вместо 7.

Нулевые значения включены.

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

СРЗНАЧА

Чтобы устранить это несоответствие, можно использовать функцию СРЗНАЧА, чтобы включить все значений в распределении, включая текст. Формат аналогичный:

  =СРЗНАЧA(значение1, [значение2],...)

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

СРЗНАЧА оценивает текстовые значения как ноль, а логическое значение ИСТИНА оценивается как 1. Логическое значение ЛОЖЬ считается нулем.

Сравните разницу в результатах между использованием СРЗНАЧ и СРЗНАЧА в приведенном ниже примере.

Средние значения для учащегося E и учащегося J теперь одинаковы при использовании функции СРЗНАЧА.

Ознакомьтесь с базовым и продвинутым курсом Microsoft Excel

СРЗНАЧЕСЛИ

Существуют способы найти среднее только тех чисел, которые удовлетворяют определенным критериям. С помощью функции СРЗНАЧЕСЛИ Excel ищет заданное условие в указанном диапазоне, а затем находит среднее арифметическое для ячеек, соответствующих этому условию.

Синтаксис функции СРЗНАЧЕСЛИ:

  =СРЗНАЧЕСЛИ(диапазон,критерий,[средний_диапазон])

Диапазон — это место, где мы можем ожидать найти ячейки, соответствующие критериям.
Критерий — это значение или выражение, которое Excel должен искать в диапазоне.
Average_range — необязательный аргумент. Это диапазон ячеек, в которых находятся значения, подлежащие усреднению. Если средний_диапазон опущен, используется диапазон .

СРЗНАЧЕСЛИ пример 1

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

в функции СРЗНАЧЕСЛИ также может быть в форме логического выражения, как в примере ниже:

  =СРЗНАЧЕСЛИ(B4:h5;"<>0")

Приведенная выше формула найдет среднее значение в диапазоне от B4 до h5, равное не равно нулю. Обратите внимание, что третий (необязательный) аргумент опущен, поэтому для вычисления среднего значения используются ячейки в диапазоне .

Поскольку ячейки, которые оцениваются как нули, опускаются из-за наших критериев, обратите внимание на разницу в результатах Студента E и J ниже при использовании функции СРЗНАЧЕСЛИ.

Чтобы найти среднее значение ячеек, удовлетворяющих нескольким критериям, используйте функцию СРЗНАЧЕСЛИМН.

МЕДИАНА

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

Возьмем, к примеру, следующий список чисел:

22, 1, 14, 21, 15, 16, 18, 100, 19, 19, 22, 25, 18

Нахождение среднего арифметического даст результат 23.8.

Однако, внимательно присмотревшись к распределению чисел в списке, вряд ли можно сказать, что 23,8 — это среднее значение этих чисел. Проблема, конечно, в том, что число 100 является выбросом и увеличивает сумму чисел.

Поэтому в некоторых ситуациях предпочтительнее использовать функцию МЕДИАНА. Эта функция определяет числовой порядок оцениваемых значений и использует среднее значение в качестве среднего.

Синтаксис:

  =МЕДИАНА(число1, [число2], …)

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

В приведенном выше примере порядок номеров будет следующим: распределение, в результате чего седьмое число является средним значением. Таким образом, медиана будет равна 19.

Если число значений четное, медиана будет определяться путем нахождения среднего значения двух чисел в середине распределения. Так, для значений 7,9,9,11,14,15 медиана будет (9+11)/2=10.

Функция МЕДИАНА игнорирует ячейки, которые содержат текст, логические значения или не содержат значений.

РЕЖИМ

Третий метод определения среднего значения набора чисел заключается в нахождении режима или наиболее часто повторяющегося числа.

В настоящее время в Excel есть три функции «режим». Классический MODE следует синтаксису:

.
  =РЕЖИМ(число1, [число2],...)

В этой функции Excel оценивает значения в списке или диапазоне и выбирает наиболее часто встречающееся число в качестве среднего значения группы.

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

1, 14, 15, 16, 18, 18, 19, 19, 21, 22, 22, 25, 100

Каждое из чисел 18, 19 и 22 встречается два раза. Какой режим? Microsoft выбирает первое появляющееся значение в качестве режима — в приведенном выше случае 18.

Если бы эти же числа были расположены в обратном порядке, то режимом считалось бы 22.

Если бы числа были расположены в случайном порядке, то Excel выбирал бы из 18, 19 и 22 на основе числа, появившегося в распределении первым.

Например, в списке:

19, 22, 1, 14, 21, 15, 16, 18, 100, 19, 22, 25, 18

Функция РЕЖИМ считает 19 режимом.

РЕЖИМ.МУЛЬТ

Функция РЕЖИМ.МУЛЬТ является решением несоответствий, возникающих в приведенном выше сценарии. Это позволяет нам предвидеть и учитывать возможность того, что в группе чисел может быть более одного режима.

Синтаксис:

  =MODE.MULT(число1, [число2],...)

Поскольку MODE.MULT является функцией массива (CSE), следующие шаги при использовании этой функции:

Выберите вертикальный диапазон для вывода
Введите формулу MODE.MULT
Одновременно нажмите Control + Shift + Enter

Одновременное нажатие клавиш Control + Shift + Enter (CSE) приведет к тому, что Excel автоматически поместит скобки (фигурные скобки) вокруг формулы и вернет «разброс» результатов, равный количеству ячеек, выбранных на шаге 1. Если есть более одного режима, они будут отображаться вертикально в выходных ячейках. Функция РЕЖИМ.МУЛЬТ вернет ошибку «#Н/Д», если:

нет повторяющихся значений или
в выходном диапазоне нет дополнительных режимов.

РЕЖИМ.ОДИН

Как и функция МОДА.МУЛЬТ, функция МОДА.ОДИН была реализована в Excel 2010. Синтаксис:

  =MODE.SNGL(число1, [число2],...]

Функция MODE.SNGL ведет себя как классическая функция MODE при определении выходных данных.

Творческое использование функции AVERAGE

Верхние 3

Мы можем объединить функцию СРЗНАЧ с функцией НАИБОЛЬШИЙ, чтобы определить среднее «n» первых значений.

Функция НАИБОЛЬШИЙ извлекает n ^t ^h наибольшее число из диапазона, используя формат

  = НАИБОЛЬШИЙ (массив, k)

, где k — это число n ^t ^h.

Используя этот формат, мы можем отображать число в 1-м, 2-м, 5-м или любом другом разряде.

Чтобы получить среднее значение трех самых больших чисел в диапазоне, мы должны вложить функции СРЗНАЧ и НАИБОЛЬШИЙ следующим образом:

  =СРЗНАЧ(МАССИВ(массив, {1,2,3}))

Когда мы вводим фигурные скобки вокруг аргумента k , Excel идентифицирует первое, второе и третье по величине числа в массиве и функцию СРЗНАЧ находит их среднее.