Как быстро рассчитать перцентиль или квартиль в Excel, игнорируя нулевые значения?
При использовании функций ПЕРСЕНТИЛЬ или КВАРТИЛЬ в Excel пользователи нередко сталкиваются с тем, что их диапазон данных содержит нулевые значения. По умолчанию эти функции учитывают нули в расчётах, что может существенно исказить результаты — особенно если ноль не несёт смысловой нагрузки в данном контексте, занижая перцентили или квартили. Для более точного статистического анализа вы можете полностью исключить нулевые значения из расчётов. В этом руководстве представлены несколько практичных методов решения этой задачи в Excel: от встроенных формул и решений на VBA до рекомендаций по выбору оптимального подхода под ваши конкретные потребности.
ПЕРСЕНТИЛЬ или КВАРТИЛЬ без учёта нулей
ПЕРСЕНТИЛЬ без учёта нулей (формула массива)
Чтобы рассчитать перцентиль, игнорируя нули, используйте формулу массива, учитывающую только значения больше нуля.
Выберите пустую ячейку для вывода результата и введите следующую формулу:
=PERCENTILE(IF(A1:A13>,0,A1:A13),0.3)После ввода формулы нажмите Ctrl + Shift + Enter (а не просто Enter), так как это формула массива. Excel автоматически заключит формулу в фигурные скобки { }, что означает её корректный ввод. В этой формуле:
- A1:A13 — это ваш диапазон данных; при необходимости скорректируйте его под свой лист.
- 0,3 задаёт 30 -йперцентиль. Вы можете изменить это значение на любой требуемый перцентиль (например, 0,75 для 75)-го перцентиля).
Этот метод особенно полезен, когда нужно исключить влияние нулей — например, пропущенных или недействительных измерений — на статистические результаты.
Имейте в виду: простое нажатие клавиши Enter не сработает — обязательно используйте Ctrl + Shift + Enter. Кроме того, формулы с IF(...) внутри агрегатных функций могут работать менее эффективно с большими наборами данных.
КВАРТИЛЬ без учёта нулей (формула массива)
Аналогичный подход используется и для квартилей. Выберите ячейку для результата и введите:
=QUARTILE(IF(A1:A18>,0,A1:A18),1)После ввода формулы нажмите Ctrl + Shift + Enter, чтобы подтвердить её как формулу массива.
- A1:A18 — это выбранный диапазон данных (при необходимости измените его).
- 1означает, что вам нужен первый квартиль (25)-й перцентиль). Вместо этого вы можете указать 2 для медианы или 3 для третьего квартиля (75 -й перцентиль).
Убедитесь, что ваш диапазон данных не содержит текстовых значений или ошибок — формула работает только с числами. Это решение идеально подходит для наборов данных умеренного размера, когда нужен быстрый расчёт без использования VBA или надстроек.
Макрос VBA для фильтрации и расчёта перцентиля/квартиля с исключением нулей
Вы также можете использовать VBA (Visual Basic for Applications) для автоматической фильтрации Нулевые значения и последующего расчёта перцентиля или квартиля по оставшимся данным. Такой подход особенно удобен при работе с большими наборами данных или когда процедуру необходимо повторять регулярно без ручного ввода формул.
Применимые сценарии: Идеально подходит опытным пользователям для выполнения повторяющихся задач или работы со сложными диапазонами. Настроив код, вы сможете указывать любой индекс перцентиля или квартиля и любую область данных.
1. Перейдите на вкладку Разработчик в Excel. Если она не отображается, щёлкните правой кнопкой мыши по ленте, выберите Настроить ленту и установите флажок напротив пункта Разработчик. Затем нажмите Разработчик > Visual Basic.
2. В окне Microsoft Visual Basic for Applications нажмите Вставка > Модуль.
3. Скопируйте и вставьте следующий код VBA в модуль:
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Нажмите кнопку 
или клавишу F5в окне VBA, чтобы запустить макрос. Сначала появится запрос на выбор диапазона данных (только числа), затем — на указание нужного перцентиля (например, 0,3 для 30)-го перцентиля) и индекса квартиля (например, 1 для первого квартиля). Макрос автоматически отфильтрует нулевые значения и покажет результат во всплывающем окне.
Преимущества: Быстро обрабатывает большие и нестандартные наборы данных, полностью исключает пустые значения и избавляет от необходимости вручную прописывать формулы. Поддерживает многократное использование и гибкую настройку.
Недостатки: Требует включения макросов и базового понимания VBA. Не подходит для использования в формулах листа без преобразования в пользовательскую функцию (UDF).
Типичные проблемы и устранение неполадок: если вы выберете нечисловые ячейки или ячейки с ошибками, макрос может пропустить их или выдать сообщение об ошибке. Убедитесь, что выбранный диапазон данных содержит только числа — нули и положительные значения. Если ненулевые данные не будут найдены, вы получите соответствующее уведомление.
Советы: вы можете дополнительно настроить код VBA для вывода результата в определённую ячейку листа, изменения расчётных функций или автоматизации обработки нескольких диапазонов. Всегда сохраняйте книгу перед запуском или редактированием макросов, чтобы избежать случайной потери данных.
Если вам нужно рассчитывать перцентили или квартили сразу в нескольких столбцах, подумайте о модификации макроса — добавьте цикл по нужным столбцам или диапазонам.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек