Как посчитать медиану в видимых ячейках только в Excel？

Разгоняем медиану в OLAP

Этот пост для тех, кто сталкивался с проблемой производительности, при расчете медианы в OLAP кубе.
Одним из главных достоинств OLAP технологии является скорость получения результатов при обращении к базе. Расчеты происходят «на лету». Однако с медианой, не все так просто.
Для справки: медиана — вид средней. Это величина, которая находиться в середине ряда значений отсортированного по возрастанию. Например, для ряда значений <1, 2, 5, 6, 9>медианой является 5.

Рассмотрим ситуацию на примере OLAP сервера от Microsoft — SSAS 2008 (SQL Server Analysis Services).
Для расчета медианы SSAS предлагает использовать MDX функцию Median. С ее помощью вы можете создать вычисляемую меру (Calculated Member) и использовать ее в расчетах.

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

Рассмотрим пример отчета:

Для категории вакансий «разработчик 1С» в 2011 году было найдено 8 354 вакансии. Чтобы рассчитать медианную зарплату для этой должности, сервер должен сделать следующее: получить все значения из этой ячейки отчета, отсортировать их по значению зарплаты и определить значение в середине ряда значений (кортежа). И так далее для каждой ячейки. Это увеличивает время, необходимое для создания отчета. Большая часть времени уходит на сортировку.

Решение

Значения для расчета медианы выбираются из таблицы фактов хранилища данных, на основе которой создается OLAP-куб. Что если мы предположим, что таблица фактов отсортирована по значениям зарплаты. Тогда нам не нужно сортировать значения для каждой ячейки. Нам просто нужно определить для каждой ячейки количество элементов и вычислить номер элемента, который находится в середине ряда. Значением этого элемента будет медиана.

Код, создающий член перечня:

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

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

Пример SQL запроса:

При каждом обновлении OLAP-куба необходимо выполнять эту операцию. Этот метод, однако, является чрезвычайно трудоемким при работе с большими объемами данных. Тем не менее, метод вполне приемлем для относительно небольших объемов. Таким же образом можно вычислить перцентили и квартили.

Удалить (удалить) пустые строки в Excel

Давайте рассмотрим, как удалить пустые строки в Excel на конкретных примерах.

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

1. Ручной способ удаления пустых строк с минимальным количеством пустых строк.
2. Найти пустые строки и удалить
3. Определить пустые строки с помощью формулы и удалить
4. Использовать фильтр для поиска пустого места и удалить.

# 1 — Вручную удалить (удалить) пустые строки в Excel

В Excel удаление пустых строк относительно простое, если мы работаем с небольшим количеством данных.

Как вы можете видеть ниже, во всех моих данных было только три пустых строки.

В этих случаях редактирование Excel не требует особых навыков.

• Шаг 1: Выделите всю пустую линию с помощью Shift + Пробел.

Примечание. Shift + Пробел — это сочетание клавиш для выбора всей строки.

• Шаг 2: После выделения всей строки мы можем использовать несколько вариантов удаления пустой строки в Excel.

Вариант 1: Найдите опцию удаления на вкладке «Главная

Как только вы нажимаете кнопку Удалить строки листа, вся выделенная пустая строка в Excel удаляется.

Вариант 2: Щелкните правой кнопкой мыши выбранную строку и нажмите кнопку Удалить. Нажатие кнопки Delete приведет к удалению выделенной пустой строки в Excel.

Вариант 3: Теперь идет мать всех вариантов. После выбора всей строки вы можете нажать Ctrl —

Примечание: Ctrl + Минус — это сочетание клавиш Excel для удаления целой пустой строки.

Нажатие комбинации клавиш в Excel устранит всю пустую строку.

Теперь я удалил одну строку. Осталось удалить две пустые строки. Перейдите к следующей пустой строке и нажмите Shift + Пробел, чтобы выделить всю строку.

Вот какой прием нужно использовать. Мы можем удалить всю пустую строку в Excel, используя вышеуказанные настройки, как мы видели в предыдущем примере. Однако мы можем удалить этот пустой ряд, просто нажав клавишу F4.

В F4 актуальность — самое главное. С помощью этой кнопки можно вызвать ранее выполненное действие. Можете ли вы сейчас вспомнить, какое действие вы выполняли ранее?

Да! Предыдущее действие действительно удалило первую пустую строку.

Нажмите F4 на пустой строке, чтобы начать.

Теперь проделайте то же самое с последней пустой строчкой.

Итак, я познакомил вас с техникой удаления пустых строк; теперь вам предстоит решить, какой из них использовать.

# 2 — Найдите пустые и удалите (удалите) пустые строки в Excel

В предыдущем примере мы удалили эти три пустые строки вручную. Ничего страшного, если у нас будут сотни пустых строк?

Конечно же, мы не можем использовать этот ручной метод правильно.

Мы можем сначала найти пустые строки и затем удалить их.

Поиск бланка — вот что вам здесь нужно знать. Следуйте этому примеру, чтобы найти способ.

У меня есть следующие данные, с которыми нужно работать.

Упс, у меня тут много пустых мест. Чтобы освоить этот метод, выполните следующие действия.

• Шаг 1: Начните с выделения всех данных
• Шаг 2: Теперь нажмите Ctrl + G (клавиша быстрого доступа для опции Go To). Откроется окно, показанное ниже.

• Шаг 3: Теперь нажмите на специальную опцию. (Можно использовать сочетание клавиш Alt + S).

• Шаг 4: После выбора пункта Special открывается окно ниже, в котором выбираются заготовки. Вы можете ввести K в качестве сочетания клавиш.

• Шаг 5: После того, как вы выберете места, нажмите OK. При этом будут выбраны все пространства в выбранном регионе.

• Шаг 6: Теперь нажмите сочетание клавиш Ctrl -. Это откроет окно ниже и выберите Целый ряд.

• Шаг 7: Нажмите «OK» для удаления всех пустых строк.

# 3 — Найдите пустое поле с помощью фильтра и удалите пустые строки в Excel

Возьмем тот же пример из предыдущего примера.

• Шаг 1: выберите данные и примените фильтр.

• Шаг 2: Нажмите на один из фильтров и выберите только пустые места.

• Шаг 3: После того, как вы выбрали заготовки, нажмите «OK». Это позволит отфильтровать только пустые строки.

• Шаг 4: Выделите все отфильтрованные строки и нажмите Alt + ;

Предупреждение. Когда мы фильтруем пустые строки, мы не можем просто удалить их, потому что под этой ячейкой находится много ячеек. Мы должны выбрать только те ячейки, которые видны. Нажмите Alt+; это быстрая клавиша для выбора «Только видимые ячейки».

• Шаг 5: После выделения видимых ячеек, вы можете нажать Ctrl -.

• Шаг 6: Теперь снимите фильтр. Мы удалили все пустые строки.

# 4 — Найдите пустое поле с помощью формулы и удалите пустые строки в Excel

Когда я читаю строку, как я узнаю, пуста ли она?

Ответ: Если строка не содержит данных, мы можем назвать ее пустой строкой.

Возьмите тот же пример, что и выше, и вставьте новый столбец с именем count.

• Шаг 1: Примените формулу COUNT и вычислите значения строк.

Функция подсчета в Excel считает все входящие данные; это метод.

Если COUNT что-то находит, он возвращает количество найденного, в противном случае он возвращает результат как ноль.