Yaqinda bir do'stim menga berilgan so'zlar to'plamidan iborat barcha mumkin bo'lgan iboralarni yaratishda yordam berishni so'rab murojaat qildi. Ushbu turdagi muammolar onlayn reklama va SEO reklamasi uchun kalit so'zlar va iboralar ro'yxatini tuzishda, qidiruv so'rovida so'zlarning barcha mumkin bo'lgan almashtirishlarini ko'rib chiqish kerak bo'lganda paydo bo'lishi mumkin:
Matematikada bu operatsiya deyiladi Dekart mahsuloti. Rasmiy taʼrif quyidagicha: A va B toʻplamlarning dekart koʻpaytmasi barcha juftliklar toʻplami boʻlib, uning birinchi komponenti A toʻplamga, ikkinchi komponenti esa B toʻplamga tegishli. Bundan tashqari, toʻplamlarning elementlari ikkalasi ham boʻlishi mumkin. raqamlar va matn.
Inson tiliga tarjima qilinganda, bu shuni anglatadiki, agar A to'plamida bizda, masalan, "oq" va "qizil" so'zlari va B to'plamida "BMW" va "Mercedes" bo'lsa, u holda bu ikki to'plamning Dekart ko'paytmasidan keyin biz chiqish - bu ikkala ro'yxatdagi so'zlardan tuzilgan iboralarning barcha mumkin bo'lgan variantlari to'plami:
- oq bmw
- qizil bmw
- oq Mercedes
- qizil mersedes
... ya'ni bizga kerak bo'lgan narsa. Keling, Excelda ushbu vazifani hal qilishning bir nechta usullarini ko'rib chiqaylik.
1-usul. Formulalar
Keling, formulalardan boshlaylik. Faraz qilaylik, dastlabki ma'lumotlar sifatida bizda mos ravishda A, B va C ustunlarida asl so'zlarning uchta ro'yxati mavjud va har bir ro'yxatdagi elementlar soni har xil bo'lishi mumkin:
Birinchidan, indekslar bilan uchta ustun yasaymiz, ya'ni har bir ro'yxatdagi so'zlarning tartib raqamlari barcha mumkin bo'lgan kombinatsiyalarda. Birliklarning birinchi qatori (E2:G2) qo'lda kiritiladi, qolganlari uchun biz quyidagi formuladan foydalanamiz:
Bu erda mantiq oddiy: agar yuqori oldingi katakdagi indeks allaqachon ro'yxat oxiriga yetgan bo'lsa, ya'ni funktsiya tomonidan hisoblangan ro'yxatdagi elementlar soniga teng bo'lsa. COUNT (COUNTA), keyin raqamlashni qayta boshlaymiz. Aks holda, indeksni 1 ga oshiramiz. Dollar belgilari ($) bilan diapazonlarni oqilona aniqlashga alohida e'tibor bering, shunda formulani pastga va o'ngga ko'chirishingiz mumkin.
Endi bizda har bir ro‘yxatda kerakli so‘zlarning tartib raqamlari mavjud bo‘lsa, funksiya yordamida so‘zlarning o‘zini ajratib olishimiz mumkin. INDEKS (INDEKS) uchta alohida ustunga:
Agar siz ilgari ishingizda ushbu funktsiyaga duch kelmagan bo'lsangiz, men sizga uni hech bo'lmaganda diagonal ravishda o'rganishni maslahat beraman - bu ko'p holatlarda yordam beradi va undan kam emas (va undan ham ko'proq!) VPR (KO'RISH).
Shundan so'ng, faqat birlashma belgisi (&) yordamida hosil bo'lgan qismlarni qatorga yopishtirish qoladi:
… yoki (agar sizda Excelning so'nggi versiyasi bo'lsa) qulay funksiya bilan ARALASHTIRMOQ (TEXTJOIN)ma'lum bir ajratuvchi belgi (bo'shliq) orqali ko'rsatilgan hujayralarning barcha tarkibini yopishtirishi mumkin:
2-usul. Power Query orqali
Power Query Microsoft Excel uchun ikkita asosiy vazifani bajaradigan kuchli plagindir: 1. Excelga deyarli har qanday tashqi manbadan maʼlumotlarni yuklash va 2. yuklangan jadvallarning barcha turlarini oʻzgartirish. Power Query allaqachon Excel 2016-2019-ga o'rnatilgan va Excel 2010-2013 uchun u alohida plagin sifatida o'rnatilgan (siz uni Microsoft rasmiy veb-saytidan bepul yuklab olishingiz mumkin). Agar siz hali ishingizda Power Query-dan foydalanishni boshlamagan bo'lsangiz, unda bu haqda o'ylash vaqti keldi, chunki yuqorida tavsiflanganlarga o'xshash transformatsiyalar u erda oson va tabiiy ravishda, atigi bir nechta harakatlarda amalga oshiriladi.
Birinchidan, manba ro'yxatlarini Power Query-ga alohida so'rovlar sifatida yuklaymiz. Buning uchun har bir jadval uchun quyidagi amallarni bajaring:
- Keling, tugma yordamida jadvallarni “aqlli”larga aylantiraylik Jadval sifatida formatlash tab Bosh sahifa (Uy — Jadval sifatida formatlash) yoki klaviatura yorlig'i Ctrl+T. Har bir jadvalga avtomatik ravishda nom beriladi Jadval 1,2,3…, ammo, agar xohlasangiz, yorliqda o'zgartirilishi mumkin konstruktor (Dizayn).
- Jadvaldagi faol katakchani o'rnatib, tugmani bosing Stoldan (Jadvaldan) tab ma'lumotlar (Sana) yoki yorliqda Quvvat so'rovi (agar siz uni Excel 2010-2013 uchun alohida plagin sifatida o'rnatgan bo'lsangiz).
- Ochilgan so'rovlar muharriri oynasida buyruqni tanlang Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash..) va keyin variant Faqat ulanishni yarating (Faqat ulanish yaratish). Bu yuklangan jadvalni xotirada qoldiradi va kelajakda unga kirish imkonini beradi.
Agar siz hamma narsani to'g'ri qilsangiz, o'ng paneldagi chiqish rejimda uchta so'rov bo'lishi kerak Faqat ulanish jadval nomlari bilan:
Endi birinchi so'rovni o'ng tugmasini bosing va buyruqni tanlang aloqa (Malumot)uning yangilanadigan nusxasini yaratish va keyin buyruq orqali ma'lumotlarga qo'shimcha ustun qo'shish Ustun qo'shish ž - Maxsus ustun (Ustun qo'shish -ž Maxsus ustun). Formulani kiritish oynasida yangi ustun nomini (masalan, Fragment2) va formula sifatida juda oddiy ifodani kiriting:
=2-jadval
… ya'ni, boshqacha qilib aytganda, ikkinchi so'rovning nomi:
Tugmasini bosgandan so'ng OK biz yangi ustunni ko'ramiz, uning har bir katagida ikkinchi jadvaldagi iboralar bilan ichki jadval bo'ladi (agar siz so'z yonidagi katakchaning fonida bossangiz, ushbu jadvallarning mazmunini ko'rishingiz mumkin. stol):
Olingan ustunning sarlavhasidagi ikkita strelkali tugma yordamida va belgini olib tashlash orqali ushbu ichki o'rnatilgan jadvallarning barcha mazmunini kengaytirish qoladi. Prefiks sifatida asl ustun nomidan foydalaning (Prefiks sifatida asl ustun nomidan foydalaning):
... va biz dastlabki ikkita to'plamdan elementlarning barcha mumkin bo'lgan kombinatsiyalarini olamiz:
Bundan tashqari, hamma narsa o'xshash. Formula bilan boshqa hisoblangan ustun qo'shing:
=3-jadval
..., va keyin ichki o'rnatilgan jadvallarni yana kengaytiring - va endi bizda mos ravishda uchta to'plamdan so'zlarni almashtirish uchun barcha mumkin bo'lgan variantlar mavjud:
Tutib, chapdan o'ngga barcha uchta ustunni tanlash qoladi Ctrl, va buyruq yordamida bo'shliqlar bilan ajratilgan tarkiblarini birlashtiring Ustunlarni birlashtirish (ustunlarni birlashtirish) tabdan O'tkazish (O'zgartirish):
Olingan natijalar allaqachon tanish bo'lgan buyruq yordamida varaqqa tushirilishi mumkin Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash..):
Agar kelajakda bizning manba jadvallarimizda parchalar bilan biror narsa o'zgarsa, hosil bo'lgan jadvalni sichqonchaning o'ng tugmasi bilan bosish va buyruqni tanlash orqali yaratilgan so'rovni yangilash kifoya qiladi. Yangilash va saqlash (yangilash) yoki klaviatura yorliqlarini bosish orqali Ctrl+Alt+F5.
- Power Query, Power Pivot, Power Map va Power BI nima va ularga Excel foydalanuvchisi nima uchun kerak
- Power Query dasturida Gantt diagrammasini yaratish
- INDEX funksiyasidan foydalanishning 5 ta usuli