Жақында біз XML деректерін Интернеттен импорттау үшін FILTER.XML функциясын пайдалануды талқыладық – бұл функция шын мәнінде арналған негізгі тапсырма. Жолда бұл функцияның тағы бір күтпеген және әдемі қолданылуы пайда болды - жабысқақ мәтінді ұяшықтарға жылдам бөлу үшін.
Бізде келесідей деректер бағаны бар делік:
Әрине, ыңғайлы болу үшін оны жеке бағандарға бөлгім келеді: компания атауы, қала, көше, үй. Сіз мұны әртүрлі жолдармен жасай аласыз:
- пайдалану Бағандар бойынша мәтін қойындысынан мәліметтер (Деректер — бағандарға мәтін) және үш қадамға барыңыз Мәтінді талдаушы. Бірақ ертең деректер өзгерсе, бүкіл процесті қайтадан қайталауға тура келеді.
- Бұл деректерді Power Query қызметіне жүктеп, оны сол жерге бөліңіз, содан кейін оны қайтадан параққа жүктеп салыңыз, содан кейін деректер өзгерген кезде сұрауды жаңартыңыз (бұл оңайырақ).
- Егер сізге жылдам жаңарту қажет болса, үтірлерді табу және олардың арасындағы мәтінді шығару үшін өте күрделі формулаларды жазуға болады.
Сіз мұны әлдеқайда талғампаз етіп жасай аласыз және FILTER.XML функциясын пайдалана аласыз, бірақ оның оған қандай қатысы бар?
FILTER.XML функциясы бастапқы аргумент ретінде XML кодын алады — арнайы тегтермен және атрибуттармен белгіленген мәтінді, содан кейін бізге қажетті деректер фрагменттерін шығарып, оны құрамдастарына талдайды. XML коды әдетте келесідей көрінеді:
XML-де әрбір деректер элементі тегтерге қосылуы керек. Тег - бұл бұрыштық жақшаға алынған кейбір мәтін (жоғарыдағы мысалда ол менеджер, атау, пайда). Тегтер әрқашан жұп болып келеді – ашу және жабу (басына қиғаш сызық қосылған).
FILTER.XML функциясы бізге қажет барлық тегтердің мазмұнын оңай шығарып алады, мысалы, барлық менеджерлердің аттары және (ең бастысы) олардың барлығын бір тізімде бірден көрсете алады. Сонымен, біздің міндетіміз бастапқы мәтінге тегтерді қосу, оны FILTER.XML функциясы арқылы кейінгі талдау үшін қолайлы XML кодына айналдыру.
Мысал ретінде тізімнен бірінші мекенжайды алсақ, оны осы құрылысқа айналдыруымыз керек:
Мен бүкіл мәтіндік тегті жаһандық ашу және жабу деп атадым t, және әрбір элементті жақтайтын тегтер s., бірақ сіз кез келген басқа белгілерді пайдалана аласыз - бұл маңызды емес.
Егер біз осы кодтан шегіністерді және жол үзілімдерін алып тастасақ – толық, айтпақшы, міндетті емес және тек түсінікті болу үшін қосылса, мұның бәрі жолға айналады:
Оны бастапқы мекенжайдан ондағы үтірлерді бірнеше тегтермен ауыстыру арқылы оңай алуға болады. функциясын пайдалану СУБСТИТУТ (АЛМАСТЫРУ) және таңбамен жапсыру & ашу және жабу тегтерінің басында және соңында:
Алынған ауқымды көлденеңінен кеңейту үшін стандартты функцияны қолданамыз КӨЛІК (TRANSPOSE), оған формуламызды орап:
Бұл дизайнның маңызды ерекшелігі динамикалық массивтерге қолдау көрсететін Office 2021 және Office 365 жаңа нұсқаларында енгізу үшін арнайы қимылдар қажет емес – жай ғана енгізіп, түймесін басыңыз. кіру – формуланың өзі қажетті ұяшықтардың санын алады және бәрі соққымен жұмыс істейді. Динамикалық массивтер болмаған алдыңғы нұсқаларда формуланы енгізбес бұрын алдымен бос ұяшықтардың жеткілікті санын таңдау керек (сіз шетпен жасай аласыз) және формуланы жасағаннан кейін пернелер тіркесімін басыңыз Ctrl+ауысым+кіруоны массив формуласы ретінде енгізу үшін.
Ұқсас трюкті жол үзілімі арқылы бір ұяшыққа біріктірілген мәтінді бөлу кезінде қолдануға болады:
Алдыңғы мысалдан жалғыз айырмашылығы - бұл жерде үтірдің орнына 10 кодымен CHAR функциясын пайдаланып формулада көрсетуге болатын көрінбейтін Alt + Enter жол үзілімінің таңбасын ауыстырамыз.
- Excel бағдарламасында жол үзілімдерімен (Alt + Enter) жұмыс істеудің нәзіктіктері
- Excel бағдарламасында мәтінді бағандар бойынша бөлу
- Мәтінді SUBSTITUTE арқылы ауыстыру