Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/Пересобрать таблицу, по другому принципу сортирования. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Пересобрать таблицу, по другому принципу сортирования. (Формулы/Formulas)
Пересобрать таблицу, по другому принципу сортирования.
mikaelw Дата: Воскресенье, 12.04.2020, 22:23 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Всем привет!

Это реестр учета приборов учета ХВС и ГВС.
Сейчас в реестре 900 строк

Есть таблица БД, ее надо перестраивать.
динамически получать новую при добавлении значений в БД.

Ее не нужно пересортировать, ее нужно принципиально перестроить.

1. в БД нужно обрабатывать строки только со значением "установлен" из столбца 1.
2. в столбце л/с и № значение может быть только 2 раза(но иногда и 1 раз) повторяться(это проверяется на вкладке БД при заполнении).
3. Нужно построить таблицу по л/с
4. расположив напротив л/с сначала прибор ХВС, а потом ГВС

Смотрите пример.

В примере исходная БД на вкладке БД
Требуемый результат на вкладке результат.

P.S.Я не понимаю как это сделать.
Не могу понять как это делать, динамически или вызовом макроса, но делать это в ручную реальная каторга, т.к. приходиться потом сидеть и проверять.

Заранее спасибо!
К сообщению приложен файл: 8727780.xlsx(21.6 Kb)
 
Ответить
СообщениеВсем привет!

Это реестр учета приборов учета ХВС и ГВС.
Сейчас в реестре 900 строк

Есть таблица БД, ее надо перестраивать.
динамически получать новую при добавлении значений в БД.

Ее не нужно пересортировать, ее нужно принципиально перестроить.

1. в БД нужно обрабатывать строки только со значением "установлен" из столбца 1.
2. в столбце л/с и № значение может быть только 2 раза(но иногда и 1 раз) повторяться(это проверяется на вкладке БД при заполнении).
3. Нужно построить таблицу по л/с
4. расположив напротив л/с сначала прибор ХВС, а потом ГВС

Смотрите пример.

В примере исходная БД на вкладке БД
Требуемый результат на вкладке результат.

P.S.Я не понимаю как это сделать.
Не могу понять как это делать, динамически или вызовом макроса, но делать это в ручную реальная каторга, т.к. приходиться потом сидеть и проверять.

Заранее спасибо!

Автор - mikaelw
Дата добавления - 12.04.2020 в 22:23
прохожий2019 Дата: Воскресенье, 12.04.2020, 23:15 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 0% ±

365 Beta Channel
может PQ? [vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([Статс] = "установлен")),
    group = Table.Group(filtr, {"л/с","№","Ресурс"}, {"Количество", each _, type table}),
    pivot = Table.Pivot(group, List.Distinct(group[Ресурс]), "Ресурс", "Количество"),
    lst = {"№ прибора","Срок поверки до","Прибор","ДАТА","Дата пр-ва", "срок поверки"},
    exp = Table.ExpandTableColumn(pivot, "ХВС", lst, List.Transform(lst, each "ХВС."&_)),
    to = Table.ExpandTableColumn(exp, "ГВС", lst, List.Transform(lst, each "ГВС."&_))
in
    to
[/vba]
К сообщению приложен файл: 3176242.xlsx(31.4 Kb)
 
Ответить
Сообщениеможет PQ? [vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([Статс] = "установлен")),
    group = Table.Group(filtr, {"л/с","№","Ресурс"}, {"Количество", each _, type table}),
    pivot = Table.Pivot(group, List.Distinct(group[Ресурс]), "Ресурс", "Количество"),
    lst = {"№ прибора","Срок поверки до","Прибор","ДАТА","Дата пр-ва", "срок поверки"},
    exp = Table.ExpandTableColumn(pivot, "ХВС", lst, List.Transform(lst, each "ХВС."&_)),
    to = Table.ExpandTableColumn(exp, "ГВС", lst, List.Transform(lst, each "ГВС."&_))
in
    to
[/vba]

Автор - прохожий2019
Дата добавления - 12.04.2020 в 23:15
mikaelw Дата: Воскресенье, 12.04.2020, 23:18 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Цитата прохожий2019, 12.04.2020 в 23:15, в сообщении № 2 ()
может PQ?

Я понимаю что это power query или макрос......

Но мне такое написать не по силам......
 
Ответить
Сообщение
Цитата прохожий2019, 12.04.2020 в 23:15, в сообщении № 2 ()
может PQ?

Я понимаю что это power query или макрос......

Но мне такое написать не по силам......

Автор - mikaelw
Дата добавления - 12.04.2020 в 23:18
прохожий2019 Дата: Воскресенье, 12.04.2020, 23:27 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 0% ±

365 Beta Channel
Так, а результат в файле чем не устраивает? От вас же требуется только обновлять запрос
И не понял про повторяющиеся л/с и № - что делать с повторами, удалять? тогда:[vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([Статс] = "установлен")),
    group = Table.Group(filtr, {"л/с","№","Ресурс"}, {"Количество", each _{0}, type table}),
    pivot = Table.Pivot(group, List.Distinct(group[Ресурс]), "Ресурс", "Количество"),
    lst = {"№ прибора","Срок поверки до","Прибор","ДАТА","Дата пр-ва", "срок поверки"},
    exp = Table.ExpandRecordColumn(pivot, "ХВС", lst, List.Transform(lst, each "ХВС."&_)),
    to = Table.ExpandRecordColumn(exp, "ГВС", lst, List.Transform(lst, each "ГВС."&_))
in
to
[/vba]
К сообщению приложен файл: 3618542.xlsx(31.4 Kb)


Сообщение отредактировал прохожий2019 - Воскресенье, 12.04.2020, 23:28
 
Ответить
СообщениеТак, а результат в файле чем не устраивает? От вас же требуется только обновлять запрос
И не понял про повторяющиеся л/с и № - что делать с повторами, удалять? тогда:[vba]
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([Статс] = "установлен")),
    group = Table.Group(filtr, {"л/с","№","Ресурс"}, {"Количество", each _{0}, type table}),
    pivot = Table.Pivot(group, List.Distinct(group[Ресурс]), "Ресурс", "Количество"),
    lst = {"№ прибора","Срок поверки до","Прибор","ДАТА","Дата пр-ва", "срок поверки"},
    exp = Table.ExpandRecordColumn(pivot, "ХВС", lst, List.Transform(lst, each "ХВС."&_)),
    to = Table.ExpandRecordColumn(exp, "ГВС", lst, List.Transform(lst, each "ГВС."&_))
in
to
[/vba]

Автор - прохожий2019
Дата добавления - 12.04.2020 в 23:27
mikaelw Дата: Воскресенье, 12.04.2020, 23:34 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Цитата прохожий2019, 12.04.2020 в 23:27, в сообщении № 4 ()
ак, а результат в файле чем не устраивает?


ВСЕ СУПЕР!!!!!!!
Все устраивает, уже перенес в файл.

Я только форматирование дат настроил...
И столбцы пустые добавил


Сообщение отредактировал mikaelw - Воскресенье, 12.04.2020, 23:35
 
Ответить
Сообщение
Цитата прохожий2019, 12.04.2020 в 23:27, в сообщении № 4 ()
ак, а результат в файле чем не устраивает?


ВСЕ СУПЕР!!!!!!!
Все устраивает, уже перенес в файл.

Я только форматирование дат настроил...
И столбцы пустые добавил

Автор - mikaelw
Дата добавления - 12.04.2020 в 23:34
mikaelw Дата: Воскресенье, 21.11.2021, 16:17 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Привет.

В продолжение.

Поменяли таблицу БД(новая в файле под старой.

При обращение запроса валиться ошибка

DataFormat.Error: Недопустимое значение ячейки "#VALUE!".

Не могу сообразить, на что ругается..

Помогите пожалуйста
К сообщению приложен файл: 6199109.xlsx(35.6 Kb)
 
Ответить
СообщениеПривет.

В продолжение.

Поменяли таблицу БД(новая в файле под старой.

При обращение запроса валиться ошибка

DataFormat.Error: Недопустимое значение ячейки "#VALUE!".

Не могу сообразить, на что ругается..

Помогите пожалуйста

Автор - mikaelw
Дата добавления - 21.11.2021 в 16:17
Pelena Дата: Воскресенье, 21.11.2021, 16:35 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Вроде всё работает (оранжевая таблица)
К сообщению приложен файл: 6216579.xlsx(41.9 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВроде всё работает (оранжевая таблица)

Автор - Pelena
Дата добавления - 21.11.2021 в 16:35
mikaelw Дата: Воскресенье, 21.11.2021, 16:55 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Вроде всё работает (оранжевая таблица)


Офис обновился, вылетает предупреждение о совместимости....
 
Ответить
Сообщение
Вроде всё работает (оранжевая таблица)


Офис обновился, вылетает предупреждение о совместимости....

Автор - mikaelw
Дата добавления - 21.11.2021 в 16:55
Pelena Дата: Воскресенье, 21.11.2021, 17:00 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Я всего лишь отформатировала второй диапазон как умную таблицу, не захватывая первую строку с нумерацией, загрузила её в PQ, скопировала прежний запрос, поменяла название таблицы


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЯ всего лишь отформатировала второй диапазон как умную таблицу, не захватывая первую строку с нумерацией, загрузила её в PQ, скопировала прежний запрос, поменяла название таблицы

Автор - Pelena
Дата добавления - 21.11.2021 в 17:00
mikaelw Дата: Воскресенье, 21.11.2021, 17:34 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Я так и понял.... И как теперь в PQ искать ошибку....


Сообщение отредактировал mikaelw - Воскресенье, 21.11.2021, 17:39
 
Ответить
СообщениеЯ так и понял.... И как теперь в PQ искать ошибку....

Автор - mikaelw
Дата добавления - 21.11.2021 в 17:34
mikaelw Дата: Вторник, 30.11.2021, 00:58 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Может есть идеи? Откуда ошибка
 
Ответить
СообщениеМожет есть идеи? Откуда ошибка

Автор - mikaelw
Дата добавления - 30.11.2021 в 00:58
прохожий2019 Дата: Вторник, 30.11.2021, 07:39 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 0% ±

365 Beta Channel
DataFormat.Error: Недопустимое значение ячейки "#VALUE!"
оно же #ЗНАЧ - в исходных данных ячейка с ошибкой, в примере, соответственно таких ячеек нет и всё работает
 
Ответить
Сообщение
DataFormat.Error: Недопустимое значение ячейки "#VALUE!"
оно же #ЗНАЧ - в исходных данных ячейка с ошибкой, в примере, соответственно таких ячеек нет и всё работает

Автор - прохожий2019
Дата добавления - 30.11.2021 в 07:39
mikaelw Дата: Вторник, 30.11.2021, 13:31 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Цитата прохожий2019, 30.11.2021 в 07:39, в сообщении № 12 ()
оно же #ЗНАЧ - в исходных данных ячейка с ошибкой, в примере, соответственно таких ячеек нет и всё работает


В том то и проблема, что и на примере у меня выдает ошибку тоже, после обновления офиса..
 
Ответить
Сообщение
Цитата прохожий2019, 30.11.2021 в 07:39, в сообщении № 12 ()
оно же #ЗНАЧ - в исходных данных ячейка с ошибкой, в примере, соответственно таких ячеек нет и всё работает


В том то и проблема, что и на примере у меня выдает ошибку тоже, после обновления офиса..

Автор - mikaelw
Дата добавления - 30.11.2021 в 13:31
mikaelw Дата: Вторник, 30.11.2021, 13:32 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Скрин.
К сообщению приложен файл: 2315187.png(77.1 Kb)
 
Ответить
СообщениеСкрин.

Автор - mikaelw
Дата добавления - 30.11.2021 в 13:32
Pelena Дата: Вторник, 30.11.2021, 13:38 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Это не ошибка, просто предупреждение. Сам первоначальный запрос я не меняла, поэтому просто нажмите ОК


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЭто не ошибка, просто предупреждение. Сам первоначальный запрос я не меняла, поэтому просто нажмите ОК

Автор - Pelena
Дата добавления - 30.11.2021 в 13:38
mikaelw Дата: Вторник, 30.11.2021, 14:22 | Сообщение № 16
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010, 2013, 2016
Цитата прохожий2019, 30.11.2021 в 07:39, в сообщении № 12 ()
оно же #ЗНАЧ - в исходных данных ячейка с ошибкой,


НАШЕЛ проблему.
Проверку на таблицу наложил, чтоб больше не было.
 
Ответить
Сообщение
Цитата прохожий2019, 30.11.2021 в 07:39, в сообщении № 12 ()
оно же #ЗНАЧ - в исходных данных ячейка с ошибкой,


НАШЕЛ проблему.
Проверку на таблицу наложил, чтоб больше не было.

Автор - mikaelw
Дата добавления - 30.11.2021 в 14:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Пересобрать таблицу, по другому принципу сортирования. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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