Как свести уникальные значения из массива в лист? - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Как свести уникальные значения из массива в лист? - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: _Boroda_, китин

Как свести уникальные значения из массива в лист? (Формулы/Formulas)

Как свести уникальные значения из массива в лист?

Дата: Среда, 20.03.2024, 12:11 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Гугл таблицы

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

В одном и том же сообществе могло быть размещено два разных креатива со своими названиями, нужно отследить эффективность сообщества

На листе "Сводная" сделать так, чтобы каждое уникальное сообщество со своим ID отражалось на строчке, а в столбцах C:I подгружались и суммировались все значения из листа "Общая".

https://docs.google.com/spreads....sharing

Какая формула поможет это реализовать?

Ответить

Дата: Среда, 20.03.2024, 15:01 | Сообщение № 2

Группа: Админы

Ранг: Участник клуба

Сообщений: 2796

Репутация: 1160 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата keygarden16, 20.03.2024 в 12:11, в сообщении № 1 (

)

Какая формула поможет это реализовать?

Думаю, можно запрос с функцией QUERY написать, но мне интересно лишний раз с функциями LET и LAMBDA повозиться. Поэтому вот моя формула для ячейки A2 листа "Сводная":
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;
уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));
{уник\суммы}
)

[/vba]
Вводите ее в эту единственную ячейку A2 и наслаждаетесь.

[p.s.]Да, кстати! У Вас там есть такая ситуация с разным регистром написания паблика:[/p.s.]
[vba]

Код

НОВАЯ МУЗЫКА 28446706
Новая музыка 28446706

[/vba]
С точки зрения функции UNIQUE - это разные строки, а с точки зрения функции SUMIFS - одинаковые. Для преодоления этой проблемы была накручена такая конструкция:
[vba]

Код

LOWER(INDEX(пабл;;1))

[/vba]
и сами наименования в итоговой таблице оказались в нижнем регистре. Чтобы это исправить, нужно "Новую музыку" прописать везде в одинаковом виде и тогда можно убрать функцию LOWER:
[vba]

Код

INDEX(пабл;;1)

[/vba]
После этого наименования пабликов на листе "Сводная" будут в привычном виде - как на листе "Общая".

Цитата keygarden16, 20.03.2024 в 12:11, в сообщении № 1 (

)

Какая формула поможет это реализовать?

Думаю, можно запрос с функцией QUERY написать, но мне интересно лишний раз с функциями LET и LAMBDA повозиться. Поэтому вот моя формула для ячейки A2 листа "Сводная":
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;
уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));
{уник\суммы}
)

[/vba]
Вводите ее в эту единственную ячейку A2 и наслаждаетесь.

[p.s.]Да, кстати! У Вас там есть такая ситуация с разным регистром написания паблика:[/p.s.]
[vba]

Код

НОВАЯ МУЗЫКА 28446706
Новая музыка 28446706

[/vba]
С точки зрения функции UNIQUE - это разные строки, а с точки зрения функции SUMIFS - одинаковые. Для преодоления этой проблемы была накручена такая конструкция:
[vba]

Код

LOWER(INDEX(пабл;;1))

[/vba]
и сами наименования в итоговой таблице оказались в нижнем регистре. Чтобы это исправить, нужно "Новую музыку" прописать везде в одинаковом виде и тогда можно убрать функцию LOWER:
[vba]

Код

INDEX(пабл;;1)

[/vba]
После этого наименования пабликов на листе "Сводная" будут в привычном виде - как на листе "Общая".Gustav

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 20.03.2024, 15:15

Ответить

Сообщение

Цитата keygarden16, 20.03.2024 в 12:11, в сообщении № 1 (

)

Какая формула поможет это реализовать?

Думаю, можно запрос с функцией QUERY написать, но мне интересно лишний раз с функциями LET и LAMBDA повозиться. Поэтому вот моя формула для ячейки A2 листа "Сводная":
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;
уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));
{уник\суммы}
)

[/vba]
Вводите ее в эту единственную ячейку A2 и наслаждаетесь.

[p.s.]Да, кстати! У Вас там есть такая ситуация с разным регистром написания паблика:[/p.s.]
[vba]

Код

НОВАЯ МУЗЫКА 28446706
Новая музыка 28446706

[/vba]
С точки зрения функции UNIQUE - это разные строки, а с точки зрения функции SUMIFS - одинаковые. Для преодоления этой проблемы была накручена такая конструкция:
[vba]

Код

LOWER(INDEX(пабл;;1))

[/vba]
и сами наименования в итоговой таблице оказались в нижнем регистре. Чтобы это исправить, нужно "Новую музыку" прописать везде в одинаковом виде и тогда можно убрать функцию LOWER:
[vba]

Код

INDEX(пабл;;1)

[/vba]
После этого наименования пабликов на листе "Сводная" будут в привычном виде - как на листе "Общая".

Автор - Gustav
Дата добавления - 20.03.2024 в 15:01

Дата: Среда, 20.03.2024, 15:44 | Сообщение № 3

Группа: Админы

Ранг: Участник клуба

Сообщений: 2796

Репутация: 1160 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата Gustav, 20.03.2024 в 15:01, в сообщении № 2 (

)

Для преодоления этой проблемы была накручена такая конструкция:
LOWER(INDEX(пабл;;1))
и сами наименования в итоговой таблице оказались в нижнем регистре. Чтобы это исправить, нужно "Новую музыку" прописать везде в одинаковом виде

Либо можно перед самым отображением результатов вернуть первоначальный регистр написания паблика, получив с помощью функции XLOOKUP "оригинальное" название (если название встречается несколько раз - будет использован первый из них). В следующей версии формулы кардинально изменена предпоследняя строка:
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;
уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));
{ArrayFormula(XLOOKUP(INDEX(уник;;2);INDEX(пабл;;2);INDEX(пабл;;1))) \ INDEX(уник;;2) \ суммы}
)

[/vba]

МОИ: Ник, Tip box: 41001663842605

Ответить

Дата: Среда, 20.03.2024, 16:20 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Гугл таблицы

Gustav, Не думал, что возможно реализовать мою идею, я попытался своими силами накрутить формулу.
Вот что получилось:
=ЕСЛИ($B2<>"";СУММЕСЛИ('Ночники МУЖЧИНЫ'!$D$2:$D$6791;$B2;'Ночники МУЖЧИНЫ'!E$2:E$6791);"")

Убрал столбец с названием сообщества, оставив только ID, а также новым столбцом добавил счет количества размещений в выбранном уникальном сообществе формулой:
=ЕСЛИ($B2<>"";СЧЁТЕСЛИ('Ночники МУЖЧИНЫ'!$D$2:$D$6791;$B2);"")

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

Твоя формула стала мне спасением, спасибо!

Только в столбцах CPM, Стоимость перехода, CTR данные суммируются. А по задумке там должны быть усреднённые значения. То есть при потраченных на это сообщество 5000 рублей средние значения были такими-то. И для бОльшего понимая эффективности сообщества добавить столбец "Количество размещений в сообществе"

"СPM" это стоимость разделить на охваты и умножить на 1000
"Стоимость перехода" это стоимость разделить на переходы
"CTR" это переходы разделить на охваты

Сообщение отредактировал keygarden16 - Среда, 20.03.2024, 16:25

Ответить

Дата: Среда, 20.03.2024, 17:39 | Сообщение № 5

Группа: Админы

Ранг: Участник клуба

Сообщений: 2796

Репутация: 1160 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата keygarden16, 20.03.2024 в 16:20, в сообщении № 4 (

)

И для бОльшего понимая эффективности сообщества добавить столбец "Количество размещений в сообществе"

В этой колонке формула должна быть какая-то?

Цитата keygarden16, 20.03.2024 в 16:20, в сообщении № 4 (

)

"СPM" это стоимость разделить на охваты и умножить на 1000
"Стоимость перехода" это стоимость разделить на переходы
"CTR" это переходы разделить на охваты

Добавил отдельные идентификаторы для каждой колонки "Сводной" и пересчитал эти три последние. Получилось всё нагляднее и прозрачнее (я собой доволен yes

yes

):
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;

уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));

п1_Пбл; ArrayFormula(XLOOKUP(INDEX(уник;;2);INDEX(пабл;;2);INDEX(пабл;;1)));
п2_ID;  INDEX(уник;;2);

р1_Стм; INDEX(суммы;;1);
р2_Прх; INDEX(суммы;;2);
р3_Охв; INDEX(суммы;;3);
р4_Вст; INDEX(суммы;;4);
р5_Лай; INDEX(суммы;;5);
р6_Реп; INDEX(суммы;;6);
р7_Ком; INDEX(суммы;;7);
р8_CPM; ArrayFormula(р1_Стм / р3_Охв * 1000);
р9_СтП; ArrayFormula(р1_Стм / р2_Прх);
р10_CTR; ArrayFormula(р2_Прх / р3_Охв);

{п1_Пбл \ п2_ID \ р1_Стм \ р2_Прх \ р3_Охв \ р4_Вст \ р5_Лай \ р6_Реп \ р7_Ком \ р8_CPM \ р9_СтП \ р10_CTR}
)

[/vba]

Цитата keygarden16, 20.03.2024 в 16:20, в сообщении № 4 (

)

И для бОльшего понимая эффективности сообщества добавить столбец "Количество размещений в сообществе"

В этой колонке формула должна быть какая-то?

Цитата keygarden16, 20.03.2024 в 16:20, в сообщении № 4 (

)

"СPM" это стоимость разделить на охваты и умножить на 1000
"Стоимость перехода" это стоимость разделить на переходы
"CTR" это переходы разделить на охваты

Добавил отдельные идентификаторы для каждой колонки "Сводной" и пересчитал эти три последние. Получилось всё нагляднее и прозрачнее (я собой доволен yes

yes

):
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;

уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));

п1_Пбл; ArrayFormula(XLOOKUP(INDEX(уник;;2);INDEX(пабл;;2);INDEX(пабл;;1)));
п2_ID;  INDEX(уник;;2);

р1_Стм; INDEX(суммы;;1);
р2_Прх; INDEX(суммы;;2);
р3_Охв; INDEX(суммы;;3);
р4_Вст; INDEX(суммы;;4);
р5_Лай; INDEX(суммы;;5);
р6_Реп; INDEX(суммы;;6);
р7_Ком; INDEX(суммы;;7);
р8_CPM; ArrayFormula(р1_Стм / р3_Охв * 1000);
р9_СтП; ArrayFormula(р1_Стм / р2_Прх);
р10_CTR; ArrayFormula(р2_Прх / р3_Охв);

{п1_Пбл \ п2_ID \ р1_Стм \ р2_Прх \ р3_Охв \ р4_Вст \ р5_Лай \ р6_Реп \ р7_Ком \ р8_CPM \ р9_СтП \ р10_CTR}
)

[/vba]Gustav

МОИ: Ник, Tip box: 41001663842605

Ответить

Сообщение

Цитата keygarden16, 20.03.2024 в 16:20, в сообщении № 4 (

)

И для бОльшего понимая эффективности сообщества добавить столбец "Количество размещений в сообществе"

В этой колонке формула должна быть какая-то?

Цитата keygarden16, 20.03.2024 в 16:20, в сообщении № 4 (

)

"СPM" это стоимость разделить на охваты и умножить на 1000
"Стоимость перехода" это стоимость разделить на переходы
"CTR" это переходы разделить на охваты

Добавил отдельные идентификаторы для каждой колонки "Сводной" и пересчитал эти три последние. Получилось всё нагляднее и прозрачнее (я собой доволен yes

yes

):
[vba]

Код

=LET(
пабл;   'Общая'!C2:D;
показ;  'Общая'!E2:N;

уник0;  SORT(ArrayFormula(UNIQUE({LOWER(INDEX(пабл;;1))\--INDEX(пабл;;2)}));2;1);
уник;   FILTER(уник0; INDEX(уник0;;1)<>"");
суммы;  MAP(INDEX(уник;;1); INDEX(уник;;2);
            LAMBDA(x; y; MAKEARRAY(1; COLUMNS(показ);
            LAMBDA(r; c; SUMIFS(INDEX(показ;;c); INDEX(пабл;;1);x; INDEX(пабл;;2);y )))));

п1_Пбл; ArrayFormula(XLOOKUP(INDEX(уник;;2);INDEX(пабл;;2);INDEX(пабл;;1)));
п2_ID;  INDEX(уник;;2);

р1_Стм; INDEX(суммы;;1);
р2_Прх; INDEX(суммы;;2);
р3_Охв; INDEX(суммы;;3);
р4_Вст; INDEX(суммы;;4);
р5_Лай; INDEX(суммы;;5);
р6_Реп; INDEX(суммы;;6);
р7_Ком; INDEX(суммы;;7);
р8_CPM; ArrayFormula(р1_Стм / р3_Охв * 1000);
р9_СтП; ArrayFormula(р1_Стм / р2_Прх);
р10_CTR; ArrayFormula(р2_Прх / р3_Охв);

{п1_Пбл \ п2_ID \ р1_Стм \ р2_Прх \ р3_Охв \ р4_Вст \ р5_Лай \ р6_Реп \ р7_Ком \ р8_CPM \ р9_СтП \ р10_CTR}
)

[/vba]

Автор - Gustav
Дата добавления - 20.03.2024 в 17:39

Как свести уникальные значения из массива в лист? (Формулы/Formulas)

Страница 1 из 1
1

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!