Как сделать медиану в excel
Если память служит, агрегатные функции 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, чтобы превратить ваши данные в таблицу; Вставить сводную таблицу; и установите этот флажок, Добавить эти данные в модель данных; щелкните правой кнопкой мыши имя таблицы и выберите новую меру. Мера будет = МЕДИАНА ((Продажи)). Это круто.
Спасибо Алексу за то, что он появился на моем семинаре и задал этот вопрос, спасибо, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Читайте также: