Framed (Все сообщения пользователя)

PQ: Исключение из итоговой таблицы всех значений, для которых верно условие

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 05.07.2022 17:42:46 surkenny, вы правы, я недоглядел и выделил не всё. Спасибо за помощь, это то, что нужно.
	Перейти

PQ: Исключение из итоговой таблицы всех значений, для которых верно условие

Framed

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

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

05.07.2022 17:01:36

Добрый день, коллеги,

Имеется таблица с большим количеством столбцов.
В ней имеется ключевой столбец (допустим, Doc.No) - содержит номера документов, номера могут повторяться.
Также имеется столбец с номером счета (допустим, Acc.No) - значение в нем может повторяться, или вовсе отсутствовать.
Имеется дополнительный запрос со списком (list) значений для Acc.No.

Нужно: исключить из итоговой таблицы все Doc.No, для которых хотя бы один из соответствующих Acc.No равен значению из list.
Файл с примером во вложении (красным выделено то, что должно быть исключено из итоговой таблицы, т.к. хотя бы одно значение для соответствующего Doc.No было найдено в list).

Перейти

PQ: Правильное использование Table.Buffer

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 20.06.2022 23:18:41 PooHkrd, да, спасибо, практическим путем удалось выяснить, что конкретно в моем случае, буферизация не подходит. А вообще очень помогла информация из тем, в обсуждении которых вы непосредственно участвовали.
	Перейти

PQ: Правильное использование Table.Buffer

Framed

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

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

18.06.2022 22:48:43

Zagadka, спасибо, принял к сведению эту информацию, но все равно пока не пойму, что делать: сначала объединить все нужное в один запрос, забуферить, после чего фильтровать это все по разным условиям, либо сначала частично отфильтровать, забуферить отфильтрованное до, после чего продолжить видоизменять промежуточный результат.

Перейти

PQ: Правильное использование Table.Buffer

Framed

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

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

18.06.2022 20:09:29

Коллеги, добрый вечер,

Ситуация следующая: есть один основной запрос, и три дополнительных, из которых я в основной запрос тяну данные с помощью Merged, после чего провожу манипуляции: фильтрация по одному или нескольким признакам, создание условных столбцов, замена значений в столбцах (некоторые - по условию), изменения формата данных, перестанока, переименование, удаление столбцов, сортировка. Основной запрос и один из трех дополнительных тянутся из CSV файлов, примерно от 90-220 тысяч строк каждый.

Вопрос: имеет ли смысл использовать в моем случае Table.Buffer, или нет?

Изучал тут, тут и тут. Понимаю смысл самой функции, но не могу понять, как её применить в моем случае: например, лучше будет сперва отmerge-ить все доп. запросы, закинуть таблицу в буфер, после чего проводить все манипуляции, либо сначала отфильтровать все, потом буфер, потом все остальное, либо вообще оставить всё как есть.

Код главного запроса, на всякий случай:

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

Код

let
    Source = Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="PQSettings"]}[Content]{0}[Value]),[Delimiter=",", Columns=167, Encoding=65001, QuoteStyle=QuoteStyle.None]),  
    //Take first 2 rows and transform them
    #"First two rows" = Table.FirstN(Source, 2),
    #"Transpose rows" = Table.Transpose(#"First two rows"),
    #"Add custom column" = Table.AddColumn(#"Transpose rows", "Custom", each if [Column1] = "" then [Column2] else [Column1]&"//"&[Column2]),
    #"Remove 2 first columns" = Table.RemoveColumns(#"Add custom column",{"Column1", "Column2"}),
    #"Transpose columns to rows" = Table.Transpose(#"Remove 2 first columns"),
    #"Source without 2 first rows" = Table.RemoveFirstN(Source, 2),
    #"Combine new rows with source" = Table.Combine({#"Transpose columns to rows",#"Source without 2 first rows"}),
    #"Promoted first row as header" = Table.PromoteHeaders(#"Combine new rows with source", [PromoteAllScalars=true]),
    // Filtering in XREVERSAL column (XREVERSAL = "" or XREVERSAL = null)
    #"Filtered rows: XREVERSAL is empty" = Table.SelectRows(#"Promoted first row as header", each ([#"Rev. With//STBLG"] = "" or [#"Rev. With//STBLG"] = null)),
    // Filtering DocumentNo column by excluding all documents that existing in exception listm, but only if E/L filter = TRUE
    #"Filtered rows: applied exceptions list" = if #"E/L filter" = "" or #"E/L filter" = false then #"Filtered rows: XREVERSAL is empty" else Table.SelectRows(#"Filtered rows: XREVERSAL is empty", each not List.Contains(#"BELNR E/L", [#"DocumentNo//BELNR"])),
    // Filtering by exceptions list
    #"Filtered rows: document type" = if List.First(#"Document types list:")= "" or List.First(#"Document types list:")= -1 then #"Filtered rows: applied exceptions list" else Table.SelectRows(#"Filtered rows: applied exceptions list", each List.Contains(#"Document types list:", [#"Doc. Type//BLART"])),
    #"Filtered rows: by Currency filter" = if #"Currency filter"= "" then #"Filtered rows: document type" else if Text.Contains(#"Currency filter","<>") then Table.SelectRows (#"Filtered rows: document type", each ([#"Crcy//WAERS"] <> Text.End(#"Currency filter",3)))   else Table.SelectRows(#"Filtered rows: document type", each ([#"Crcy//WAERS"] = #"Currency filter")),
    // Replace 751 to 0751 in CoCd column for doing correctly the future mapping
    #"Replaced values: correcting matching below" = Table.ReplaceValue(#"Filtered rows: by Currency filter","751","0751",Replacer.ReplaceValue,{"CoCd//BUKRS"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced values: correcting matching below",{{"DocumentNo//BELNR", each Text.TrimStart(_,"0")}}),
    #"Merged with BSEG" = Table.NestedJoin(#"Trimmed Text", {"DocumentNo//BELNR", "CoCd//BUKRS", "Year//GJAHR"}, #"0_BSEG", {"Document Number//BELNR", "Company Code//BUKRS", "Fiscal Year//GJAHR"}, "BSEG", JoinKind.LeftOuter),
    #"Expanded BSEG" = Table.ExpandTableColumn(#"Merged with BSEG", "BSEG", {"Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK"}, {"Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK"}),
    #"Filtered rows: HKONT" = Table.SelectRows(#"Expanded BSEG", each [#"G/L Account//HKONT"] <> "766000" and [#"G/L Account//HKONT"] <> "666000"),
    #"Filtered rows: HKONT list" = if List.First(#"HKONT S/L")= null then #"Filtered rows: HKONT" else Table.SelectRows(#"Filtered rows: HKONT", each List.Contains(#"HKONT S/L", [#"G/L Account//HKONT"])),
    #"Merged with Plan" = Table.NestedJoin(#"Filtered rows: HKONT list", {"G/L Account//HKONT"}, #"2_План счетов", {"счет HANA"}, "План счетов", JoinKind.LeftOuter),
    #"Expanded Plan" = Table.ExpandTableColumn(#"Merged with Plan", "План счетов", {"Column1"}, {"Column1"}),
    #"Merged with Codes" = Table.NestedJoin(#"Expanded Plan", {"Account Type//KOART", "Special G/L Ind.//UMSKZ"}, #"1_Codes", {"Вид счета", "КОД ОГК в Нессии"}, "Codes", JoinKind.LeftOuter),
    #"Expanded Codes" = Table.ExpandTableColumn(#"Merged with Codes", "Codes", {"Код ОГК ms2"}, {"Код ОГК ms2"}),
    #"Grouped Rows" = Table.Group(#"Expanded Codes", {"DocumentNo//BELNR"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Table", each _, type table [#"Cl.//MANDT"=text, #"CoCd//BUKRS"=text, #"DocumentNo//BELNR"=text, #"Year//GJAHR"=text, #"Doc. Type//BLART"=nullable text, #"Doc. Date//BLDAT"=text, #"Pstng Date//BUDAT"=text, #"Posting period//MONAT"=text, #"Entry Dte//CPUDT"=text, #"Time//CPUTM"=text, #"Changed//AEDAT"=text, #"Last Updte//UPDDT"=text, #"TranslDate//WWERT"=text, #"User Name//USNAM"=text, #"TCode//TCODE"=text, #"Cross-CC Number//BVORG"=text, #"Reference//XBLNR"=text, #"RecEnt Doc//DBBLG"=text, #"RecYear//DBBLG_GJAHR"=text, #"RecCoCode//DBBLG_BUKRS"=text, #"Rev. With//STBLG"=text, #"Year//STJAH"=text, #"Document Header Text//BKTXT"=text, #"Crcy//WAERS"=text, #"Exch.rate//KURSF"=text, #"GrpCy//KZWRS"=text, #"Group Exchange Rate//KZKRS"=text, #"S//BSTAT"=text, #"Net document type//XNETB"=text, #"Un.del.cts//FRATH"=text, #"I//XRUEB"=text, #"Tran//GLVOR"=text, #"Session Name//GRPID"=text, #"Document Name in the Archive System//DOKID"=text, #"ExtractID//ARCID"=text, #"DT//IBLAR"=text, #"Ref. proc.//AWTYP"=text, #"Object Key//AWKEY"=text, #"FMA//FIKRS"=text, #"LCurr//HWAER"=text, #"LCur2//HWAE2"=text, #"LCur3//HWAE3"=text, #"FX Rate 2//KURS2"=text, #"FX Rate 3//KURS3"=text, #"SC//BASW2"=text, #"SC//BASW3"=text, #"Translation Date//UMRD2"=text, #"Translation Date//UMRD3"=text, #"Reversal Flag//XSTOV"=text, #"Reversal Date//STODT"=text, #"Calculate Tax//XMWST"=text, #"CT//CURT2"=text, #"CT//CURT3"=text, #"ERTy//KUTY2"=text, #"ERTy//KUTY3"=text, #"Calculate Taxes on Net Amount//XSNET"=text, #"SCCd//AUSBK"=text, #"Tax details changed//XUSVR"=text, #"Status of Data Transfer to Next Release//DUEFL"=text, #"Log.System//AWSYS"=text, #"Rate for Taxes//TXKRS"=text, #"TaxRate:LC//CTXKRS"=text, #"Request No//LOTKZ"=text, #"Customer Bill/Ex. Paid Before Due Date//XWVOF"=text, #"Reason//STGRD"=text, #"Parked By//PPNAM"=text, #"Parking Date//PPDAT"=text, #"Time//PPTME"=text, #"Parked with//PPTCOD"=text, #"Branch number//BRNCH"=text, #"Pages//NUMPG"=text, #"dis. doc.//ADISC"=text, #"Ref.key (header) 1//XREF1_HD"=text, #"Ref.key (header) 2//XREF2_HD"=text, #"Reversal//XREVERSAL"=text, #"IR date//REINDAT"=text, #"Ld//RLDNR"=text, #"Ledger Group//LDGRP"=text, #"Mand.//PROPMANO"=text, #"Alt Refer Number//XBLNR_ALT"=text, #"Tax Reporting Date//VATDATE"=text, #"Doc.Type//DOCCAT"=text, #"Split Posting//XSPLIT"=text, #"Cash Flow-Relevant Doc.//CASH_ALLOC"=text, #"Follow-on document indicator//FOLLOW_ON"=text, #"Reorg//XREORG"=text, #"Subs//SUBSET"=text, #"ERTy//KURST"=text, #"Market Data Exchange Rate//KURSX"=text, #"Market Data Exchange Rate 2//KUR2X"=text, #"Market Data Exchange Rate 3//KUR3X"=text, #"Document Originates from Multi Currency//XMCA"=text, #"Resubmission Date//RESUBMISSION"=text, #"SendLogSys//LOGSYSTEM_SENDER"=text, #"SenderCCde//BUKRS_SENDER"=text, #"SendDocNo.//BELNR_SENDER"=text, #"Sender FY//GJAHR_SENDER"=text, #"SubID//INTSUBID"=text, #"Rev. Org.//AWORG_REV"=text, #"Rev. Ref.//AWREF_REV"=text, #"RvD//XREVERSING"=text, #"Rev//XREVERSED"=text, #"GL BtGrp//GLBTGRP"=text, #"BTran//CO_VRGNG"=text, #"RT//CO_REFBT"=text, #"ALE: DocNr//CO_ALEBN"=text, #"Val. Date//CO_VALDT"=text, #"SCODocNo//CO_BELNR_SENDER"=text, #"SendCOAr//KOKRS_SENDER"=text, #"AccP//ACC_PRINCIPLE"=text, #"Data Filter Value for Data Aging//_DATAAGING"=text, #"Transfer Variant//TRAVA_PN"=text, #"LDGRP-Specific//LDGRPSPEC_PN"=text, #"Area Limitation//AFABESPEC_PN"=text, #"Sec//XSECONDARY"=text, #"Rep St//REPROCESSING_STATUS_CODE"=text, #"Partial//TRR_PARTIAL_IND"=text, #"RemSt//ITEM_REMOVAL_STATUS"=text, #"LR//PENRC"=text, #"Country Specific Reference 1//GLO_REF1_HD"=text, #"Country Specific Date 1//GLO_DAT1_HD"=text, #"Country Specific Reference 2//GLO_REF2_HD"=text, #"Country Specific Date 2//GLO_DAT2_HD"=text, #"Country Specific Reference 3//GLO_REF3_HD"=text, #"Country Specific Date 3//GLO_DAT3_HD"=text, #"Country Specific Reference 4//GLO_REF4_HD"=text, #"Country Specific Date 4//GLO_DAT4_HD"=text, #"Country Specific Reference 5//GLO_REF5_HD"=text, #"Country Specific Date 5//GLO_DAT5_HD"=text, #"Country Specific Business Partner 1//GLO_BP1_HD"=text, #"Country Specific Business Partner 2//GLO_BP2_HD"=text, #"invoice type (for annexation)//ANXTYPE"=text, #"annexation amount//ANXAMNT"=text, #"annexation percentage//ANXPERC"=text, #"V.A.T indicator//ZVAT_INDC"=text, #"Document Status///SAPF15/STATUS"=text, #"RT//PSOTY"=text, #"Reason//PSOAK"=text, #"Region//PSOKS"=text, #"S//PSOSG"=text, #"File number//PSOFN"=text, #"IF//INTFORM"=text, #"Interest Calc. Date//INTDATE"=text, #"Posting Day//PSOBT"=text, #"Act//PSOZL"=text, #"Chngd On//PSODT"=text, #"Time//PSOTM"=text, #"Transfer type//FM_UMART"=text, #"CTyp//CCINS"=text, #"Card number//CCNUM"=text, #"Payment Statistical Sampling Block//SSBLK"=text, #"Lot//BATCH"=text, #"User Name//SNAME"=text, #"Sampled invoice by Payment Stat. Samplin//SAMPLED"=text, #"PPA Ex.//EXCLUDE_FLAG"=text, #"BL Ind.//BLIND"=text, #"Offset Status//OFFSET_STATUS"=text, #"Date Referred to Treasury//OFFSET_REFER_DAT"=text, #"Doc.Cond.//KNUMV"=text, #"DocumentBlock//BLO"=text, #"Contract No//CNT"=text, #"Doc Type//PYBASTYP"=text, #"Doc No.//PYBASNO"=text, #"Date//PYBASDAT"=text, #"IBAN//PYIBAN"=text, #"Incg Nmbr//INWARDNO_HD"=text, #"Incg Date//INWARDDT_HD"=text, #"Account Type//KOART"=text, #"Amount//WRBTR"=nullable text, #"Amt.in loc.cur.//DMBTR"=nullable text, #"Assignment//ZUONR"=text, #"Baseline Payment Dte//ZFBDT"=text, #"Clearing Document//AUGBL"=text, #"Cost Center//KOSTL"=text, #"Customer//KUNNR"=text, #"G/L Account//HKONT"=text, #"Posting Key//BSCHL"=text, #"Profit Center//PRCTR"=text, #"Reference Key 1//XREF1"=text, #"Reference Key 2//XREF2"=text, #"Special G/L Ind.//UMSKZ"=text, #"Tax Code//MWSKZ"=text, #"Text//SGTXT"=text, #"Trading partner//VBUND"=text, #"Vendor//LIFNR"=text, #"WBS Element//PROJK"=text, Column1=nullable number, Код ОГК ms2=nullable text]}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Cl.//MANDT", "CoCd//BUKRS", "Year//GJAHR", "Doc. Type//BLART", "Doc. Date//BLDAT", "Pstng Date//BUDAT", "Posting period//MONAT", "Entry Dte//CPUDT", "Time//CPUTM", "Changed//AEDAT", "Last Updte//UPDDT", "TranslDate//WWERT", "User Name//USNAM", "TCode//TCODE", "Cross-CC Number//BVORG", "Reference//XBLNR", "RecEnt Doc//DBBLG", "RecYear//DBBLG_GJAHR", "RecCoCode//DBBLG_BUKRS", "Rev. With//STBLG", "Year//STJAH", "Document Header Text//BKTXT", "Crcy//WAERS", "Exch.rate//KURSF", "GrpCy//KZWRS", "Group Exchange Rate//KZKRS", "S//BSTAT", "Net document type//XNETB", "Un.del.cts//FRATH", "I//XRUEB", "Tran//GLVOR", "Session Name//GRPID", "Document Name in the Archive System//DOKID", "ExtractID//ARCID", "DT//IBLAR", "Ref. proc.//AWTYP", "Object Key//AWKEY", "FMA//FIKRS", "LCurr//HWAER", "LCur2//HWAE2", "LCur3//HWAE3", "FX Rate 2//KURS2", "FX Rate 3//KURS3", "SC//BASW2", "SC//BASW3", "Translation Date//UMRD2", "Translation Date//UMRD3", "Reversal Flag//XSTOV", "Reversal Date//STODT", "Calculate Tax//XMWST", "CT//CURT2", "CT//CURT3", "ERTy//KUTY2", "ERTy//KUTY3", "Calculate Taxes on Net Amount//XSNET", "SCCd//AUSBK", "Tax details changed//XUSVR", "Status of Data Transfer to Next Release//DUEFL", "Log.System//AWSYS", "Rate for Taxes//TXKRS", "TaxRate:LC//CTXKRS", "Request No//LOTKZ", "Customer Bill/Ex. Paid Before Due Date//XWVOF", "Reason//STGRD", "Parked By//PPNAM", "Parking Date//PPDAT", "Time//PPTME", "Parked with//PPTCOD", "Branch number//BRNCH", "Pages//NUMPG", "dis. doc.//ADISC", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Reversal//XREVERSAL", "IR date//REINDAT", "Ld//RLDNR", "Ledger Group//LDGRP", "Mand.//PROPMANO", "Alt Refer Number//XBLNR_ALT", "Tax Reporting Date//VATDATE", "Doc.Type//DOCCAT", "Split Posting//XSPLIT", "Cash Flow-Relevant Doc.//CASH_ALLOC", "Follow-on document indicator//FOLLOW_ON", "Reorg//XREORG", "Subs//SUBSET", "ERTy//KURST", "Market Data Exchange Rate//KURSX", "Market Data Exchange Rate 2//KUR2X", "Market Data Exchange Rate 3//KUR3X", "Document Originates from Multi Currency//XMCA", "Resubmission Date//RESUBMISSION", "SendLogSys//LOGSYSTEM_SENDER", "SenderCCde//BUKRS_SENDER", "SendDocNo.//BELNR_SENDER", "Sender FY//GJAHR_SENDER", "SubID//INTSUBID", "Rev. Org.//AWORG_REV", "Rev. Ref.//AWREF_REV", "RvD//XREVERSING", "Rev//XREVERSED", "GL BtGrp//GLBTGRP", "BTran//CO_VRGNG", "RT//CO_REFBT", "ALE: DocNr//CO_ALEBN", "Val. Date//CO_VALDT", "SCODocNo//CO_BELNR_SENDER", "SendCOAr//KOKRS_SENDER", "AccP//ACC_PRINCIPLE", "Data Filter Value for Data Aging//_DATAAGING", "Transfer Variant//TRAVA_PN", "LDGRP-Specific//LDGRPSPEC_PN", "Area Limitation//AFABESPEC_PN", "Sec//XSECONDARY", "Rep St//REPROCESSING_STATUS_CODE", "Partial//TRR_PARTIAL_IND", "RemSt//ITEM_REMOVAL_STATUS", "LR//PENRC", "Country Specific Reference 1//GLO_REF1_HD", "Country Specific Date 1//GLO_DAT1_HD", "Country Specific Reference 2//GLO_REF2_HD", "Country Specific Date 2//GLO_DAT2_HD", "Country Specific Reference 3//GLO_REF3_HD", "Country Specific Date 3//GLO_DAT3_HD", "Country Specific Reference 4//GLO_REF4_HD", "Country Specific Date 4//GLO_DAT4_HD", "Country Specific Reference 5//GLO_REF5_HD", "Country Specific Date 5//GLO_DAT5_HD", "Country Specific Business Partner 1//GLO_BP1_HD", "Country Specific Business Partner 2//GLO_BP2_HD", "invoice type (for annexation)//ANXTYPE", "annexation amount//ANXAMNT", "annexation percentage//ANXPERC", "V.A.T indicator//ZVAT_INDC", "Document Status///SAPF15/STATUS", "RT//PSOTY", "Reason//PSOAK", "Region//PSOKS", "S//PSOSG", "File number//PSOFN", "IF//INTFORM", "Interest Calc. Date//INTDATE", "Posting Day//PSOBT", "Act//PSOZL", "Chngd On//PSODT", "Time//PSOTM", "Transfer type//FM_UMART", "CTyp//CCINS", "Card number//CCNUM", "Payment Statistical Sampling Block//SSBLK", "Lot//BATCH", "User Name//SNAME", "Sampled invoice by Payment Stat. Samplin//SAMPLED", "PPA Ex.//EXCLUDE_FLAG", "BL Ind.//BLIND", "Offset Status//OFFSET_STATUS", "Date Referred to Treasury//OFFSET_REFER_DAT", "Doc.Cond.//KNUMV", "DocumentBlock//BLO", "Contract No//CNT", "Doc Type//PYBASTYP", "Doc No.//PYBASNO", "Date//PYBASDAT", "IBAN//PYIBAN", "Incg Nmbr//INWARDNO_HD", "Incg Date//INWARDDT_HD", "Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK", "Column1", "Код ОГК ms2"}, {"Cl.//MANDT", "CoCd//BUKRS", "Year//GJAHR", "Doc. Type//BLART", "Doc. Date//BLDAT", "Pstng Date//BUDAT", "Posting period//MONAT", "Entry Dte//CPUDT", "Time//CPUTM", "Changed//AEDAT", "Last Updte//UPDDT", "TranslDate//WWERT", "User Name//USNAM", "TCode//TCODE", "Cross-CC Number//BVORG", "Reference//XBLNR", "RecEnt Doc//DBBLG", "RecYear//DBBLG_GJAHR", "RecCoCode//DBBLG_BUKRS", "Rev. With//STBLG", "Year//STJAH", "Document Header Text//BKTXT", "Crcy//WAERS", "Exch.rate//KURSF", "GrpCy//KZWRS", "Group Exchange Rate//KZKRS", "S//BSTAT", "Net document type//XNETB", "Un.del.cts//FRATH", "I//XRUEB", "Tran//GLVOR", "Session Name//GRPID", "Document Name in the Archive System//DOKID", "ExtractID//ARCID", "DT//IBLAR", "Ref. proc.//AWTYP", "Object Key//AWKEY", "FMA//FIKRS", "LCurr//HWAER", "LCur2//HWAE2", "LCur3//HWAE3", "FX Rate 2//KURS2", "FX Rate 3//KURS3", "SC//BASW2", "SC//BASW3", "Translation Date//UMRD2", "Translation Date//UMRD3", "Reversal Flag//XSTOV", "Reversal Date//STODT", "Calculate Tax//XMWST", "CT//CURT2", "CT//CURT3", "ERTy//KUTY2", "ERTy//KUTY3", "Calculate Taxes on Net Amount//XSNET", "SCCd//AUSBK", "Tax details changed//XUSVR", "Status of Data Transfer to Next Release//DUEFL", "Log.System//AWSYS", "Rate for Taxes//TXKRS", "TaxRate:LC//CTXKRS", "Request No//LOTKZ", "Customer Bill/Ex. Paid Before Due Date//XWVOF", "Reason//STGRD", "Parked By//PPNAM", "Parking Date//PPDAT", "Time//PPTME", "Parked with//PPTCOD", "Branch number//BRNCH", "Pages//NUMPG", "dis. doc.//ADISC", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Reversal//XREVERSAL", "IR date//REINDAT", "Ld//RLDNR", "Ledger Group//LDGRP", "Mand.//PROPMANO", "Alt Refer Number//XBLNR_ALT", "Tax Reporting Date//VATDATE", "Doc.Type//DOCCAT", "Split Posting//XSPLIT", "Cash Flow-Relevant Doc.//CASH_ALLOC", "Follow-on document indicator//FOLLOW_ON", "Reorg//XREORG", "Subs//SUBSET", "ERTy//KURST", "Market Data Exchange Rate//KURSX", "Market Data Exchange Rate 2//KUR2X", "Market Data Exchange Rate 3//KUR3X", "Document Originates from Multi Currency//XMCA", "Resubmission Date//RESUBMISSION", "SendLogSys//LOGSYSTEM_SENDER", "SenderCCde//BUKRS_SENDER", "SendDocNo.//BELNR_SENDER", "Sender FY//GJAHR_SENDER", "SubID//INTSUBID", "Rev. Org.//AWORG_REV", "Rev. Ref.//AWREF_REV", "RvD//XREVERSING", "Rev//XREVERSED", "GL BtGrp//GLBTGRP", "BTran//CO_VRGNG", "RT//CO_REFBT", "ALE: DocNr//CO_ALEBN", "Val. Date//CO_VALDT", "SCODocNo//CO_BELNR_SENDER", "SendCOAr//KOKRS_SENDER", "AccP//ACC_PRINCIPLE", "Data Filter Value for Data Aging//_DATAAGING", "Transfer Variant//TRAVA_PN", "LDGRP-Specific//LDGRPSPEC_PN", "Area Limitation//AFABESPEC_PN", "Sec//XSECONDARY", "Rep St//REPROCESSING_STATUS_CODE", "Partial//TRR_PARTIAL_IND", "RemSt//ITEM_REMOVAL_STATUS", "LR//PENRC", "Country Specific Reference 1//GLO_REF1_HD", "Country Specific Date 1//GLO_DAT1_HD", "Country Specific Reference 2//GLO_REF2_HD", "Country Specific Date 2//GLO_DAT2_HD", "Country Specific Reference 3//GLO_REF3_HD", "Country Specific Date 3//GLO_DAT3_HD", "Country Specific Reference 4//GLO_REF4_HD", "Country Specific Date 4//GLO_DAT4_HD", "Country Specific Reference 5//GLO_REF5_HD", "Country Specific Date 5//GLO_DAT5_HD", "Country Specific Business Partner 1//GLO_BP1_HD", "Country Specific Business Partner 2//GLO_BP2_HD", "invoice type (for annexation)//ANXTYPE", "annexation amount//ANXAMNT", "annexation percentage//ANXPERC", "V.A.T indicator//ZVAT_INDC", "Document Status///SAPF15/STATUS", "RT//PSOTY", "Reason//PSOAK", "Region//PSOKS", "S//PSOSG", "File number//PSOFN", "IF//INTFORM", "Interest Calc. Date//INTDATE", "Posting Day//PSOBT", "Act//PSOZL", "Chngd On//PSODT", "Time//PSOTM", "Transfer type//FM_UMART", "CTyp//CCINS", "Card number//CCNUM", "Payment Statistical Sampling Block//SSBLK", "Lot//BATCH", "User Name//SNAME", "Sampled invoice by Payment Stat. Samplin//SAMPLED", "PPA Ex.//EXCLUDE_FLAG", "BL Ind.//BLIND", "Offset Status//OFFSET_STATUS", "Date Referred to Treasury//OFFSET_REFER_DAT", "Doc.Cond.//KNUMV", "DocumentBlock//BLO", "Contract No//CNT", "Doc Type//PYBASTYP", "Doc No.//PYBASNO", "Date//PYBASDAT", "IBAN//PYIBAN", "Incg Nmbr//INWARDNO_HD", "Incg Date//INWARDDT_HD", "Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Clearing Document//AUGBL", "Cost Center//KOSTL", "Customer//KUNNR", "G/L Account//HKONT", "Posting Key//BSCHL", "Profit Center//PRCTR", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Special G/L Ind.//UMSKZ", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "Vendor//LIFNR", "WBS Element//PROJK", "Column1", "Код ОГК ms2"}),
    //Filtering displayed rows: if ALL or null then full stack of rows; if 2 then only pair rows; if >2 then all rows except pair rows
    #"Filtered rows: ALL, >2 or 2" = if #"R/C filter" = "" then #"Expanded Table" else if #"R/C filter" = 2 then Table.SelectRows(#"Expanded Table", each [Count] = 2) else if #"R/C filter" = 3 then Table.SelectRows(#"Expanded Table", each [Count] = 3)  else Table.SelectRows(#"Expanded Table", each [Count] > 2),
    #"Replaced values: point to nothing" = Table.ReplaceValue(#"Filtered rows: ALL, >2 or 2",".",",",Replacer.ReplaceText,{"Exch.rate//KURSF"}),
    // Replace ZR to BR (KZ=KZ, DZ=DZ; stay as it was)
    #"Replaced ZR to BR" = Table.ReplaceValue(#"Replaced values: point to nothing","ZR","BR",Replacer.ReplaceText,{"Doc. Type//BLART"}),
    #"Created NEWKO" = Table.AddColumn(#"Replaced ZR to BR", "Custom", each if [#"Account Type//KOART"] = "D" then [#"Customer//KUNNR"] else if [#"Account Type//KOART"] = "K" then [#"Vendor//LIFNR"] else [Column1]),
    #"Created Custom Profit Center" = Table.AddColumn(#"Created NEWKO", "Custom Profit Center", each if [#"WBS Element//PROJK"] = "" and [#"Cost Center//KOSTL"] = "" then "DEF-TECH" else [#"Profit Center//PRCTR"]),
    #"Changed type: NEWKO" = Table.TransformColumnTypes(#"Created Custom Profit Center",{{"Custom", type text}}),
    #"Change type with Locale (Currency)" = Table.TransformColumnTypes(#"Changed type: NEWKO", {{"Exch.rate//KURSF", type number}}, "ru-RU"),
    #"Removed Other Columns" = Table.SelectColumns(#"Change type with Locale (Currency)",{"DocumentNo//BELNR", "CoCd//BUKRS", "Year//GJAHR", "Doc. Type//BLART", "Doc. Date//BLDAT", "Pstng Date//BUDAT", "Reference//XBLNR", "Document Header Text//BKTXT", "Crcy//WAERS", "Exch.rate//KURSF", "Calculate Tax//XMWST", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Account Type//KOART", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Assignment//ZUONR", "Baseline Payment Dte//ZFBDT", "Cost Center//KOSTL", "Posting Key//BSCHL", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Tax Code//MWSKZ", "Text//SGTXT", "Trading partner//VBUND", "WBS Element//PROJK", "Код ОГК ms2", "G/L Account//HKONT", "Custom", "Custom Profit Center", "Clearing Document//AUGBL"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"DocumentNo//BELNR", "Year//GJAHR", "Doc. Date//BLDAT", "Doc. Type//BLART", "CoCd//BUKRS", "Pstng Date//BUDAT", "Crcy//WAERS", "Exch.rate//KURSF", "Reference//XBLNR", "Document Header Text//BKTXT", "Ref.key (header) 1//XREF1_HD", "Ref.key (header) 2//XREF2_HD", "Calculate Tax//XMWST", "Posting Key//BSCHL", "G/L Account//HKONT", "Custom", "Trading partner//VBUND", "Код ОГК ms2", "Amount//WRBTR", "Amt.in loc.cur.//DMBTR", "Tax Code//MWSKZ", "Assignment//ZUONR", "Text//SGTXT", "Cost Center//KOSTL", "Custom Profit Center", "WBS Element//PROJK", "Reference Key 1//XREF1", "Reference Key 2//XREF2", "Baseline Payment Dte//ZFBDT"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"G/L Account//HKONT", "G/L OLD"},{"Custom", "G/L Account//HKONT"}, {"Код ОГК ms2", "SG"}, {"Custom Profit Center", "Profit Center//PRCTR"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"DocumentNo//BELNR", Order.Ascending}, {"G/L OLD", Order.Descending}})
in
    #"Sorted Rows"

Изменено: Framed - 18.06.2022 20:13:33

Перейти

PQ: Конвертация столбцов с датами и суммами с разными форматами в один общий

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 17.06.2022 10:53:48 mechanix 85, вероятно, мы так и сделаем в конечном итоге, согласен с вами. Файлов сейчас: около 40.
	Перейти

PQ: Конвертация столбцов с датами и суммами с разными форматами в один общий

Framed

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

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

17.06.2022 01:16:38

PooHkrd,

В теории могут быть любыми.
Практически встречал пока только три варианта (они ниже), в т.ч. американский (данных по американскому формату больше всего):

1. dd.mm.yyyy;
2. m/d/yyyy;
3. dd-mm-yyyy.

Добавлю, что в одном файле присутствует лишь один формат; смешения не замечено.

Изменено: Framed - 17.06.2022 02:39:35

Перейти

PQ: Конвертация столбцов с датами и суммами с разными форматами в один общий

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 16.06.2022 23:25:02 PooHkrd, Столбцы с суммами - DMBTR, WRBTR Даты - AUGDT, ZFBDT Изменено: Framed - 16.06.2022 23:25:54
	Перейти

PQ: Конвертация столбцов с датами и суммами с разными форматами в один общий

Framed

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

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

16.06.2022 18:26:21

Коллеги, добрый вечер,

Ситуация следующая: коллеги прислали набор файлов csv с 2019-2022 год, попросили обработать.
Каждый файл - это конкретный месяц конкретного года с набором данных.

Проблема в том, что в исходниках разные форматы: в январском файле дата выглядит как 05-09-2022, в февральском - 9\5\2022, ну и так далее.
Аналогичная ситуация с суммами - в одних файлах формат, в котором тысячный разделитель это точка, а десятичный - запятая (например 56.233,71), а в других - наоборот. Встречается что-то вроде такого: 21,76,65,678.22

Нужно: привести все даты любого формата из исходного состояния к формату ru-RU; а суммы - к формату, в котором тысячного разделителя не будет, а десятичный - это запятая. Уточнение: если это вообще возможно средствами PQ, без вмешательства в исходники.

Что пробовал:
1) с суммами - находить в каждой ячейке столбцов с суммами (их всего два пока что) знак с помощью Text.Middle и Text.Length, и в зависимости от знака проводить манипуляции с Replace.Text каждой ячейки. Работает, но прям сильно-сильно медленнее стало подгружать;
2) с датами - использовать Convert to Local, но возникают ошибки, а способ выше не стал применять пока что.

Посоветуйте варианты, спасибо заранее.

Перейти

PQ: Преобразование каждой второй строки таблицы в дополнительные столбцы

Framed

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

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

02.06.2022 07:55:36

Коллеги, доброе утро,

Имеется большая таблица данных, преобразованная в PQ. Особенность её в том, что, условно - ячейки каждых двух строк первых трех столбцов дублируют друг-друга (первый столбец содержит ключи), а, скажем 4-7 столбцы содержат в себе значения, которые могут и не дублироваться.
Задача в том, чтобы преобразовать каждые две строки в одну таким образом, чтобы информация, содержащаяся в ячейках каждой второй строки столбцов 4-7 перенеслась вправо в новые столбцы, дублирующие (само собой, со знаком отличия) названия столбцов-исходников.
Также: таблица преобразованна таким образом, что каждого ключа в ней по 2 шт., это строгое правило.

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

Помогите, пожалуйста, советом: нужно решить с помощью PQ, при этом не дублируя уже существующий запрос для его последующего преобразования и объединения с копией (т.к. они действительно очень большие). Может, есть шанс решить это транспонированием или группировкой?

Спасибо заранее.

Изменено: Framed - 02.06.2022 08:06:14

Перейти

PQ: Фильтрация итогового массива данных в зависимости от множества условий

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 13.01.2022 15:59:05 Спасибо, помогло.
	Перейти

PQ: Фильтрация итогового массива данных в зависимости от множества условий

Framed

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

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

13.01.2022 14:00:56

Коллеги, добрый день,

Имеется источник данных - файл с проектами.
С помощью PQ источник разделяется на несколько одинаковых по структуре файлов, каждый из которых предназначен для одного конкретного админа (т.е. каждый админ видит только те проекты, которые к нему относятся).
В массиве данных( в файлах админов) каждый проект повторяется 12 раз (по разу в месяц), а админы вносят в соседние колонки информацию по проекту для каждого месяца.

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

Проблема: потеря данных, необходимость постоянно согласовывать заранее изменения в проектах и также заранее переносить информацию.

Что нужно - построить запрос таким образом, чтобы:

1. Если в течение всего года админ не менялся, то ничего;
2. Если в течение года админ хотя бы раз менялся, то:
2.1 Для передающего (т.е. для Ивана) проект оставался в его файле после обновления вплоть до месяца, в котором произошло изменение (например, Иван передал проект в августе, значит должен видеть в своем файле январь-июль этого проекта, а после - не видеть);
2.2 Получающий проект (т.е. Дмитрий) после изменения мог видеть как остаток года (август -декабрь), так и период январь-июль (главное, чтобы появились строки с проектами, даже без информации, которую Иван заполнял в течение этого периода), т.к. однозначно может возникнуть ситуация, когда понадобится предоставить информацию по предыдущим периодам;
3. Если проект передался третьему (4-ому, 5-ому, и т.д) админу, то правило, в целом, то же: новый админ видит все месяцы последующие месяцы и предыдущие, а старый - всё до месяца передачи.

Отвечу на любые вопросы по теме, если они будут.

Приложил порезаные, но рабочие файлы, со всеми запросами и структурой.

Изменено: Framed - 13.01.2022 14:05:06

Перейти

Включение и выключение итоговой строки в умных таблицах

Framed

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

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

24.09.2020 10:07:35

Дмитрий(The_Prist) Щербаков,

Разбиение не установлено.
Сам автофильтр стоит в каждой таблице, конечно же, но по нему ничего не отфильтровано.
Группировка есть, как по столбцам, так и по строкам, до второго уровня (по строкам скрыты некоторые ключи, о которых я подробнее напишу ниже, по столбцам - некоторые малозначимые колонки).
Переносится ~15000 строк, столбцов ~35. На всякий случай, ниже пишу детально о переносе информации.

Вообще, как переносится всё: во второй строке каждого листа, в соответствующих каждому столбцу таблицы ячейках прописан ключ - название столбца таблицы источника (эта таблица находится в этой же книге, на отдельном листе). Соответственно, один массив я заполняю единожды - это названия столбцов источника; второй массив заполняется ключами из листа, на который я хочу перенести данные. Плюс, по определенным правилам, автоматически определяется по источнику, какие листы вообще заполняются, а какие нет. Когда все исходные условия определены, я запускаю цикл по каждому листу, который должен заполняться, внутри которого каждый раз, опять-таки, с помощью циклов For... Next сравниваются ключи по двум первым массивам. Если есть совпадение, то уже в третий массив я загоняю все данные столбца (например, arrTransfer = tblSource.ListColumns(n).DataBodyRange.Value) и осуществляю .Resize. Перед тем, как цикл перейдет к обработке следующего листа, я стираю второй масив ключей, первый и третий не трогаю.

Перейти

Включение и выключение итоговой строки в умных таблицах

Framed

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

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

22.09.2020 19:20:47

Дмитрий(The_Prist) Щербаков,

Я пробовал всё.
Стандартно ставлю везде:
Application.ScreenUpdate = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual.

Условного форматирования нет вообще.

По F8 прогонял, никаких событий нет до момента отключения total row в умной таблице (это первое событие, которое приводит к тому, что, если файл большой, то он зависает минуты на 2-3).

Еще, из того, что удалось обнаружить: например, если я заполняю таблицы по порядку, начиная с нулевого листа (~15000 строк), то первая таблица заполняется за 10 секунд, следующие заполняются - 1-2 минуты. При этом, у меня реализована возможность с помощью чекбоксов выбрать, какие именно таблицы заполнять. Так вот, если разрушить эту цепочку, и заполнять не по порядку, а, скажем, первый лист, потом третий, потом пятый, то время заполнения каждой таблицы будет 10-15 секунд; т.е., я хочу сказать, что процесс пойдет гораздо быстрее.

Еще: при условии, что все таблицы заполнены информацией, то видно, что самый долгий отклик на событие у нулевого листа, а самый быстрый - у последнего листа, что, наверное, логично.

И еще: насколько я понял, любое событие на листе заставляет Excel пересчитывать все формулы. Например, даже если я удаляю пустую строку вне заполненной умной таблицы текущего листа (пускай это будет нулевой лист, самый долгий), или отключаю или включаю сабтотал у второй умной таблицы, которая вообще пустая, то файл дико виснет. Чем дальше я от нулевого листа, тем быстрее все это происходит. xlCalculationManual не помогает здесь вообще, почему, я не знаю.

Сейчас, т.к. в текущей версии я потер все формулы, каждый лист заполняется за 12-18 секунд.

Изменено: Framed - 22.09.2020 19:24:46

Перейти

Включение и выключение итоговой строки в умных таблицах

Framed

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

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

22.09.2020 18:51:47

PooHkrd, к сожалению, так и есть. Конечные пользователи абсолютно не умеют обращаться с этим тулом, но при этом хотят иметь возможность прямо влиять на определенные вещи, к примеру, изменять порядок столбцов в шаблоне, менять их название, прописывать формулы и т.д.

Изменено: Framed - 22.09.2020 19:02:35

Перейти

Включение и выключение итоговой строки в умных таблицах

Framed

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

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

22.09.2020 16:35:09

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

В файле, помимо всего прочего, есть 13 определенных листов, каждый из которых содержит по 2 умные таблицы. Все эти таблицы одинаковой структуры, изначально все пустые. В определенных столбцах каждой таблицы (всего таких столбцов 5 шт.) прописаны формулы. На нулевом листе (самом первом по списку) в эти столбцы подтягиваются определенные данные из другой таблицы, которая очень редко изменятся (раз в год) и сама по себе не очень большая. Формулы в аналогичных столбцах таблиц следующих листов (1-12) сперва ссылаются на предыдущую таблицу, проверяя по ключу, существовал ли он там ранее, и, в зависимости от этого, показывают определенный результат.

Если короче, суть в том, что все таблицы связаны через формулы, и это очень сильно влияет на определенные события в книге, например, удаление строк, выключение сабтоталов (причем, неважно, удаляю я строки внутри или вне таблицы, или же выключаю/включаю сабтотал в таблице, в которой вообще нет ничего - все равно файл будет виснуть), и так далее, и чем больше файл (~ больше строк в таблицах), тем медленнее эти события реализуются.

Пока решение принял следующее: просто поубирал все формулы, которые ссылаются на предыдущий объект.
Сами формулы и подобные столбцы - требование заказчика.

Перейти

Включение и выключение итоговой строки в умных таблицах

Framed

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

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

22.09.2020 16:16:08

Михаил Витальевич С.,

1. Требование заказчика;
2. Мне проще ссылаться на диапазоны в VBA, в столбцах с формулами, и т.д.;

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

Перейти

Включение и выключение итоговой строки в умных таблицах

Framed

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

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

18.09.2020 09:36:05

Коллеги, добрый день,

Проконсультируйте, пожалуйста.
Имеется файл. Если коротко: он весит 17 мб, формат .xlsx, в нем очень много формул и умных таблиц.

Проблема конкретно с умными таблицами, в которых ~15 тысяч строк. Суть проблемы: если включить/выключить у таблицы указанного размера строку итогов, то это происходит ну прям очень долго, а файл в это время в состоянии not responding. Причем, что вручную, что с помощью VBA, результат один. Application.Calculation = xlManual не помогает.

Вопрос: почему это происходит (очень долгая реакция на выключение total row) и можно ли это как-нибудь вылечить (кроме как отказом от умных таблиц), потому что таких таблиц, теоретически, может быть 26, а мой макрос не может разнести каких-то жалких 600 строк между 4 таблицами даже за полчаса из-за этого.

Важное уточнение: вполне возможно, что дело не только в выключении/включении строки итогов, а вообще в любых манипуляциях с умной таблицей: далее, мой макрос должен будет полностью удалить все строки в таблице с помощью .DataBodyRange.Rows.Delete, а потом перенести новую информацию из источника, и я вполне допускаю, что на данном этапе тоже будет похожая проблема.

Очень жду советов, спасибо.

Перейти

VBA: Поиск и перенос объекта по ключу

Framed

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

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

21.02.2020 15:12:06

Коллеги, добрый день,

Подскажите, пожалуйста, советами или конкретным решением.

Имеется две книги, в первой находится отсортированный список вопросов, в другой: такой же список вопросов, но без сортировки, а также во второй книге находятся ответы.
Задача: притянуть ответы по каждому вопросу в первую книгу.

Сложность в том, что ответы это не просто текст в ячейке: это может быть текст, может быть изображение, а может быть несколько чекбоксов со значением True или элементы ActiveX Control. В общем, как корретно это все перенести по ключам? С помощью какого инструмента?

Изменено: Framed - 21.02.2020 15:14:47

Перейти

VBA: Автоматическая проверка и установка надстройки в ленту без вспомогательных файлов

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 18.02.2020 16:58:07 PooHkrd, да, так и есть, вообще странно, что сам не догадался, спасибо вам. Alemox, попробовал, помогло, пошел работать дальше, спасибо.
	Перейти

VBA: Автоматическая проверка и установка надстройки в ленту без вспомогательных файлов

Framed

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

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

18.02.2020 16:31:16

Коллеги, добрый день,

Заранее прошу прощения, я знаю, что таких тем миллион: я поиском пользоваться умею и смотрел предложенные варианты, но все равно хочу спросить здесь.

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

Главное отличие от всех способов, которые я встречал: то, что я открываю не вспомогательный файл с кодом, а саму надстройку; для меня это принципиальный момент. Соответственно, меня в принципе интересует, возможно ли практически сделать так, как я описал выше, и если да, то как?

Ниже код, который у меня есть на данный момент, он расположен в ThisWorkbook надстройки. На этапе FileCopy ругается ошибкой (Permission denied)

Код

Private Sub Workbook_Open()
    Dim sFullFileName$, sFolder$, sFileName$
    sFileName = ThisWorkbook.Name
    sFullFileName = ThisWorkbook.FullName
    sFolder = Application.UserLibraryPath
    If Len(Dir(sFolder & sFileName)) = 0 Then
        FileCopy sFullFileName, sFolder
    Else
        MsgBox False
    End If
End Sub

Перейти

VBA: Выбор всех сабтоталов в сводной таблице

Framed

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

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

22.01.2020 12:37:48

Vladimir Chebykin, я изучу, но мне все-таки нужен способ выделить промежуточные итоги другим способом, потому что сводная таблица создается каждый раз, и каждый раз надо подсвечивать, а создавать с помощью VBA для этого отдельный стиль каждый раз кажется мне слегка перебором

Перейти

VBA: Выбор всех сабтоталов в сводной таблице

Framed

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

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

21.01.2020 18:47:43

Коллеги, добрый день,

Собственно, вопрос в названии темы: как с помощью VBA выбрать (или покрасить) строки, которые subtotals в любой сводной таблице?
Есть какой-нибудь метод, который мне выделит сразу все сабтоталы в сводной или надо выбирать по одному?

Спасибо.

Перейти

VBA: Правильная ссылка на книгу в формуле VLOOKUP в ячейке умной таблицы

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 20.12.2019 15:46:45 Ігор Гончаренко, спасибо, помогло. Символы не лишние, все верно.
	Перейти

VBA: Правильная ссылка на книгу в формуле VLOOKUP в ячейке умной таблицы

Framed

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

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

20.12.2019 15:10:10

Коллеги, добрый день,

Пожалуйста, подскажите решение; я начну немного издалека.

Имеется форма VBA, с помощью которой пользователь должен выбрать 5 разных файлов, после чего, нажав кнопку "ОК", макрос обработает данные в этих файлах и сформирует отчет из нескольких листов в определенной форме.

Соответственно, в макросе несколько модулей, но основной механизм простой: каждый файл открывается с помощью GetObject, оттуда берется информация (иногда она сразу заносится в массив, а иногда я использую просто VLOOKUP), и все переносится в отдельно созданный файл, после чего файл-исходник закрывается без сохранения.

В чем проблема: в том, что в некоторых файлах-исходниках есть умная таблица, плюс они огромные по объему (20к+ строк и около 20 столбцов). В файле-отчете, куда переносится информация, тоже используются умные таблицы. В результате, я решил, что в таких случаях буду применять обычный VLOOKUP, т.к. способом сделать перенос информации быстрее я не владею. Таким образом, с помощью VBA, я просто в первую ячейку нужного мне столбца умной таблицы прописываю формулу VLOOKUP; т.к. это умная таблица, то остальные ячейки столбца также заполняются данными, и я просто следующим шагом превращаю все это в значения.

Часть кода:

Код

<...>        
strStrFile = "" & Dir(frmUtl.txbStructure.Value) & "!" 'Здесь берется название файла, который выбрал пользователь в UserForm + "!" 
With .ListColumns("Department").DataBodyRange 
    .FormulaR1C1 = "=VLOOKUP([@[Pers.'#]]," & strStrFile & "Str[#Data],5,0)" 'Здесь я конструирую формулу в умной таблице
    .Cells.Value = .Cells.Value
End With
<...>

Проблема:

- если у файла имя односложное, без определенных символов, то формула нормально срабатывает и подтягивает данные, но!
- если у файла имя, например, "SQ01_2019_12-1_structure", то формула получается =VLOOKUP([@[Pers.'#]];SQ01_2019_12-'1_structure.XLSX'!Str[#Data];5;0), и она уже не срабатывает (добавляются лишние символы ') - результат формулы #NAME?

Как обойти/решить эту проблему?

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

Перейти

Конвертирование .xlsx в .txt с сохранением табличной структуры

Framed

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

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

04.12.2019 17:14:34

Коллеги, добрый вечер,

Подскажите, пожалуйста, как пересохранить xlsx в txt таким образом, чтобы в txt формате информация не "съезжала"? То есть, если в моем исходном файле под, например, столбцом "C" вся соответствующая этому столбцу информация, то в txt формате все съезжает в зависимости от названий столбцов (если я правильно понял причину).
В общем, как конвертировать так, чтобы сохранилась наглядная структура?
Спасибо.

Перейти

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

Framed

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

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

01.11.2019 15:24:14

Коллеги, добрый день,

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

Сейчас использую:

Код
WorksheetFunction.VLookUp(arr1(i,1), arr2, 2, False)

, но во втором массиве больше ста тысяч строк, процесс идет долго.
Как ускорить?

Перейти

VBA: Не работает надстройка после перехода на Windows 10, Run-time error 432 и Can't find project or library

Framed

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

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

30.09.2019 13:20:25

Дмитрий(The_Prist) Щербаков, понял.

Версия офиса: MO 365 ProPlus, v. 1902 (Build 11328.20420);
GetObject должен запустить обычный excel файл формата .XLSX

С кодом да, сложнее, не знаю, как его выложить сюда и показать именно часть с ошибкой.
В теории, я могу просто попробовать вручную подключить MISSING библиотеку, но надстройкой пользуются человек 20, и просто хотелось бы избежать вот этих ручных манипуляций.

Перейти

VBA: Не работает надстройка после перехода на Windows 10, Run-time error 432 и Can't find project or library

Framed Пользователь Сообщений: 223 Регистрация: 25.01.2018	# 30.09.2019 13:01:07 Дмитрий(The_Prist) Щербаков, я убрал, как раз библиотека с названием из п.4 и была MISSING. Но не помогло, да.
	Перейти

VBA: Не работает надстройка после перехода на Windows 10, Run-time error 432 и Can't find project or library

Framed

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

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

30.09.2019 12:53:21

Коллеги, добрый день,

В офисе происходит постепенная миграция на Windows 10 с Windows 7. Надстройка, которой пользуются коллеги, работала на семерке без проблем, а на десятке стали возникать проблемы:

1. Run-time error '432', когда компилятор пытается выполнить, например, Set x = GetObject("PathName");

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

2. Компилятор не выполняет код, если какие-то переменные не объявлены, хотя Require Variable Declaration не активно. Возникает ошибка "Can't find project or library";
3. Функции Trim и Mid также не распознаются, пока не исправил на VBA.Trim и VBA.Mid, иначе ошибка из п.2;
4. Microsoft Common Dialog Control 6.0 (SP6) больше не активно; это логично, потому что, насколько мне известно, эта библиотека не входит в пакет Windows 10 и поэтому мы просто её отключили. Пишу этот пункт для инфы, вдруг дело в этом.

Пожалуйста, если есть идеи, как это исправить, поделитесь.

Перейти