اخیراً یکی از دوستانم با درخواست کمک به من در تولید همه عبارات ممکن متشکل از مجموعه ای از کلمات داده شده به من مراجعه کرد. مشکلاتی از این دست می‌توانند هنگام جمع‌آوری فهرستی از کلمات کلیدی و عبارات برای تبلیغات آنلاین و ارتقاء سئو ایجاد شوند، زمانی که شما نیاز به بررسی همه جابجایی‌های ممکن کلمات در یک عبارت جستجو دارید:
در ریاضیات به این عمل می گویند ضرب دکارتی. تعریف رسمی به این صورت است: حاصل ضرب دکارتی مجموعه های A و B مجموعه ای از تمام جفت ها است که جزء اول آن متعلق به مجموعه A و جزء دوم متعلق به مجموعه B است. همچنین عناصر مجموعه ها می توانند هر دو باشند. اعداد و متن
ترجمه شده به زبان انسانی، به این معنی است که اگر در مجموعه A، برای مثال، کلمات "سفید" و "قرمز" و در مجموعه B "BMW" و "Mercedes" داشته باشیم، پس از حاصلضرب دکارتی این دو مجموعه، ما get on the output مجموعه ای از همه انواع ممکن عبارات است که از کلمات هر دو لیست تشکیل شده است:
- بی ام و سفید
- بی ام و قرمز
- مرسدس سفید
- مرسدس قرمز
... یعنی فقط چیزی که ما نیاز داریم. بیایید به چند روش برای حل این کار در اکسل نگاه کنیم.
روش 1. فرمول ها
بیایید با فرمول ها شروع کنیم. بیایید فرض کنیم که به‌عنوان داده‌های اولیه، به ترتیب سه فهرست از کلمات اصلی در ستون‌های A، B و C داریم و تعداد عناصر در هر فهرست می‌تواند متفاوت باشد:
ابتدا بیایید سه ستون با شاخص، یعنی تعداد ترتیبی کلمات از هر لیست در تمام ترکیبات ممکن بسازیم. ردیف اول واحدها (E2:G2) به صورت دستی وارد می شود و برای بقیه از فرمول زیر استفاده می کنیم:
منطق در اینجا ساده است: اگر شاخص در سلول قبلی قبلی به انتهای لیست رسیده باشد، یعنی برابر با تعداد عناصر موجود در لیست باشد که توسط تابع محاسبه می شود. COUNT (COUNTA)، سپس شماره گذاری را مجدداً راه اندازی می کنیم. در غیر این صورت، شاخص را 1 افزایش می دهیم. به تثبیت هوشمندانه محدوده ها با علائم دلار ($) توجه ویژه ای داشته باشید تا بتوانید فرمول را به پایین و به راست کپی کنید.
اکنون که اعداد ترتیبی کلمات مورد نیاز از هر لیست را داریم، می‌توانیم خود کلمات را با استفاده از تابع استخراج کنیم. شاخص (فهرست مطالب) در سه ستون مجزا:
اگر قبلاً با این عملکرد در کار خود برخورد نکرده اید، به شما توصیه می کنم حداقل به صورت مورب آن را مطالعه کنید - در بسیاری از موقعیت ها کمک می کند و نه کمتر (و حتی بیشتر!) مفید است. VPR (VLOOKUP).
خوب، پس از آن، فقط چسباندن قطعات حاصل از خط به خط با استفاده از نماد الحاق (&) باقی می ماند:
… یا (اگر آخرین نسخه اکسل را دارید) با عملکرد مفید آمیختن (TEXTJOIN)، که می تواند کل محتویات سلول های مشخص شده را از طریق یک کاراکتر جداکننده معین (فضا) بچسباند:
روش 2. از طریق Power Query
Power Query یک افزونه قدرتمند برای مایکروسافت اکسل است که دو کار اصلی را انجام می دهد: 1. بارگذاری داده ها در اکسل تقریباً از هر منبع خارجی و 2. انواع تبدیل جداول بارگذاری شده. Power Query قبلاً در Excel 2016-2019 تعبیه شده است و برای Excel 2010-2013 به عنوان یک افزونه جداگانه نصب شده است (می توانید آن را به صورت رایگان از وب سایت رسمی مایکروسافت دانلود کنید). اگر هنوز استفاده از Power Query را در کار خود شروع نکرده اید، وقت آن است که در مورد آن فکر کنید، زیرا تغییراتی مانند آنچه در بالا توضیح داده شد، به راحتی و به طور طبیعی و تنها در چند حرکت انجام می شود.
ابتدا، بیایید لیست های منبع را به عنوان پرس و جوهای جداگانه در Power Query بارگذاری کنیم. برای این کار برای هر جدول مراحل زیر را انجام دهید:
- بیایید جداول را با یک دکمه به میزهای "هوشمند" تبدیل کنیم قالب بندی به صورت جدول برگ صفحه اصلی (صفحه اصلی - قالب بندی به عنوان جدول) یا میانبر صفحه کلید کلیدهای Ctrl+T. به هر جدول به طور خودکار یک نام داده می شود جدول 1,2,3، XNUMX، XNUMX …، اما در صورت تمایل می توان آن را در برگه تغییر داد سازنده (طرح).
- پس از تنظیم سلول فعال در جدول، دکمه را فشار دهید از روی میز (از جدول) برگ داده ها (تاریخ) یا روی برگه پرس و جو برق (اگر آن را به عنوان یک افزونه جداگانه برای Excel 2010-2013 نصب کرده اید).
- در پنجره ویرایشگر query که باز می شود، دستور را انتخاب کنید صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی — بستن و بارگذاری — بستن و بارگذاری به..) و سپس گزینه فقط یک اتصال ایجاد کنید (فقط اتصال ایجاد کنید). با این کار جدول بارگذاری شده در حافظه باقی می ماند و امکان دسترسی به آن در آینده فراهم می شود.
اگر همه چیز را به درستی انجام دهید، خروجی در پانل سمت راست باید سه درخواست در حالت باشد فقط اتصال با نام جدول ما:
حالا روی اولین کوئری کلیک راست کرده و دستور را انتخاب کنید ارتباط دادن (ارجاع)برای ایجاد یک کپی قابل به روز رسانی از آن و سپس اضافه کردن یک ستون اضافی به داده ها از طریق دستور اضافه کردن یک ستون ž – ستون سفارشی (افزودن ستون -ž ستون سفارشی). در پنجره ورودی فرمول، نام ستون جدید (به عنوان مثال، Fragment2) و یک عبارت بسیار ساده را به عنوان فرمول وارد کنید:
=جدول 2
به عبارت دیگر، نام پرس و جو دوم:
پس از کلیک روی OK ما یک ستون جدید خواهیم دید که در هر سلول آن یک جدول تودرتو با عبارات جدول دوم وجود دارد (اگر در پس زمینه سلول کنار کلمه کلیک کنید می توانید محتویات این جداول را مشاهده کنید. جدول):
باقی مانده است که تمام محتویات این جداول تودرتو را با استفاده از دکمه با فلش های دوتایی در هدر ستون حاصل گسترش دهید و علامت آن را بردارید. از نام ستون اصلی به عنوان پیشوند استفاده کنید (از نام ستون اصلی به عنوان پیشوند استفاده کنید):
... و ما تمام ترکیبات ممکن از عناصر را از دو مجموعه اول دریافت می کنیم:
علاوه بر این، همه چیز مشابه است. یک ستون محاسبه شده دیگر با فرمول اضافه کنید:
=جدول 3
...، و سپس جداول تودرتو را دوباره گسترش دهید - و اکنون همه گزینه های ممکن را برای جابجایی کلمات از سه مجموعه به ترتیب در اختیار داریم:
باقی مانده است که هر سه ستون را از چپ به راست انتخاب کنید، نگه دارید کلیدهای Ctrlو محتویات آنها را که با فاصله از هم جدا شده اند با استفاده از دستور الحاق کنید ادغام ستون ها (ادغام ستون ها) از زبانه دگرگونی (تبدیل):
نتایج به دست آمده را می توان با دستور از قبل آشنا بر روی برگه بارگیری کرد صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی — بستن و بارگذاری — بستن و بارگذاری به..):
اگر در آینده چیزی در جداول منبع ما با قطعات تغییر کند، فقط کافی است پرس و جو تولید شده را با کلیک راست بر روی جدول نتیجه و انتخاب دستور، به روز رسانی کنیم. به روز رسانی و ذخیره (تازه کردن) یا با فشار دادن میانبر صفحه کلید کلیدهای Ctrl+دگرساز+F5.
- Power Query، Power Pivot، Power Map و Power BI چیست و چرا به یک کاربر اکسل نیاز دارند
- ایجاد نمودار گانت در پاور کوئری
- 5 روش برای استفاده از تابع INDEX