Связать 2 таблицы в одну макросом. Сложное объединение двух таблиц.

[ Закрыто ] Связать 2 таблицы в одну макросом. Сложное объединение двух таблиц.

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

17.11.2016 21:59:29

Доброго вечера, уважаемые форумчане!
Прошу вашей помощи в организации связи "один-ко-многим" между двумя таблицами через макрос. От создания сводных по нескольким таблицам (это также решило бы проблему) пока отказался, ввиду слабого знания материала и опасений "накосячить", а макрос есть макрос - из созданной им таблицы я всегда смогу отличную сводную построить.
Не воспринимайте, пожалуйста, как ТЗ - выложил все мысли по теме, может чем-то поможет…

В посте №24 - рабочий макрос.

Пример такой (описание файла-примера):

Детали по макросу (как примерно я это вижу):

Комментарии:

Примеры похожих проблем:
Нашёл вот тут пример похожей проблемы. Решить с помощью этой надстройки не получилось - да и громоздкая она такая, основательная.

Инструкция по использованию спойлеров на форуме

Прикрепленные файлы

Сложное объединение таблиц.xlsx (15.43 КБ)

Изменено: Jack Famous - 24.11.2016 09:31:50

Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

SuperCat Пользователь Сообщений: 2737 Регистрация: 21.12.2012	#2 17.11.2016 22:41:54 Опять ТЗ строчим? There is no knowledge that is not power

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

17.11.2016 22:47:53

SuperCat, так и знал)))) да вот как ни напишу - всё как ТЗ выглядит))))

Вот то, что смог по теме в VBA)))

Код

Sub One2Many()
Application.ScreenUpdating = False
 
Dim KEY_DICT As Range
Dim LAYERS As Range
Dim KEY_FILL As Range
 
KEY_DICT = Application.InputBox("Укажите диапазон ключевого поля словаря:", "Запрос данных", "", Type:=8)
LAYERS = Application.InputBox("Укажите диапазон значений для вставки:", "Запрос данных", "", Type:=8)
KEY_FILL = Application.InputBox("Укажите диапазон ключевого поля таблицы назначения:", "Запрос данных", "", Type:=8)
 
Application.ScreenUpdating = True
End Sub

Изменено: Jack Famous - 18.11.2016 00:10:27

С.М.

Пользователь

Сообщений: 964 Регистрация: 22.12.2012

18.11.2016 02:51:45

Тренировка скрывания под спойлер

Код

Option Explicit
Option Base 1
 
Private Type MaterType
    LCnt As Long
    LNums() As Long
    Maters() As String
End Type
 
Sub Linkuem()
    Dim TList As ListObject, MTs() As MaterType, TNum, RNum, Arr(), TT(), TNums As New Collection
    Dim I As Long, J As Long, K As Long, H As Long
    Arr = [типы].Value
    H = UBound(Arr, 1)
    On Error Resume Next
    For K = 1 To H
        TNums.Add Arr(K, 1), CStr(Arr(K, 1))
    Next
    On Error GoTo 0
    ReDim MTs(TNums.Count)
    For Each TNum In TNums
        J = J + 1
        With MTs(J)
            ReDim .Maters(TNums.Count), .LNums(TNums.Count)
            K = 0
            For I = 1 To H
                If Arr(I, 1) = TNum Then
                    K = K + 1
                    .LNums(K) = Arr(I, 2)
                    .Maters(K) = Arr(I, 3)
                End If
            Next
            .LCnt = K
        End With
    Next
    Arr = [помещения].Value
    H = 0
    For K = 1 To UBound(Arr, 1)
        TNum = TNums(Arr(K, 2))
        H = H + MTs(TNum).LCnt
    Next
    ReDim TT(H, 3)
    I = 0
    For J = 1 To UBound(Arr, 1)
        RNum = Arr(J, 1)
        TNum = TNums(Arr(J, 2))
        With MTs(TNum)
            I = I + 1
            TT(I, 1) = RNum
            TT(I, 2) = TNum
            TT(I, 3) = .LNums(1) & ". " & .Maters(1)
            For K = 2 To .LCnt
                I = I + 1
                TT(I, 3) = .LNums(K) & ". " & .Maters(K)
            Next
        End With
    Next
    Set TList = Range("СБОР").ListObject
    H = H - TList.ListRows.Count
    If H Then
        Application.ScreenUpdating = False
        If H > 0 Then
            For I = 1 To H
                TList.ListRows.Add
            Next
        ElseIf H < 0 Then
            For I = TList.ListRows.Count To TList.ListRows.Count + H + 1 Step -1
                TList.ListRows(I).Delete
            Next
        End If
        Application.ScreenUpdating = True
    End If
    TList.DataBodyRange.Value = TT
End Sub

Изменено: С.М. - 18.11.2016 02:55:06

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

18.11.2016 09:32:44

С.М., наконец-то первый вариант по теме))))) спасибо вам большое - как обращаться с этим зверем?))) выделял и 1 и 2 таблицу, запускал макрос - ничего(((

Андрей VG

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

18.11.2016 12:11:34

Доброе время суток

Цитата
Сложное объединение двух таблиц.

Несколько щелчков мышкой... в Power Query. И выясняется, что в результате есть ошибочка

Успехов.

Прикрепленные файлы

Копия Копия Сложное объединение таблиц.xlsx (26.85 КБ)

Изменено: Андрей VG - 18.11.2016 12:12:23 (Файл не той системы приложил.)

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

18.11.2016 12:12:45

Андрей VG, здравствуйте!))) спасибо за пример, но пишет "указанного файла не существует"
По поводу Power Query - знаю, что так можно, но пока что боюсь DAX-формул (писал в шапке темы). Жду уроков от Николая Павлова…

Изменено: Jack Famous - 18.11.2016 12:15:52

Андрей VG

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

18.11.2016 12:15:38

Цитата
Jack Famous написал: "указанного файла не существует"

Вы слишком быстро пришли за примером. Обнаружил, что не ту версию файла прикрепил. Переложил, и тут вы - качать... Накладка, однако

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

18.11.2016 12:18:02

Андрей VG, спасибо большое)))) есть возможность рассказать, как эту динамическую связку организовывать? Я бы в динамике всё построил…

Прикрепленные файлы

Screenshot_1.png (23.84 КБ)

Изменено: Jack Famous - 18.11.2016 12:19:51

JeyCi

Пользователь

Сообщений: 3399 Регистрация: 27.11.2013

#10

18.11.2016 12:29:23

Цитата
Jack Famous написал: По поводу Power Query - знаю, что так можно, но пока что боюсь DAX-формул

не делайте непроверенных выводов!.. в PQ - язык другой (M-language), язык DAX в PP
p.s. см. #14 --> Расширенный редактор

Изменено: JeyCi - 18.11.2016 12:32:33

чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)

Максим Зеленский

Пользователь

Сообщений: 4663 Регистрация: 11.06.2014

Microsoft MVP 2018-2022

#11

18.11.2016 12:31:51

Можно чуть подсократить:

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="помещения"]}[Content],
    #"Объединенные запросы" = Table.NestedJoin(Источник,{"№ типа"},типы,{"№ типа"},"NewColumn",JoinKind.LeftOuter),
    #"Развернутый элемент NewColumn" = Table.ExpandTableColumn(#"Объединенные запросы", "NewColumn", {"Формула"}, {"Формула"}),
    #"Сортированные строки" = Table.Sort(#"Развернутый элемент NewColumn",{{"№ пом.", Order.Ascending}, {"Формула", Order.Ascending}}),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Сортированные строки",{{"Формула", "Материал"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Переименованные столбцы1",{{"№ пом.", Int64.Type}, {"№ типа", Int64.Type}})
in
    #"Changed Type"

F1 творит чудеса

Андрей VG

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

#12

18.11.2016 13:05:09

Цитата
Максим Зеленский написал: Можно чуть подсократить:

Конечно!

Код

let
    types = Excel.CurrentWorkbook(){[Name="типы"]}[Content],
    houses = Excel.CurrentWorkbook(){[Name="помещения"]}[Content],
    renHouses = Table.RenameColumns(houses, { {"№ типа", "typeName"} }),
    joined = Table.Join(renHouses, {"typeName"}, types, {"№ типа"})[[#"№ пом."], [#"№ типа"], [Формула]]
in
    Table.RenameColumns(joined, { {"Формула", "Материал"} })

С.М.

Пользователь

Сообщений: 964 Регистрация: 22.12.2012

#13

18.11.2016 13:27:25

Цитата
Jack Famous написал #5: С.М. , как обращаться с этим зверем?))) выделял и 1 и 2 таблицу, запускал макрос - ничего(((

Не надо ничего выделять, жмём кнопку :

Прикрепленные файлы

Сложное объединение таблиц.1.xlsb (27 КБ)

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#14

18.11.2016 13:34:16

С.М., огромное спасибо!!! Буду разбираться с кодом)))
если можно закомментируйте, пожалуйста код - хочу понять, какие переменные участвуют, чтобы запихнуть их в диалоговые окна при запуске макроса…
JeyCi, благодарю за подсказку и коррекцию)))
Андрей VG и Максим Зеленский, благодарою за вариант решения через PQ - в перспективе этот вариант, скорее всего станет основным, а пока буду учить матчасть))))

Изменено: Jack Famous - 18.11.2016 13:45:07

Максим Зеленский

Пользователь

Сообщений: 4663 Регистрация: 11.06.2014

Microsoft MVP 2018-2022

#15

18.11.2016 15:11:30

Андрей VG, нет предела совершенству

Скрытый текст

Код

let
    types = Excel.CurrentWorkbook(){[Name="типы"]}[Content],
    houses = Excel.CurrentWorkbook(){[Name="помещения"]}[Content],
    joined = Table.Join(houses, {"№ типа"}, types, {"№ типа"})[[#"№ пом."], [#"№ типа"], [Формула]]
in
    Table.RenameColumns(joined, { {"Формула", "Материал"} })

до этого опускаться не будем:

Код

let
    joined = Table.Join(Excel.CurrentWorkbook(){[Name="помещения"]}[Content], {"№ типа"}, Excel.CurrentWorkbook(){[Name="типы"]}[Content], {"№ типа"})[[#"№ пом."], [#"№ типа"], [Формула]]
in
    Table.RenameColumns(joined, { {"Формула", "Материал"} })

Изменено: Максим Зеленский - 18.11.2016 15:12:10

F1 творит чудеса

Андрей VG

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

#16

18.11.2016 15:33:34

Максим, у меня PQL через раз ругается, что не может выполнить Join из-за одинаковых названий столбцов

Код

1	`Table.Join(houses, {"№ типа"}, types, {"№ типа"})`

, поэтому и пришлось делать "лишнюю" операцию. Причём, если соединение по двум и более столбцам, тогда без вопросов - отрабатывает как и должно быть. Один из глюков PQL - может в последних и поправили.

Максим Зеленский Пользователь Сообщений: 4663 Регистрация: 11.06.2014 Microsoft MVP 2018-2022	#17 18.11.2016 16:41:53 странно, у меня не ругается... Прикрепленные файлы Table.Join.JPG (37.17 КБ) Копия Копия Сложное объединение таблиц.xlsx (25.59 КБ) F1 творит чудеса

Максим Зеленский

Пользователь

Сообщений: 4663 Регистрация: 11.06.2014

Microsoft MVP 2018-2022

#18

18.11.2016 16:46:01

я думаю, в этом дело:

Цитата
An Expression.Error is thrown if a column with the same name to appear in both tables of the join unless the column is selected by both key1 and key2 and the join is an inner join.

F1 творит чудеса

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#19

18.11.2016 17:10:33

Максим Зеленский, Андрей VG, подскажите, а эти формулы только руками вбивать или есть что-то вроде мастера функций, как в Excel - тогда дело за синтаксисом и практикой))

JeyCi

Пользователь

Сообщений: 3399 Регистрация: 27.11.2013

#20

18.11.2016 18:24:47

Цитата
Jack Famous написал: а эти формулы только руками вбивать или

Jack Famous вы кнопки в надстройке нажимайте и смотрите, что получается, а в расширенном редакторе - весь код целиком увидеть можно... вопрос в том в какой последовательности какие кнопки (в зависимости от того, какие шаги по коду совершить хотите)... последовательность - это уже вопрос алгоритмизации... для получения нужного на выходе... вы не стесняйтесь знакомиться с надстройкой

, если чётко себе представляете алгоритм, который вам надо закодировать [решение порождает человек, простые шаги может пройти по кнопкам на ленте PQ]... сложные моменты придётся править руками, когда захотите выполнять ювелирную работу (как с макрорекодером)... - что вызвало проблему, чтобы самому прийти к такому выводу?? - особенно после прохождения по моему линку в #10 и ответа Максима там...

Изменено: JeyCi - 18.11.2016 18:30:27

С.М.

Пользователь

Сообщений: 964 Регистрация: 22.12.2012

#21

18.11.2016 20:05:07

Цитата
Jack Famous написал: С.М. , Буду разбираться с кодом))) если можно закомментируйте, пожалуйста код - хочу понять, какие переменные участвуют

Вот:

Код

Private Type MaterType ' тип отделки [материалами]
Rem это (в VBA) - Пользовательский Тип Данных _
(в других языках называется Запись) - набор c именованными элементами-полями
    LCnt As Long ' число слоёв в отделке
    LNums() As Long ' массив номеров слоёв
    Maters() As String ' массив наименований материалов в отделке
End Type
 
Sub Linkuem()
    Dim MTs() As MaterType ' массив записей отделок
    Dim TNums As New Collection ' номера (уникальные) отделок
    Dim TNum, RNum ' номер_отделки и номер_слоя_в_отделке
    Dim Arr() ' массив, в который считываются значения исходных таблиц
    Dim TT() ' выходной массив для вставки в таблицу "СБОР"
    Dim H As Long ' число строк в массивах
    Dim TList As ListObject ' объект-таблица "СБОР"
    Dim I As Long, J As Long, K As Long
    '

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#22

18.11.2016 21:15:15

JeyCi, надо пробовать - спасибо за напутствие!))
С.М., большое спасибо вам!)) Попробую что-нибудь сделать с этим

наверняка куча вопросов будет

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#23

18.11.2016 22:07:10

С.М., посидел, посмотрел - из переменных, которые смог: разглядеть только типы, помещения и таблица. Первые 2 - в виде именованных диапазонов, а таблица и есть таблица)) не могли бы вы изменить сам принцип работы макроса? Сейчас он решает одну конкретную задачу - ни влево, ни в право, к тому же он вставляет не значения столбца, а самостоятельно индексирует третий столбец справочника. То есть, если мне нужно вставлять какой-либо другой столбец - это очень замороченная процедура получится…
Прошу вас попробовать сделать нечто похожее, как я начал в #3. То есть, выбираем ключевые поля в обоих таблицах через диалоговые окна, выбираем, какой столбец справочника переносить в целевую таблицу (размножением строк) и всё. Номеров типов и слоёв ведь тоже может не быть, поэтому макрос должен считать всё самостоятельно, ориетируясь не на числа, а только на то, сколько строк переносимого столбца справочника соответствуют одному и тому же значению ключа справочника

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#24

24.11.2016 09:41:44

Доброго утра, уважаемые форумчане!
Александр С.М. согласился помочь с макросом и вот, что мы получили в итоге (выкладываю файл с макросом и отдельно модуль и класс для замечаний и предложений по оптимизации):

В модуле

Код

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

'Один-ко-многим по ключевому полю
'
'Заказчик: Jack Famous
'Воплотил идею в жизнь: С.М.
'Тема на форуме: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=84557&TITLE_SEO=84557-svyazat-2-tablitsy-v-odnu-makrosom-slozhnoe-obedinenie-dvukh-tablits&MID=705043&result=edit#message705043
'
'Присутствует проверка на соответствие ключей с перечислением несоответствий
'=======================================================================================================================================================================================================================
Option Explicit
Option Base 1
 
Sub One2Many()
 
    Dim DObj1 As New Class1, FObj1 As New Class1
    Dim ListObj As ListObject
    Dim DRng As Range, LRng As Range, FRng As Range
    Dim DArr(), LArr(), FArr(), Arr(), Item, Key
    Dim H As Long, J As Long, K As Long, L As Long
    Dim Txt As String, MsgRes As VbMsgBoxResult
    '
    Const ShowMaxLost As Long = 5 ' вывести макс. число не соответствующих ключей
    Dim LostObj As Class1
    '
    Application.DisplayAlerts = False
     
    On Error GoTo Exit1
    Set DRng = Application.InputBox("Укажите ячейку столбца ключевого поля словаря:", "Запрос данных. Шаг1.", , Type:=8).Cells(1)
    Set DRng = Intersect(DRng.EntireColumn, DRng.ListObject.DataBodyRange)
    Set LRng = Application.InputBox("Укажите ячейку столбца значений словаря для вставки:", "Запрос данных. Шаг2.", , Type:=8).Cells(1)
    Set LRng = Intersect(LRng.EntireColumn, LRng.ListObject.DataBodyRange)
    H = DRng.Rows.Count
    Set FRng = Application.InputBox("Укажите ячейку столбца ключевого поля таблицы_назначения:", "Запрос данных. Шаг3.", , Type:=8).Cells(1)
    Set ListObj = FRng.ListObject
    Set FRng = Intersect(FRng.EntireColumn, ListObj.DataBodyRange)
    FRng.Parent.Select
    DArr = DRng.Value ' ключи словаря '
    LArr = LRng.Value ' значения словаря '
    FArr = FRng.Value ' ключи таблицы_вставки '
    '
    For Each Item In FArr
        If Len(Item) Then FObj1.AddItem Item
    Next
    Set LostObj = New Class1
    For Each Item In DArr
        If Len(Item) Then
            If FObj1.Exists(Item) Then
                DObj1.AddItem Item
            Else ' ключа словаря нет в таблице_вставки
                LostObj.AddItem Item
            End If
        End If
    Next
    K = LostObj.Count
    If K Then
        Txt = "Не все ключи словаря найдены в таблице_назначения"
        GoSub Routine1
    End If
    '
    Set LostObj = New Class1
    For Each Key In FObj1.Keys
        If Not DObj1.Exists(Key) Then ' ключа таблицы_вставки нет в словаре
            LostObj.AddItem Key
        End If
    Next
    K = LostObj.Count
    If K Then
        Txt = "Ключу в таблице_назначения нет соответствия ключей словаря"
        GoSub Routine1
    End If
    '
    For Each Key In DObj1.Keys
        ReDim Arr(H)
        J = 0
        For K = 1 To H
            If CStr(DArr(K, 1)) = Key Then
                J = J + 1: Arr(J) = LArr(K, 1)
            End If
        Next
        ReDim Preserve Arr(J)
        DObj1.Item(Key) = WorksheetFunction.Transpose(Arr)
    Next
    '
     
    Application.ScreenUpdating = False
     
    ListObj.ListColumns.Add
    FRng.Columns(2).EntireColumn.Insert
    FRng.Cells(0, 2) = LRng.Cells(0, 1)
    For K = FRng.Rows.Count To 1 Step -1
        Item = FArr(K, 1)
        If Len(Item) Then
            If DObj1.Exists(Item) Then
                Arr = DObj1.Items(CStr(Item))
                H = UBound(Arr)
                L = K + 1
                For J = 1 To H - 1
                    ListObj.ListRows.Add L
                Next
                FRng.Cells(K, 2).Resize(H).Value = Arr
            End If
        End If
    Next
    ListObj.ListColumns(ListObj.ListColumns.Count).Delete
     
    Application.ScreenUpdating = True
     
    Exit Sub
Routine1:
    ReDim Arr(K)
    J = 0
    For Each Key In LostObj.Keys
        J = J + 1: Arr(J) = "    " & Key
    Next
    If K > ShowMaxLost Then
        K = ShowMaxLost + 1
        ReDim Preserve Arr(K): Arr(K) = "    ......."
        Txt = Txt & " [" & J & "]"
    End If
    Txt = Txt & ":" & vbCr & Join(Arr, vbCr) & vbCr & "Продолжить выполнение ?"
    If MsgBox(Txt, vbInformation + vbOKCancel + vbDefaultButton2, "Запрос данных.") = vbCancel Then Exit Sub
Return
Exit1:
    Err.Clear
    MsgBox "Диапазон не выбран.", , "Запрос данных."
     
    Application.ScreenUpdating = True
     
End Sub

В Class1 раздела Class Modules

Код

Option Explicit
Option Base 1
 
Dim HisKeys As New Collection
Dim HisItems As New Collection
Dim Indexes As New Collection
'
 
Sub AddItem(ByVal Key As String, Optional Item = Empty)
    On Error Resume Next
    HisKeys.Add Key, Key
    If Err = 0 Then
        HisItems.Add Item, Key
        Indexes.Add Indexes.Count + 1, Key
    Else
        Err.Clear
    End If
End Sub 'AddItem'
 
Property Get Exists(ByVal Key As String) As Boolean
    On Error Resume Next
    HisKeys.Add Empty, Key
    If Err Then
        Err.Clear: Exists = True
    Else
        HisKeys.Remove Key
    End If
End Property 'Exists'
 
Property Let Item(ByVal Key As String, NewValue)
    Dim I As Long
    On Error Resume Next
    HisKeys.Add Empty, Key
    If Err Then
        Err.Clear: I = Indexes.Item(Key)
        HisItems.Remove I
        If I > 1 Then HisItems.Add NewValue, Key, , I - 1 Else HisItems.Add NewValue, Key, I
    Else
        HisKeys.Remove Key
    End If
End Property 'Item'
 
Property Get Items()
    Set Items = HisItems
End Property
 
Property Get Keys()
    Set Keys = HisKeys
End Property
 
Property Get Count() As Long
    Count = HisItems.Count
End Property

Инструкция к макросу

Прикрепленные файлы

Объединение таблиц.xlsb (24.13 КБ)

Изменено: Jack Famous - 24.11.2016 10:25:06

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#25

24.11.2016 10:32:01

Отдельная благодарность Андрей VG, Максим Зеленский и JeyCi - с их подсказок удалось довольно простым способом осуществить подобную связь в динамически обновляемом виде через запросы в Power Query - теперь, связав 2 и более таблицы через запросы по ключевым полям и удалив в этих запросах всё лишнее, можно готовый итоговый запрос выгрузить в книгу и строить сводную по нему. При изменении/дополнении/удалении данных в справочниках - всё динамически обновляется и в сводной.
Теперь я серьёзно настроен на изучение Power BI в целом - спасибо, мастера!

Максим Зеленский

Пользователь

Сообщений: 4663 Регистрация: 11.06.2014

Microsoft MVP 2018-2022

#26

29.11.2016 15:40:57

Цитата
Jack Famous написал: можно готовый итоговый запрос выгрузить в книгу

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

F1 творит чудеса

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#27

29.11.2016 17:12:41

Максим Зеленский, да - добавить в модель данных и готово))) подскажите, пожалуйста аналоги функций СУММЕСЛИМН и СЧЁТЕСЛИМН для Power Query. Или это только через DAX в Power Pivot? А то таблички посвязывал по ключевым полям (6 штук), а этого капец как не хватает…

Андрей VG

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

#28

29.11.2016 21:21:22

Доброе время суток

Цитата
Jack Famous написал: аналоги функций СУММЕСЛИМН и СЧЁТЕСЛИМН для Power Query

Смотря что вы под этим подразумеваете. В общем случае - фильтрация по критериям и группировка (плоская сводная таблица).

Jack Famous

Пользователь

Сообщений: 12274 Регистрация: 07.11.2014

OS: Win 10 Корп. x64 | Excel 2016 x64: | Browser: Chrome

#29

30.11.2016 00:23:52

Андрей VG, доброй ночи!
Вы мне всегда подсказывали по поводу сводных и Power Pivot - надеюсь, что и тут поможете… Прикрепил файл-пример со связкой 3х таблиц через Power Query - как вышло и чего не хватает.
ЖЁЛТЫМ выделены ключевые поля
ЗЕЛЁНЫМ выделены поля с формулами
СИРЕНЕВЫМ выделены пользовательские столбцы с формулами в Power Query
ОРАНЖЕВЫМ выделено то, чего не смог достичь внутри запросов в Power Query

Прикрепленные файлы

Формулы в Power Query.xlsx (32.48 КБ)

Изменено: Jack Famous - 30.11.2016 00:24:07

Jack Famous Пользователь Сообщений: 12274 Регистрация: 07.11.2014 OS: Win 10 Корп. x64 \| Excel 2016 x64: \| Browser: Chrome	#30 30.11.2016 00:27:28 МОДЕРАТОРАМ: предлагаю (из-за развития темы вне названия) следующее название темы - "Объединение таблиц. Макросы, Power Query и Power Pivot" Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄

Читают тему