Как сделать медиану в excel

Добавил пользователь Евгений Кузнецов
Обновлено: 04.10.2024

Если память служит, агрегатные функции Excel тесно связаны с агрегатными функциями SQL, и здесь вы также не найдете медианную функцию. Обход SQL может дать некоторое представление о создании версии таблицы Excel Pivot.

Для простых примеров вы можете использовать формулы массива вместо сводной таблицы.

Если у вас есть исходные данные в строках 10: 1000, обозначения категорий исходных данных в столбце A, значения исходных данных в столбце C и категория, рассматриваемая в G3, следующая формула массива найдет медиану:

Зафиксируйте запись с помощью Ctrl + Shift + Enter и скопируйте для категорий в G4, G5 и т. Д.

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

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

Вы действительно можете использовать iserror функцию, чтобы исключить общее количество строк из медианы. Например, если Total row метки находятся, row A а данные, для которых вы хотите получить медиану, находятся в row I :

Самый быстрый и простой способ получить медиану в сводных таблицах - это импортировать файл Excel в Google Sheets. Там вы можете создать сводную таблицу и использовать медиану.

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

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

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

это конечный результат - см. изображение здесь:

ПРИМЕЧАНИЕ: вы не можете поместить ячейку формулы медианы / смещения в тот же столбец, из которого вы получаете медианное значение. ТАКЖЕ, смещение позволяет вам получить медиану из значения столбца за вычетом итоговой строки.

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

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

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

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

Эти инструкции применимы к Excel 2019, 2016, 2013, 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011, Excel для Office 365 и Excel Online.

Как работает функция MEDIAN

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

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

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

аргументы

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

MEDIAN Синтаксис функции

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

Ниже приведен синтаксис для функции MEDIAN:

= MEDIAN : все формулы MEDIAN начинаются таким образом.
Number1:данные, необходимые для расчета по функции.
Число 2: необязательные дополнительные значения данных, которые должны быть рассчитаны в среднем. Максимально допустимое количество записей — 255, каждая из которых должна быть разделена запятой.

Этот аргумент может содержать:

Список чисел для усреднения на расположение данных на листе
Диапазон ссылок клеток
Именованный диапазон

Варианты ввода функции и ее аргументов:

Ввод полной функции, например = MEDIAN (A2: F2) , в ячейку листа
Ввод функции и аргументов с использованием диалогового окна функции

Пример функции MEDIAN

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

Ответ 20 должен появиться в ячейке G2

Если щелкнуть ячейку G2, полная функция = MEDIAN (A2: F2) появится на панели формул над рабочим листом.

Почему медиана 20? Для первого примера в изображении, поскольку существует нечетное количество аргументов (пять), среднее значение рассчитывается путем нахождения среднего числа. Здесь 20, потому что есть два числа больше (49 и 65) и два числа меньше (4 и 12).

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

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

Как показано в приведенных выше примерах, функция MEDIAN игнорирует пустые ячейки, но не ячейки, содержащие нулевое значение.

Медиана изменяется между первым и вторым примерами, потому что в ячейку A3 был добавлен ноль, а ячейка A2 пуста.
Добавление нуля к ячейке A3 изменяет число аргументов, передаваемых функции в ячейке G3, с пяти до шести — четное число. В результате медиана рассчитывается путем сложения двух средних значений (12 и 20) вместе, а затем деления на два, чтобы найти их среднее значение (16).

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

Этот параметр нельзя отключить в Excel Online.

Как включить или отключить этот параметр в Excel 2019, Excel 2016, Excel 2013 и Excel 2010 :

Как включить и отключить этот параметр в Excel 2019 для Mac, Excel 2016 для Mac и Excel для Mac 2011 :

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

Вычислить медиану в диапазоне

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

Вкладка Office Включите редактирование и просмотр с вкладками в Office и сделайте вашу работу намного проще. ..

Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное , и быстро использовать их в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Вычислить медианное значение в диапазоне

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!

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

Выберите пустую ячейку и введите эту формулу = МЕДИАНА (A1: C6) (A1: C6 указывает диапазон, из которого вы хотите вычислить медиану), нажмите клавишу Enter , а затем вы можно получить медианное значение в ячейке.

Совет . Если диапазон данных не является непрерывным, вы можете использовать эту формулу = МЕДИАНА (A1: C3, E1: G3) (A1: C3 и E1: G3 являются диапазоны, по которым вы хотите вычислить медиану, а запятая означает и. Если у вас есть три прерывистых диапазона, вы можете использовать формулу = МЕДИАНА (A1: C3, E1: G3, A5: D8)) .

Вычислить медиану, исключая ноль в диапазоне

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

Выберите пустую ячейку и введите эту формулу = MEDIAN (IF (A1: C6> 0, A1: C6)) (A1: C6 указывает диапазон, из которого вы хотите вычислить медиану), одновременно нажмите клавиши Ctrl + Shift + Enter , после чего вы можете получить медианное значение без нуля в ячейке.

Этот вопрос возникает раз в десять лет: можете ли вы вычислить медианное значение в сводной таблице. Традиционно ответ был отрицательным. Я помню, как в 2000 году я нанял Excel MVP Хуана Пабло Гонсалеса для написания потрясающего макроса, который создавал отчеты, похожие на сводные таблицы, но с медианами.

Что нужно для использования DAX в сводной таблице? Вам потребуется версия Excel для Windows с Excel 2013 или новее.

Вот мой очень простой набор данных, который я буду использовать, чтобы проверить, как рассчитываются медианы сводной таблицы. Вы можете видеть, что медиана для Чикаго должна быть 5000, а медиана для Кливленда должна быть 17000. В случае Чикаго существует пять значений, поэтому медиана будет 3-м по величине значением. Но для всего набора данных есть 12 значений. Это означает, что медиана находится где-то между 11000 и 13000. Excel усредняет эти два значения и возвращает 12000.

Рисунок 1

Для начала выделите одну ячейку в данных и нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы.

фигура 2

Рисунок 3

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

Рисунок 5

Смотреть видео

Стенограмма видео

Изучите Excel из подкаста, эпизод 2197: медиана в сводной таблице.

А теперь проверим. Итак, здесь, весь Средний Запад, медиана всех наборов данных от 11000 до 13000. Таким образом, это среднее значение 11 и 13, что в точности соответствует среднему значению в обычном Excel. Теперь давайте добавим округ, и он говорит, что среднее значение 5000 в Чикаго, среднее значение для Кливленда - 17000; Всего на Среднем Западе и в целом 12000. Все это правильно. Как это круто? Наконец, медиана в сводной таблице благодаря вычисляемому полю или мере, как ее называют, и модели данных.

А теперь многие из моих любимых советов - советы для моего семинара по Power Excel - в этой книге LIVe, 54 величайших совета всех времен. Щелкните "I" в правом верхнем углу, чтобы узнать больше о книге.

Хорошо. Подведение итогов: можете ли вы вычислить медианное значение в сводной таблице? О нет, да, да, вы можете, благодаря модели данных. Вы можете создать медиану; Ctrl + T, чтобы превратить ваши данные в таблицу; Вставить сводную таблицу; и установите этот флажок, Добавить эти данные в модель данных; щелкните правой кнопкой мыши имя таблицы и выберите новую меру. Мера будет = МЕДИАНА ((Продажи)). Это круто.

Спасибо Алексу за то, что он появился на моем семинаре и задал этот вопрос, спасибо, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.

Читайте также: