Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как ранжировать данные в Excel по вхождению?

Глава 16. SUMX() и другие X функции (итераторы)

Это продолжение перевода книги Роба Колли. Формулы DAX для Power Pivot. Главы не являются самостоятельными, поэтому я рекомендую читать их по порядку.

Ранее мы вводили показатель [Продажи в день]:

Ris. 16.1. Promezhutochnye itogi ne sootvetstvuyut summe vhodyashhih v nih chastej

Рис. 16.1. Промежуточные итоги не соответствуют сумме своих частей

Скачать заметку в формате Word или PDF, примеры в формате Excel

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

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

Функция SUMX() имеет два аргумента (Рисунок 16.2):

SUMX(

; )

Ris. 16.2. Sintaksis funktsii SUMX

Рис. 16.2. Синтаксис функции SUMX()

Функция SUMX() действует следующим образом:

  1. Он просматривает каждую строку таблицы, беря по одной строке за раз. Вы можете передать имя таблицы в качестве первого аргумента или использовать функцию, возвращающую таблицу, например, VALUES() или FILTER(). Содержимое таблицы зависит от контекста фильтрации текущей операции с ячейкой. Такое инкрементное поведение часто описывается как перечисление или итерация.
  2. Для каждой строки вычисляется арифметическое выражение с использованием контекста фильтра текущей строки.
  3. Функция сохраняет результат арифметического выражения из каждой строки и возвращает сумму арифметических выражений после обхода всех строк.

SUMX() в действии

Вернувшись к рис. 1.6.1, мы создадим новую меру:

Обратите внимание, что мы использовали VALUES(Products[ModelName]) для аргумента

. Это позволяет нам быть очень точными — мы хотим, чтобы SUMX() обошел все уникальные значения ModelName в текущем контексте фильтра. С другой стороны, если мы укажем всю таблицу Products (и не будем использовать функцию VALUES), SUMX() обойдет все строки таблицы Products из текущего контекста фильтра. В первом и втором случаях мы получим разное количество строк в аргументе

.

Ris. 16.3. Dlya novoj mery znachenie itogov ravno summe znachenij dlya otdelnyh modelej

Рисунок 16.3: Для новой меры значение суммы равно сумме значений индивидуальных моделей

Остановимся на работе функции SUMX() подробнее

Сумма $21 600 на рисунке 16.3 должна получиться другой, исходя из трех вышеприведенных пунктов. Поскольку ячейка D3 имеет фильтры: Year=2001, Products=All, таблица Products в данном случае совершенно не фильтруется. Таким образом, VALUES(Products[ModelName]) возвращает каждое уникальное значение столбца [ModelName] из таблицы Products. Таких значений 119. Давайте проверим, введя показатель

[ModelName Values] = COUNTROWS(VALUES(Products[ModelName]))

Ris. 16.4. Est 119 razlichnyh modelej hotya na ris. 16.3 predstavleny tolko 3

Рисунок 16.4. Существует 119 различных моделей, хотя на рисунке 16.3 показаны только три; чтобы увеличить изображение, щелкните правой кнопкой мыши и выберите Открыть изображение в новой вкладке.

Почему 119 против 3? Всего существует 119 моделей, но только три из них имели продажи в 2001 г. Т.е. только три модели возвращают непустые результаты для [Продажи в день], что и показывает сводка на рис. 16.3.

На втором этапе SUMX() оценивает показатель [Продажи в день] для каждого из этих 119 значений. На протяжении всего процесса контекст фильтра Год=2001 сохраняется для каждой строки. Тем не менее, Products[ModelName] меняет свой контекст фильтра каждый раз, когда SUMX() переходит к следующей строке из 119 значений. Другими словами, SUMX() сначала оценивает [Продажи в день] с контекстом фильтра Год=2001, ModelName=» Универсальная подставка для велосипеда «, и возвращает ноль, поскольку в 2001 году не было продаж этой модели. После этого SUMX() переходит к Year=2001, ModelName= » Bike Wash «, и так далее для всех 119 моделей. Для каждого из этих продуктов в VALUES(Products[ModelName]) есть только три строки, которые возвращают непустые значения для [Продажи в день], и это те три, которые мы видели на рисунке 16.3: Mountain-100, Road-150 и Road-650.

В результате SUMX() суммирует все 119 результатов для [Продажи в день]: 116 пустых значений и три непустых, всего $21 600.

MINX(), MAXX() или AVERAGEX()

Три другие функции делают то же самое, что и SUMX(). Единственное отличие заключается в том, что вместо сложения всех результатов, возвращаемых каждым шагом, они применяют агрегирование: MIN(), MAX() или AVERAGE().

FILTER()

Функцию FILTER следует называть FILTERX, поскольку она принадлежит к семейству функций X. Основное отличие заключается в том, что фильтр возвращает массив, а не одно значение, как SUMX (или другие функции X). Однако их принципы работы и синтаксис схожи:

СОММЭ ( ; )

FILTER(

; )

Обе функции проходят через массив (первый аргумент) строка за строкой. Затем вычисляется SUMX ), а FILTER вычисляет для каждой строки. Наконец, SUMX суммирует все значения, вычисленные в каждой строке, а FILTER собирает все строки таблицы, удовлетворяющие , и возвращает их в виде таблицы.

Надеемся, вы видите сходство.

STDEVX.P(), STDEVX.S(), VARX.P(), VARX.S()

Разница между версиями P и S точно такая же, как между СТАНДОТКЛОН.G() и СТАНДОТКЛОН.B() в обычном Excel. Версия P используется, когда набор данных представляет собой генеральную совокупность, а функция S — когда имеется только выборка данных. Это вопрос статистики, а не DAX (см. STANDOTCLON.B и STANDOTCLON.D: в чем разница? ).

COUNTX() и COUNTAX()

Возвращаясь к нашему примеру с SUMX(): SUMX(VALUES(Products[ModelName]); [Продажи в день]). Было 119 уникальных значений ModelName, из которых только 3 имели непустые значения для [Продажи в день]. Если мы заменим SUMX() на COUNTX(), то получим 3, потому что COUNTX() не учитывает пустые значения. Поэтому мы можем думать о COUNTX() как о COUNT_ONBLANKX(). Чем же функция COUNTX() отличается от COUNTROWS()? Функция COUNTROWS() не может принимать меру в качестве аргумента, поэтому ее нельзя использовать для оценки того, сколько раз мера возвращала непустое значение.

COUNTAX() в отличие от COUNTX() считает значимыми пустые текстовые строки =»».

Использование функций X для полей, которые не отображаются

Выше мы использовали SUMX() для «правильного» суммирования итогов. Функции X также можно использовать для перечисления полей, которые не отображаются в итоговой таблице. Чтобы проиллюстрировать это, давайте добавим новый показатель: [Максимальные продажи в одной стране] = MAXX(VALUES(SalesTerritory[Country]); [Total Sales])

Ris. 16.5. Vy uvereny chto ponimaete chto imenno otobrazhaet novaya mera

Рисунок 16.5. Вы уверены, что понимаете, что именно представляет собой новая мера?

Добавим страну в перекрестную таблицу

Ris. 16.6. Mera Max Single Country Sales vozvrashhaet maksimalnye prodazhi v odnoj strane s uchetom konteksta filtra

Рисунок 16.6 — Мера [Max Single-Country Sales] возвращает максимальный объем продаж для одной страны, учитывая контекст фильтра.

Значение в ячейке E3 равно сумме значений в ячейках E5, E12 и E19. Ячейка E3 имеет следующий контекст фильтрации: Год=2001, Продукты=Все, что создает максимальный объем продаж в стране для всех моделей. Посмотрите еще раз на рис. 16.5. В сводной таблице поле Страна отсутствует, но значения в столбце E соответствуют только одной стране.

RANKX()

Используем меру [Customer Sales Rank] = RANKX(ALL(Customers[FullName]); [Total Sales])

Ris. 16.7. Rang po summe prodazh

Рис. 16.7. Ранжирование по объему продаж

Единственное отличие от других функций X заключается в том, что в первом аргументе мы использовали ALL() вместо VALUES(). VALUES() возвращает уникальное значение Customer[FullName] для каждого вхождения, поэтому RANKX идентифицирует каждого клиента так, как будто он единственный в мире. В то же время, ALL() ранжирует каждого клиента среди всех остальных.

Примечание в нижней части обзора:

Ris. 16.8. Esli imeetsya neskolko odinakovyh znachenij sleduyushhij rang otstoit na chislo takih povtorenij

Рисунок 16.8. Если есть несколько одинаковых значений (здесь это $4), то следующий ранг отделяется по количеству таких повторений

Если это вам не подходит, выход есть. Функция RANKX() на самом деле имеет пять параметров (последние три являются необязательными):

Ris. 16.9. Neobyazatelnye parametry funktsii RANKX

Рисунок 16.9. Необязательные параметры функции RANKX()

Вероятно, самым загадочным параметром в Power Pivot является параметр [Value]. Мы рекомендуем всегда оставлять его пустым (мы рассмотрим его еще раз в главе 18). Ранги можно упорядочить, чтобы показать порядок убывания (значение параметра 0, DESC или пусто) или чтобы показать порядок возрастания (значение 1 или ASC). Используя функцию рангов, [Equal] указывает, как расположить одинаковые значения плотно, пропустить количество рангов, равное количеству повторений (0, Skip или Empty), и расположить ранги плотно (1 или Dense):

=RANKX(ALL(Customers[FullName]); [Total Sales];;;Dense)

Ris. 16.10. Plotnoe raspolozhenie rangov v sluchae esli ParametrDense

Рис. 16.10. «Плотное» расположение рангов в случае, когда [Equals]=Dense

Если у вас есть два клиента с одинаковым полным именем, они будут объединены в одного клиента и отсортированы несправедливо. Поэтому обязательно сортируйте по одному полю. Мы рекомендуем объединить CustomerKey или что-то уникальное с полным именем, чтобы иметь возможность распознать клиента по имени и сохранить уникальность.

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

T OPN() не является X-функцией, но она очень близка к RANKX, поэтому она будет рассмотрена здесь.

Ris. 16.11. Sintaksis funktsii TOPN

Рис. 16.11. Синтаксис функции TOPN()

Функция возвращает N верхних строк указанной таблицы. RANKX присваивает числовой ранг. В TOPN ранжируется каждая строка. Затем она фильтруется по N верхним строкам (N задается пользователем), и этот набор возвращается. TOPN помогает нам отфильтровать несколько верхних строк (например, самые популярные товары, лучшие клиенты, лучшие продавцы и т.д.). а затем произвести с ними вычисления. Давайте обратимся к примеру. Ниже приведен объем продаж и количество продуктов, проданных в 2002 году:

Ris. 16.12. Obem prodazh dovolno stabilen ot mesyatsa k mesyatsu

Рисунок 16.12. Объем продаж довольно стабилен из месяца в месяц.

Чтобы выяснить, какой вклад в продажи вносят пять самых продаваемых товаров (в разные месяцы разный). Давайте создадим меру:

Как ранжировать список по возрастанию в Excel

Пример 1: Имеется документ, содержащий имена студентов и их общие оценки за семестр. Вы хотите отсортировать студентов по их оценкам.

Пример 1.

Мы используем формулу =RANG(B2;$B$2:$B$7;0) для ячейки C2, формулу =RANG.RV(B2;$B$2:$B$7;0) для ячейки D2 и формулу =RANG.SR(B2;$B$2:$B$7;0) для ячейки E2. Разверните все формулы в ячейки ниже.

Как ранжировать список.

Итак, мы видим, что ранжирование по RANG() и RANG. RV() не различаются: есть два студента, занявших второе место, нет третьего, и есть два студента, занявших четвертое, нет пятого. Ранжирование определялось по максимально возможному выбору.

В то же время функция RANG.SR() присваивала совпавшим студентам среднее значение мест, которые они заняли бы, если бы сумма баллов отличалась, например, на один балл. Для второго и третьего мест средний показатель составляет 2,5; для четвертого и пятого мест — 4,5.

Шаг 2. Вооружение

На основе проведенного анализа составляется семантическое ядро — список запросов, по которым будет продвигаться сайт. Разумнее использовать проверенные временем инструменты, чем тратить время на ручной сбор ключевых слов. (Мы используем Just Magic).

Наш подход к Just Magic: Прежде всего, мы получили токены запросов (например: «водонагреватели», «котлы», «полотенцесушители», «радиаторы») и указали их в файле Excel вместе с планируемыми целевыми страницами для удобства сортировки.

После этого мы начали собирать семантическое ядро (раздел «Сервисы», вкладка «Маркеры»). Назначив имя задания, выбрав базу данных и режим работы, обозначив порог привязки и прикрепив файл с маркерами, мы смогли его выполнить.

Сервис прислал нам письмо со ссылкой на результаты — собранное семантическое ядро с ранее указанными группами и страницами.

Здесь важно отфильтровать ненужные запросы и проверить на wordstat.yandex.ru по точному вхождению («!word !query»).

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

Служба «Hint Parser» также собирает подсказки в Just Magic.

Как только программа запускалась, она расширяла маску основного запроса («радиаторы», «радиаторы», «обогреватели») словами «цены», «купить», «стоимость» и «в Москве». После получения ядра мы провели ручную очистку, а также согласовали ее с клиентом.

После этого мы распределили ее по страницам, предварительно разбив по группам:

  • Биметаллические радиаторы; биметаллические радиаторы
  • Купить; биметаллические радиаторы
  • Цена; биметаллические радиаторы
  • В Москве; биметаллические радиаторы
  • Стоимость; биметаллические радиаторы
  • Дешево.

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

Ранжируйте данные по вхождению с помощью Kutools for Excel’s Advanced Sortхорошая идея3

Ранжируйте данные по вхождению с помощью формулы

Чтобы отсортировать данные по встречаемости, сначала подсчитайте, сколько раз они появляются, затем отсортируйте встречаемость.

1. Скопируйте и вставьте список данных в другое место и нажмите Данные > Удалить дубликаты. Смотрите скриншот:
рейтинг документа по вхождению 2

2. Вы можете закрыть диалоговые окна, нажав OK > OK. См. снимок экрана:

рейтинг документа по вхождению 3стрелка вправорейтинг документа по вхождению 4

Теперь уникальные ссылки извлечены. См. скриншот:

рейтинг документа по вхождению 5стрелка вправорейтинг документа по вхождению 6

3. В следующем столбце извлеченных данных, например, E2, введите эту формулу. = СЧЁТЕСЛИ ($ A $ 2: $ A $ 12; D2) чтобы подсчитать, сколько раз все данные появляются в исходном списке, нажмите Перейти и перетащите маркер заполнения вниз, чтобы применить эту формулу к ячейкам.
рейтинг документа по вхождению 7

В формуле A2: A12 — исходный список данных, который вы используете, D2 — ссылка, которую вы подсчитываете.

4. Затем в следующем столбце подсчитанных данных F2 и введите эту формулу. = РАНГ (E2; $ E $ 2: $ E $ 6) , Нажмите Перейти и перетащите дескриптор заполнения вниз, чтобы вычислить рейтинг. Смотрите скриншот:
рейтинг документа по вхождению 8

В формуле E2 — это ячейка, для которой вы хотите вычислить результат, а E2:E6 — используемый диапазон.

Ранжируйте данные по вхождению с помощью Kutools for Excel’s Advanced Sort

Например, если вы хотите отсортировать данные по количеству вхождений в исходное место, вы можете использовать Kutools for ExcelAdvanced Sorting Utility (Утилита расширенной сортировки).

Чтобы установить Kutools for Excel, выполните следующие действия: (Получите Kutools for Excel бесплатно прямо сейчас!)

1. Выберите данные, которые вы используете, и нажмите Кутулс Плюс > Сортировать > Расширенная сортировка. Смотрите скриншот:
рейтинг документа по вхождению 9

2. Затем в Расширенная сортировка диалоговом окне выберите столбец, который вы хотите ранжировать и отсортировать, выберите частота из Сортировать по раскрывающийся список и укажите порядок сортировки. Смотрите скриншот:
рейтинг документа по вхождению 10

3. Нажмите OK, данные будут отсортированы по частоте, от самой низкой до самой высокой.

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

Главное правило — контент для людей. Текст должен быть информативным, экспертным и легко читаемым.

Как я уже говорил, не существует точного и универсального процента встречаемости ключевых слов в тексте.

Важно придерживаться золотой середины: избегать как переизбытка ключей в тексте, так и недостаточного их количества. Если плотность слишком высокая, убирайте повторяющиеся запросы и/или заменяйте синонимами, словоформами. Так вы сможете повысить релевантность и ценность текста без переспама. Для подбора синонимов подойдет функционал Serpstat «Похожие фразы».

серпстат похожие фразы

Лучший способ органически регулировать показатель плотности — повысить объем контента в целом. Если ключевое слово указано 30 раз в документе из 2000 слов, оно будет выглядеть естественнее, чем ключевое слово, которое встречается 10 раз в тексте из 300 слов.

Сочетание прямых и разбавленных случаев использования ключей в тексте.

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

голоса
Рейтинг статьи
Читайте так же:
Как разрешить ввод только буквенно-цифровых символов в Excel?
Ссылка на основную публикацию
Adblock
detector