Функции для подсчёта среднего
Автор алекс139, 6 марта 2018, 23:14
0 Пользователи и 1 гость просматривают эту тему.
Здравствуйте..
Подскажите пожалуйста, какой функцией (формулой) посчитать среднюю цену.. Столбец А- кол. товара, В- цена.
=SUMPRODUCT(A1:A23;B1:B23)/SUM(A1:A23), если нужна средневзвешенная
В строке состояния по умолчанию отображается сумма содержимого выбранных ячеек.
Чтобы изменить отображаемую стандартную формулу, щелкните поле правой кнопкой мыши и выберите нужную формулу. Доступные формулы: “Среднее значение”, “Количество значений”, “Количество значений”, “Максимум”, “Минимум”, “Сумма” и “Нет” (без формулы).
(x86_64) Kubuntu 16.04.3 – LibreOffice 6.0.2 / 6.1 alpha
бли.. перестало правильно считать.. не пойму почему..
уменьшал масштаб, что бы все ячейки было видно..- думаю это же не могло повлиять..
Цитата: алекс139 от 22 марта 2018, 20:37
бли.. перестало правильно считать.. не пойму почему..
уменьшал масштаб, что бы все ячейки было видно..- думаю это же не могло повлиять..
Масштаб влияет только на отображение, а не на расчёты. Что именно не правильно считает и как это выглядит?
Давайте документ, что на снимке, заодно скажите, какой результат вы считаете правильным.
в столбце А – цена ,там 1,14-1,335
в столбце B – количество, обьём..
ранее получалось средне-взвешенное 1,24(примерно)
что сделал не так не пойму.. 
Раньше было условие:
Цитата: алекс139 от 6 марта 2018, 21:14Подскажите пожалуйста, какой функцией (формулой) посчитать среднюю цену.. Столбец А- кол. товара, В- цена.
под это условие mikekaganski формулу предложил, а сейчас столбцы A и B поменялись местами:
Цитата: алекс139 от 22 марта 2018, 19:28в столбце А – цена ,там 1,14-1,335
в столбце B – количество, обьём..
соответственно и формула должна измениться:
=SUMPRODUCT(A9:A38;B9:B38)/SUM(B9:B38)
Цитата: rami от 22 марта 2018, 21:57
Раньше было условие:Цитата: алекс139 от 6 марта 2018, 21:14Подскажите пожалуйста, какой функцией (формулой) посчитать среднюю цену.. Столбец А- кол. товара, В- цена.
под это условие mikekaganski формулу предложил, а сейчас столбцы A и B поменялись местами:
Цитата: алекс139 от 22 марта 2018, 19:28в столбце А – цена ,там 1,14-1,335
в столбце B – количество, обьём..соответственно и формула должна измениться:
=SUMPRODUCT(A9:A38;B9:B38)/SUM(B9:B38)
спасибо..
ещё один вопрос..- какой функцией вычисляется обратное 1 . Т.е 1/число
LibreOffice – это бесплатный офисный пакет, включающий приложение для работы с электронными таблицами Calc. Calc – это программный пакет, который имеет множество функций и формул для электронных таблиц. Некоторые из них являются условными функциями, которые дают результаты формулы и значения, основанные на конкретном условии. Вот как можно добавить некоторые условные IF-функции Calc в электронные таблицы.
Также смотрите нашу статью Что такое Office 365?
Если у вас еще нет этого приложения, нажмите кнопку Загрузить версию 5.2.0 на этой странице. Запустите мастер установки LibreOffice, чтобы установить пакет. Затем откройте окно Calc, показанное на снимке ниже.
Функция ЕСЛИ
Во-первых, давайте добавим базовую функцию IF / ELSE в электронную таблицу Calc. Это позволяет вам создать условный оператор, в соответствии с которым результат одной ячейки зависит от значения другой ячейки. Сначала введите значение 777 в ячейку B4. Затем щелкните ячейку C4 и нажмите кнопку « Мастер функций» . Выберите « IF» в этом окне, а затем нажмите кнопку « Далее» , чтобы открыть параметры функции, показанные непосредственно ниже.
Нажмите кнопку « Выбрать» рядом с текстовым полем « Тест» , а затем выберите ячейку B4. Затем введите> 500 после B4 в текстовом поле Test . Введите «true» в поле Then_value и введите «false» в текстовое поле « Otherwise_value», как показано на снимке ниже.
Нажмите OK, чтобы закрыть окно. Теперь электронная таблица будет соответствовать той, что показана на снимке ниже. Здесь вы только что создали условную функцию, которая подтверждает значение ячейки B4 больше 500. Если число B4 меньше 500, ячейка IF будет содержать false. Полная формула = IF (B4> 500, «истина», «ложь») .
Вы можете установить множество функций IF, практически одинаково с =, > и <, что означает равно, больше или меньше чем. Чтобы включить числовое значение в ячейку IF, введите число или ссылку на ячейку в поле Then_value без дополнительных кавычек. Кавычки необходимы для вывода текста, как в примере.
Функция SUMIF
Есть множество функций, которые распространяются на базовый оператор IF. Например, с помощью функции SUMIF вы можете добавлять числа, соответствующие определенным критериям. Например, предположим, что вам нужно только суммировать цифры продаж, которые соответствуют определенным критериям или условиям, в электронной таблице. Тогда SUMIF, или SUMIFS для нескольких условий, будет идеальным для этого.
Например, настройте функцию SUMIF, которая только добавляет ячейки вместе в диапазон, который затмевает определенное значение. Для этого введите четыре значения в таблицу точно так, как показано ниже. Затем выберите ячейку для включения функции SUMIF и нажмите кнопку « Мастер функций» . Выберите SUMIF и нажмите кнопку Далее , чтобы открыть мастер SUMIF.
Нажмите кнопку « Выбрать» рядом с текстовым полем диапазона , а затем выберите ячейки, содержащие введенные вами числа. Ниже вы должны ввести «> 55» в поле критериев . Вы также должны выбрать те же ячейки B4: B7 в поле sum_range, как показано ниже.
Теперь, когда вы нажмете кнопку OK , электронная таблица вернет значение 154 в ячейку SUMIF. Таким образом, электронная таблица добавила две ячейки вместе с числами выше 55. Две ячейки с 77 в них составляют 154.
Таким образом, вы можете сложить числа в столбце или строке, которые меньше или равны определенному значению, практически одинаково. Для этого вам нужно заменить> в поле критериев на <или =. Например, чтобы добавить числа меньше 55, вы должны ввести «<55» в поле критерия .
Функция COUNTIF
COUNTIF – это еще одна условная функция, которую вы можете добавить в электронные таблицы Calc. Эта функция суммирует количество ячеек, а не их конкретные значения, которые соответствуют условию. Например, вы можете настроить функцию COUNTIF, которая подсчитывает, сколько ячеек в столбце содержит числа, которые меньше определенного значения.
Итак, давайте сделаем это, введя некоторые числа в таблицу Calc точно так же, как на снимке прямо ниже. Щелкните ячейку, чтобы добавить функцию COUNTIF, а затем откройте мастер функций. Выберите COUNTIF > Далее, чтобы открыть его мастер.
Выберите числа, введенные в электронную таблицу, нажав кнопку « Выбрать» рядом с диапазоном. Введите «= 1232» в поле критерия, как показано ниже. Закройте окно мастера функций.
Теперь ячейка COUNTIF подсчитывает количество ячеек, в которые входит 1232, что в этом примере равно трем. Вы можете подсчитать, сколько ячеек содержит большее или меньшее значение, чем 1232, заменив = на <или>. Эта функция может пригодиться для больших таблиц с большим количеством чисел в столбце или строке.
Функция AVERAGEIF
Функция AVERAGEIF похожа на SUMIF за исключением того, что она находит среднее значение ячеек на основе определенного условия. Таким образом, вы можете найти среднее значение ячеек, которые затмевают или ниже определенного числа. В качестве альтернативы вы также можете основывать условие на заголовке строки или столбца.
Введите несколько чисел в строку таблицы точно так же, как на снимке прямо ниже. Выберите ячейку для функции AVERAGEIF, откройте Мастер функций и выберите AVERAGEIF . Откроется мастер AVERAGEIF для настройки функции с помощью.
Нажмите кнопку « Выбрать» рядом с полем диапазона, чтобы выбрать ячейки, в которые вы ввели числа. Введите «<145» в поле критериев . Выберите те же ячейки, что и в поле диапазона, для поля Average_range . Нажмите OK, чтобы закрыть окно.
Теперь ячейка AVERAGEIF должна иметь значение 131. Это среднее значение двух ячеек в столбце ниже 145. Значения 139 и 123 составляют 162, которые делятся на два и равны 131.
Вы также можете установить условие на основе текста в другом столбце или строке. Например, введите некоторый текст в соседний столбец электронной таблицы, как показано ниже. Затем выберите ячейки в строке, содержащей текст для поля диапазона функции AVERAGEIF. Введите «весна» в поле критерия и выберите ячейки с числами в них для текстового поля Average_range . Это найдет среднее значение в ячейках пружин.
Это четыре из условных функций, которые вы можете добавить в свою таблицу Calc. Вы также можете выбрать функции SUMIFS, COUNTIFS и AVERAGEIFS, чтобы настроить функции на основе нескольких условий. Функции, безусловно, пригодятся, когда вам понадобятся некоторые значения из ячеек таблицы данных, которые соответствуют заданным условиям.
В прошлом году я написал статью Сумма, автосумма и некоторые другие секреты LibreOffice Calc, в которой попытался показать основные возможности суммирования в LibreOffice Calc. Тогда, в той статье, я говорил что нет справочного материала по функции AGGREGATE в справке LibreOffice, и отсылал в справку MS Office к функции АГРЕГАТ, так как она совместима с функцией Calc. Позже уже, я сделал патч в английскую версию Справки, но, к сожалению, у команды локализации пока нет времени заниматься переводом. Эта статья, хотя и основана на моём патче, не является его переводом, но пытается предоставить больше информации в более простом, понятном виде об этой функции.
Основная задача функции AGGREGATE вернуть значение из диапазона, который может иметь скрытые строки, ошибки, результаты других функции SUBTOTAL и AGGREGATE. Я говорю «значение», потому что AGGREGATE может возвращать не только сумму видимых ячеек, но и находить минимальное/максимальное значение, подсчитывать количество ячеек и многое другое.
Сразу хочу заметить, что функция предназначена для работы с Автофильтром и при использовании её для других целей, можно получать неожиданные результаты. Например, если вы используете её для вертикального диапазона, но без Автофильтра, то пересчет значения при скрытии строк не будет работать. При применении к горизонтальному диапазону, она не определяет скрытые столбцы, хотя может игнорировать ошибки в ячейках или результаты SUBTOTAL и AGGREGATE.
Синтаксис
AGGREGATE(Функция; Условие; Ссылка1 [; Ссылка2 [; …]])
или
AGGREGATE(Функция; Условие; Массив [; k])
Функция – это обязательный аргумент. Индекс или ссылка на ячейку содержащую значение от 1 до 19, задающее применяемую функцию для возвращаемого значения в соответствии со следующей таблицей.
| Индекс | Применяемая функция | Значение |
|---|---|---|
| 1 | AVERAGE | Возвращает среднее значение |
| 2 | COUNT | Считает ячейки в которых есть числа, текстовые значения игнорируются |
| 3 | COUNTA | Считает ячейки содержащие значения, текстовые записи также учитываются, даже если они содержат пустую строку |
| 4 | MAX | Возвращает максимальное значение |
| 5 | MIN | Возвращает минимальное значение |
| 6 | PRODUCT | Перемножает все значения |
| 7 | STDEV.S | Вычисляет стандартное отклонение по выборке |
| 8 | STDEV.P | Вычисляет стандартное отклонение по генеральной совокупности |
| 9 | SUM | Суммирует все значения |
| 10 | VAR.S | Вычисляет дисперсию на основе выборки |
| 11 | VAR.P | Вычислить дисперсию, основанную на генеральной совокупности |
| 12 | MEDIAN | Возвращает медиану |
| 13 | MODE.SNGL | Возвращает моду |
| 14 | LARGE | Возвращает k-ое по величине наибольшее значение |
| 15 | SMALL | Возвращает k-ое по величине наименьшее значение |
| 16 | PERCENTILE.INC | Возвращает персентиль от 0 до 1 включительно |
| 17 | QUARTILE.INC | Возвращает квартиль от 0 до 1 включительно |
| 18 | PERCENTILE.EXC | Возвращает персентиль от 0 до 1 исключая крайние значения |
| 19 | QUARTILE.EXC | Возвращает квартиль от 0 до 1 исключая крайние значения |
Как видно из таблицы, все функции присутствуют в отдельном виде в LibreOffice Calc. И если вам нужно уточнить их действие, то вы можете обратится к справке.
Условие – это обязательный аргумент. Индекс условия или ссылка на ячейку со значением от 0 до 7, определяющий какие значения будут игнорироваться при подсчете.
| Индекс | Применяемое условие |
|---|---|
| 0 | Игнорируются только встроенные функции SUBTOTAL и AGGREGATE |
| 1 | Игнорируются только скрытые строки и встроенные функции SUBTOTAL и AGGREGATE |
| 2 | Игнорируются только ошибки и встроенные функции SUBTOTAL и AGGREGATE |
| 3 | Игнорируются скрытые строки, ошибки, встроенные функции SUBTOTAL и AGGREGATE |
| 4 | Ничего не игнорируется |
| 5 | Игнорируются только скрытые строки |
| 6 | Игнорируются только ошибки |
| 7 | Игнорируются только скрытые строки и ошибки |
Ссылка1 – это обязательный аргумент. Первый числовой аргумент (если диапазон задается списком значений внутри функции) или ссылка на ячейку, которая содержит этот числовой аргумент.
Ссылка2, 3, … – это не обязательные аргументы, которые представляют собой числовой значения или ссылки на ячейку (до 253 аргументов), для которых вам необходимо рассчитать функцию AGGREGATE.
Массив – это обязательный аргумент. Массив может быть указан границами диапазона, именем именованного диапазона или заголовком столбца.
Замечу, что для использования названий столбцов, должна быть включена функция “Автоматический поиск заголовков столбцов и строк» (Сервис → Параметры → Calc → Вычисления).
k – Обязательный аргумент для следующих функций: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC. Это числовой аргумент, который должен соответствовать второму аргументу этих функций.
Следующие ошибки могут появляться при использовании функции:
Если аргумент k является необходимым, но не указан, то функция возвращает ошибку Err:511.
Если аргументы Функция и/или Условие указан не правильно (индекс не соответствует табличному), то функция возвращает ошибку Err:502.
Примеры
Файл с примерами
=AGGREGATE(4;2;A2:A9)
Возвращает максимальное значение в диапазоне A2:A9 =34, в то время как =MAX(A2:A9) возвращает ошибку.
=AGGREGATE(9;5;A5:C5)
Возвращает сумму в строке A5:C5 =29, даже если некоторые из столбцов скрыты.
=AGGREGATE(9;5;B2:B9)
Возвращает сумму в столбце B =115. Если какая-либо строка скрыта, то функция опустить её значение, например, если 7-ая строка скрыта, функция вернёт 95.
Если вам нужно применить функцию с диапазоном 3D, этот пример показывает, как это сделать.
=AGGREGATE(13;3;Sheet1.B2:B9:Sheet3.B2:B9)
Функция возвращает значения второго столбца сквозного (3D) диапазона листов 1:3 (который содержит те же данные) =8.
Вы можете использовать ссылку на ячейку или диапазон для любого аргумента в функции. В следующем примере показано, как это работает. Кроме того, это показывает, что вы можете использовать названия столбцов, чтобы задать массив.
=AGGREGATE(E3;E5;’Второй’)
Если E3 =13 и E5 =5, функция возвращает моду из второго столбца, которая равна 10.
Обратите внимание, что если в столбце нет повторяющихся значений, функция MODE.SNGL возвращает ошибку.
Пример на листе 4 в фале с примерами не очень типичен, так как в нём нет ошибок и мы могли бы для суммы в этом случае использовать SUBTOTAL, но сам смысл я думаю понятен. Если у таблица является сборкой из разных таблиц, и в ней могут быть ошибки, то функция AGGREGATE — это именно то что вам нужно.
Дополнительная литература и источники:
- Уроки LibreOffice
- Список функций по категориям на сайте Онлайн Справки LibreOffice Calc
- ЧаВо LibreOffice Calc
- Раздел Calc на Форуме поддержка пользователей открытых офисных пакетов
Содержание
- Функция ЕСЛИ
- Функция СУММЕСЛИ
- Функция СЧЁТЕСЛИ
- Функция СРЗНАЧЕСЛИ
LibreOffice – это бесплатный офисный пакет, который включает приложение Spreadsheet Calc. Calc – это программный пакет, который имеет множество функций и формул для электронных таблиц. Некоторые из них являются условными функциями, которые предоставляют значения формул и результаты на основе определенного условия. Вот как добавить в электронные таблицы некоторые из функций условного ЕСЛИ Calc.
См. Также нашу статью Что такое Office 365?
Если у вас еще нет этого приложения, нажмите кнопку Скачать версию 5.2.0 на этой странице. Запустите мастер установки LibreOffice, чтобы установить пакет. Затем откройте окно Calc, показанное на изображении ниже.
Функция ЕСЛИ
Во-первых, давайте добавим базовую функцию IF / ELSE в рабочий лист Calc. Это позволит вам настроить условный оператор, в котором результат одной ячейки зависит от значения другой ячейки. Сначала введите значение 777 в ячейку B4. Затем щелкните ячейку C4 и нажмите кнопку Мастер роли . Пожалуйста выберите ЕСЛИ в этом окне, а затем нажмите кнопку Следующий , чтобы открыть параметры функции, показанные непосредственно ниже.
Нажмите кнопку Выбирать рядом с текстовым полем Проверять а затем выберите ячейку B4. Затем введите> 500 после B4 в текстовое поле. Контрольная работа . Введите “true” в поле Then_value и введите “false” в текстовое поле В противном случае_значение как показано на картинке ниже.
Нажмите на Принять закрыть окно. Таблица теперь будет соответствовать таблице, показанной на изображении ниже. Здесь вы только что задали условие, если функция, которая фиксирует значение ячейки B4, больше 500. Если бы число B4 было меньше 500, ячейка IF будет включать false. Полная формула = ЕСЛИ (B4> 500, «истина», «ложь») .
Вы можете сконфигурировать множество функций ЕСЛИ почти таким же образом, используя =,> и <, что означает равно, больше или меньше чем. Чтобы включить числовое значение в ячейку ЕСЛИ, введите число или ссылку на ячейку в поле. Then_value без лишних кавычек. Кавычки необходимы для вывода текста, как в примере.
Функция СУММЕСЛИ
Существует множество функций, расширяющих базовый оператор IF. Например, с помощью функции СУММЕСЛИ можно складывать числа, соответствующие определенным критериям. Например, предположим, что вам нужно только суммировать цифры продаж, которые соответствуют определенному критерию или условию на листе. Таким образом, СУММЕСЛИ или СУММЕСЛИМН для нескольких условий было бы идеально для этого.
В качестве примера настройте функцию СУММЕСЛИ, которая суммирует только ячейки в диапазоне, который затмевает определенное значение. Для этого введите четыре значения в электронную таблицу точно так, как показано ниже. Затем выберите ячейку для включения функции СУММЕСЛИ и нажмите кнопку Мастер роли . Пожалуйста выберите СУММЕСЛИ и нажмите кнопку Следующий , чтобы открыть мастер СУММЕСЛИ.
Щелкните текстовое поле Выбирать рядом с диапазоном, а затем выберите ячейки, содержащие введенные вами числа. Ниже вы должны ввести “> 55” в поле. критерии . Вы также должны выбрать те же ячейки B4: B7 в поле диапазон_суммы как показано ниже.
Теперь, когда я нажимаю кнопку хорошо , рабочий лист вернет значение 154 в ячейке СУММЕСЛИ. Таким образом, электронная таблица добавила две ячейки вместе с числами больше 55. Две ячейки с 77 в сумме дают 154.
Таким образом, вы можете складывать числа в столбце или строке, меньшие или равные определенному значению, почти таким же образом. Для этого необходимо заменить символ> в поле критерии на <или =. Например, чтобы сложить числа меньше 55, вы должны ввести поле «критерии «.
Функция СЧЁТЕСЛИ
СЧЁТЕСЛИ – еще одна условная функция, которую можно добавить в рабочие листы Calc. Эта функция суммирует количество ячеек, а не их конкретные значения, которые соответствуют условию. Например, вы можете настроить функцию СЧЁТЕСЛИ, которая подсчитывает, сколько ячеек в столбце содержат числа, которые меньше определенного значения.
Итак, давайте сделаем это, введя некоторые числа в электронную таблицу Calc, как показано на снимке ниже. Щелкните ячейку, чтобы добавить функцию СЧЁТЕСЛИ, а затем откройте Мастер функций. Пожалуйста выберите СЧЁТЕСЛИ>Следующий , чтобы открыть мастер.
Выберите числа, введенные в электронную таблицу, нажав кнопку Выбирать рядом с ассортиментом. Напишите в поле “= 1232”. критерии как показано непосредственно ниже. Закройте окно Role Wizard.
Теперь ячейка СЧЁТЕСЛИ будет подсчитывать количество ячеек, которые включают в себя 1232, что в этом примере равно трем. Вы можете рассчитать, сколько ячеек содержит значение больше или меньше 1232, заменив = на <or>. Эта функция может быть полезна для больших листов с большим количеством чисел в столбце или строке.
Функция СРЗНАЧЕСЛИ
Функция СРЗНАЧЕСЛИ похожа на СУММЕСЛИ, за исключением того, что она находит среднее значение ячеек на основе определенного условия. Таким образом, вы можете найти среднее значение ячеек, которые затмевают или ниже определенного числа. В качестве альтернативы условие также может быть основано на заголовке строки или столбца.
Введите несколько чисел в строку в таблице точно так, как показано на снимке ниже. Выберите ячейку для функции СРЗНАЧЕСЛИ, откройте Мастер функций и выберите СРЕДНИЙ . Откроется мастер AVERAGEIF для настройки функции.
нажать на кнопку Выбирать рядом с диапазоном, чтобы выбрать ячейки, в которые вы ввели числа. Введите в поле «критерии» «. Выберите те же ячейки, что и флажок классифицировать для коробки Средний диапазон . Нажмите на Принять закрыть окно.
Теперь ячейка Averageageif должна иметь значение 131.Это среднее значение в двух ячейках в столбце ниже 145. Значения 139 и 123 в сумме дают 162, которые делятся на два, чтобы получить 131.
Вы также можете установить условие на основе текста в другом столбце или строке. Например, введите текст в соседний столбец электронной таблицы следующим образом. Затем выберите ячейки в строке, которая включает текст диапазона функции СРЗНАЧЕСЛИ. Введите “весна” в текстовое поле. критерии , и выберите ячейки с числами в них для текстового поля средний_ диапазон . Это найдет среднее значение ячеек в весенних строках.
Это четыре условные функции, которые вы можете добавить в свою электронную таблицу Calc. Вы также можете выбрать функции СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН для настройки функций на основе нескольких условий. Эти функции пригодятся, когда вам понадобятся некоторые значения ячеек таблицы данных, которые соответствуют указанным условиям.
AVERAGE
Функция возвращает среднее (арифметическое) аргументов, игнорируя текст.
Синтаксис функции:
| =AVERAGE(number1; number2; … number30) |
|---|
Рис. 1. Пример функции.
где:
- number1; number2; … number30 — до 30 чисел или диапазонов, содержащих числа.
Среднее, также называемое средним арифметическим, а также называемое средним числом, вычисляется математически сложением значений, а затем делением на количество значений (размер выборки n).
|
(1) |
Сумма данных 
может быть определена c использованием функции =SUM(data). Размер выборки n может быть определён c использованием функции =COUNT(data). Таким образом =SUM(data)/COUNT(data) вычислит среднее.
На рис. 1:
- в ячейке C2 возвращается 4, среднее арифметическое этих трёх чисел в списке;
- в ячейке C4 возвращается 2. 2, среднее арифметическое значений диапазона A2:A4 — чисел 1 и 3. Текст игнорируется.
 |
InfraOffice.pro Calc предполагает, что логические значения 1 (TRUE) и 0 (FALSE). MS Excel игнорирует логические значения. |
