Excel это не сложно
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
29.03.2024, 14:17:16

Войти
Добавляйтесь в нашу группу ВКонтакте - будьте в курсе всех новых событий сайта, узнавайте первым обо всех акциях и новых статьях!
33 233 Сообщений в 5 454 Тем от 6 750 Пользователей
Последний пользователь: Alex1210
*
Перейти на сайт Хитрости Надстройка MulTEx Обучающие тренинги Наша группа ВКонтакте
Правила форума Начало Помощь Поиск Календарь Войти Регистрация Выйти
+  Excel это не сложно
|-+  Основные форумы
| |-+  Вопросы по Excel и VBA
| | |-+  сводная таблица без суммирования или перекрестный запрос
Страниц: [1]   Вниз
Печать
Автор Тема: сводная таблица без суммирования или перекрестный запрос  (Прочитано 7082 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Nurbol
Новичок
*

Репутация: +1/-0
Офлайн Офлайн

Сообщений: 25


Просмотр профиля
« : 08.06.2018, 13:38:15 »

Доброго времени вам
Есть файл, некий план командировок.
Выглядит он из граф: ФИО - город - период
Ну, то есть, кто когда и куда поедет.
Нужно сделать что-то похожее на сводную таблицу. пытался через сводные сделать - но там на пересечении результат в виде арифметического действия (сумма и т.п.)
А нужно сделать сводную, в которой строки - ФИО, столбцы - это периоды, а на пересечении - города командировок.
Ну и обратную сводную: в строках - города, в столбцах - периоды, а на пересечении - ФИО сотрудника.
Что-то вроде перекрестного запроса получается
Подскажите, как это сделать малой кровью?

Файл прилагаю
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #1 : 08.06.2018, 16:53:34 »

Если у Вас 2010 и выше, то можно использовать PowerQuery. Она перестроит таблицу как надо.
Правда, мне лично непонятно что делать с пустыми ячейками в месяцах.
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Nurbol
Новичок
*

Репутация: +1/-0
Офлайн Офлайн

Сообщений: 25


Просмотр профиля
« Ответ #2 : 08.06.2018, 17:01:45 »

Если у Вас 2010 и выше, то можно использовать PowerQuery..
Что за зверь. Ща гляну
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #3 : 08.06.2018, 17:07:35 »

Power Query - что такое и почему её необходимо использовать в работе?
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #4 : 08.06.2018, 17:17:23 »

Во вложении файлик с готовым запросом для "Свод1".
Как было сделано:
на листе "Исходник" выделяем любую ячейку -вкладка Данные -Получить и преобразовать данные -Из таблицы
соглашаемся с созданием умной таблицы, ставим галочку "с заголовками"
далее уже в редакторе PowerQuery выделяем столбец период -вкладка Преобразование -Замена значений. Заменяем null на "пусто"(или что-то другое)
оставляя выделенным столбец "период" на той же вкладке Преобразование выбираем Столбец сведения. В качестве Столбец значений выбираете "ФИО", раскрываете "Расширенные параметры" и выбираете Не агрегировать.
Вкладка Главная - Закрыть и загрузить.
Все.
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Nurbol
Новичок
*

Репутация: +1/-0
Офлайн Офлайн

Сообщений: 25


Просмотр профиля
« Ответ #5 : 09.06.2018, 09:59:59 »

пытался вчера ответить, но похоже опять вчера оппозиционер ссушенный вещал - отрубили интернет.
в общем, получилось, как описано, но вот вариант, когда нужно выбрать в столбце значений "город", то в некоторых выдает ошибку "Error".
Очевидно, что это там, где несколько значений. например, в один месяц один сотрудник едет в несколько городов. Или в один город в один месяц едут несколько сотрудников.
вопрос: что делать?

Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #6 : 09.06.2018, 10:06:02 »

Так вроде и в Вашем примере нет ответа на этот вопрос. Как Вы хотите видеть результат в этом случае?
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Nurbol
Новичок
*

Репутация: +1/-0
Офлайн Офлайн

Сообщений: 25


Просмотр профиля
« Ответ #7 : 09.06.2018, 13:12:02 »

Вот ровно так, как в примере: несколько городов, или несколько фио, соответственно.
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #8 : 09.06.2018, 13:54:17 »

ровно так, как в примере
Тогда придется помучиться чуть больше. Вот текст запроса для приложенного файла:
Код: (vb)
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Регион", type text}, {"ФИО", type text}, {"Период", type text}}),
    #"Замененное значение" = Table.ReplaceValue(#"Измененный тип",null,"пусто",Replacer.ReplaceValue,{"Период"}),
    #"Сгруппированные строки" = Table.Group(#"Замененное значение", {"Регион", "Период"}, {{"ФИО.2", each _, type table}}),
    #"Сведенный столбец" = Table.Pivot(#"Сгруппированные строки", List.Distinct(#"Сгруппированные строки"[Период]), "Период", "ФИО.2"),
    #"Развернутый элемент июль" = Table.ExpandTableColumn(#"Сведенный столбец", "июль", {"ФИО"}, {"июль"}),
    #"Развернутый элемент июнь" = Table.ExpandTableColumn(#"Развернутый элемент июль", "июнь", {"ФИО"}, {"июнь"}),
    #"Развернутый элемент пусто" = Table.ExpandTableColumn(#"Развернутый элемент июнь", "пусто", {"ФИО"}, {"пусто"}),
    #"Развернутый элемент сентябрь" = Table.ExpandTableColumn(#"Развернутый элемент пусто", "сентябрь", {"ФИО"}, {"сентябрь"}),
    #"Развернутый элемент август" = Table.ExpandTableColumn(#"Развернутый элемент сентябрь", "август", {"ФИО"}, {"август"})
in
    #"Развернутый элемент август"

будет в точности как надо. Сначала сгруппировал по Региону и Периоду(Преобразование -Группировать). Потом свел по периоду(как и до этого). А потом поочередно развернул каждый столбец. В расширенном редакторе переименовал названия созданных столбцов на нужные месяцы.

Чуть проще будет, если все фио в одну ячейку через запятую записать. Текст запроса:
Код: (vb)
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Регион", type text}, {"ФИО", type text}, {"Период", type text}}),
    #"Замененное значение" = Table.ReplaceValue(#"Измененный тип",null,"пусто",Replacer.ReplaceValue,{"Период"}),
    #"Сгруппированные строки" = Table.Group(#"Замененное значение", {"Регион", "Период"}, {{"ФИО_все", each _, type table}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "ФИО_вместе", each Text.Combine(Table.ToList(Table.SelectColumns([ФИО_все],{"ФИО"})),", ")),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"ФИО_все"}),
    #"Сведенный столбец" = Table.Pivot(#"Удаленные столбцы", List.Distinct(#"Удаленные столбцы"[Период]), "Период", "ФИО_вместе")
in
    #"Сведенный столбец"

Файл с первым запросом приложил.
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
Страниц: [1]   Вверх
Печать
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2011, Simple Machines Valid XHTML 1.0! Valid CSS!
Яндекс.Метрика Рейтинг@Mail.ru