چند مقدار در یک سلول Excel؛ آموزش Lists و Arrays با مثال‌های کاربردی

اگر چند سال است با Excel کار می‌کنید، احتمالاً این قانون نانوشته را آن‌قدر رعایت کرده‌اید که دیگر به آن فکر نمی‌کنید: هر سلول فقط یک مقدار. برای ثبت سه منطقه فروش یک بازاریاب، یا باید سه ستون می‌ساختیم، یا سه ردیف، یا هر سه را با ویرگول پشت سر هم در یک سلول تایپ می‌کردیم و بعد با TEXTSPLIT، SEARCH و Power Query به جانش می‌افتادیم تا دوباره از هم جدایشان کنیم.

Microsoft حالا قدم بزرگی برداشته است. در نسخه‌های Beta اکسل، یک سلول می‌تواند یک List واقعی یا یک Array کامل را در خود نگه دارد، به‌طوری که Excel هر عضو را یک مقدار مستقل بشناسد، نه تکه‌ای از یک متن طولانی. این یعنی می‌توانید بپرسید «آیا این مشتری سم خریده است؟» و Excel بدون ترفندهای متنی، دقیق و بدون خطای «تطبیق جزئی» جواب بدهد.

در این مقاله، قدم‌به‌قدم و با مثال‌هایی که به کار روزمره حسابداران، فروشندگان، مدیران پروژه و شرکت‌های بازرگانی نزدیک است، یاد می‌گیریم List چیست، چه تفاوتی با یک متن ساده یا یک Drop-down دارد، توابع جدید HAS، HASANY، HASALL و FLATTEN چطور کار می‌کنند، و چرا با وجود همه این جذابیت‌ها، این قابلیت جایگزین جدول نرمال‌شده، Power Query و Power BI نیست.

۱از «یک سلول، یک مقدار» تا «یک سلول، چند مقدار»

Excel از روز اول بر پایه یک مدل ساده ساخته شد: One Cell = One Value. هر سلول یک عدد، یک متن، یک تاریخ یا یک مقدار منطقی را نگه می‌داشت. این سادگی دلیل اصلی محبوبیت Excel بود، اما در دنیای واقعی خیلی از داده‌ها ذاتاً «چندتایی» هستند:

• یک فروشنده چند منطقه فروش دارد.
• یک مشتری چند گروه کالا خریداری می‌کند.
• یک پروژه چند عضو تیم و چند مرکز هزینه دارد.
• یک سفارش از چند قلم کالا تشکیل شده است.
• یک پروژه عمرانی چند مرحله با مدت‌زمان‌های متفاوت دارد.

تا امروز برای نگه‌داری این داده‌ها سه راه داشتیم و هر کدام دردسر خودش را داشت. راه اول ساختن ستون‌های «منطقه ۱»، «منطقه ۲»، «منطقه ۳» بود که با اضافه شدن منطقه چهارم، ساختار جدول را به هم می‌ریخت. راه دوم ثبت هر منطقه در یک ردیف جداگانه بود که از نظر تحلیلی درست است، اما برای نمایش خلاصه، جدول را طولانی و نامرتب می‌کند. راه سوم که از همه رایج‌تر است، تایپ همه مقادیر در یک سلول با ویرگول بود. ظاهرش مرتب بود، اما Excel آن را فقط یک رشته متنی می‌دید.

قابلیت جدید دقیقاً همین راه سوم را «هوشمند» می‌کند. شما همچنان همه مقادیر را در یک سلول می‌بینید، اما Excel این بار می‌داند که آن سلول شامل چند عضو مستقل است. به این ساختار سه نام مرتبط داده شده که در ادامه با هر سه آشنا می‌شویم:

۲وضعیت فعلی قابلیت و پیش‌نیازها

قبل از هیجان‌زده شدن، باید بدانیم این قابلیت در چه مرحله‌ای است. طبق اعلام رسمی Microsoft در وبلاگ Microsoft 365 Insider (مهر ۱۴۰۵ / سپتامبر ۲۰۲۶)، Lists، Arrays in Cells و Nested Arrays در حال حاضر فقط برای کاربران Beta Channel در برنامه Microsoft 365 Insiders منتشر شده‌اند.

• انتشار تدریجی: حتی اگر Build شما با شرایط بالا مطابقت دارد، ممکن است قابلیت هنوز برایتان فعال نشده باشد. Microsoft این قابلیت‌ها را مرحله‌ای منتشر می‌کند تا کیفیت و کارایی را زیر نظر داشته باشد.
• پلتفرم‌ها: اعلام رسمی فقط Windows و Mac را پوشش می‌دهد. درباره Excel برای وب، iOS و Android تاکنون اطلاعات رسمی منتشر نشده است.
• احتمال تغییر: چون قابلیت در مرحله Beta است، رفتار، نام توابع و آرگومان‌ها ممکن است تا انتشار عمومی بر اساس بازخورد کاربران تغییر کند.

Compatibility Version 3 چیست؟

Excel برای اینکه تغییر در موتور محاسبه، فایل‌های قدیمی را خراب نکند، از تنظیمی به نام Compatibility Version استفاده می‌کند. Microsoft اعلام کرده که بیشتر محاسبات Nested Array به Compatibility Version 3 نیاز دارند. این تنظیم از مسیر زیر در دسترس است:

Formulas › Calculation Options › Compatibility Version › 3

۳جداکننده List: مهم‌ترین نکته قبل از شروع

وقتی یک List می‌سازید، اعضا را با یک کاراکتر جداکننده از هم جدا می‌کنید. نکته‌ای که بسیاری از آموزش‌ها از آن می‌گذرند این است که این جداکننده برای همه کاربران یکسان نیست. Excel جداکننده List را از تنظیمات Regional ویندوز (یا تنظیمات زبان و منطقه در Mac) و تنظیمات خود Excel می‌گیرد. در سیستم‌هایی که ممیز اعشار نقطه است، معمولاً ویرگول انگلیسی (,) جداکننده است و در سیستم‌هایی که ممیز اعشار ویرگول است، معمولاً نقطه‌ویرگول.

چطور جداکننده سیستم خودم را پیدا کنم؟

ساده‌ترین روش این است که به یک فرمول معمولی در Excel خودتان نگاه کنید. همان کاراکتری که آرگومان‌های توابع را از هم جدا می‌کند، جداکننده List شما هم هست.

=IF(A1>10,100,0)

,

=IF(A1>10;100;0)

;

۴ساخت اولین List در یک سلول

بیایید با مثال فروش شروع کنیم. آقای احمدی، کارشناس فروش یک شرکت پخش، سه منطقه فروش دارد: تهران، کرج و قزوین. می‌خواهیم این سه منطقه را در یک سلول نگه داریم، اما به شکل List واقعی.

روش اول: میانبر Ctrl+J

1. در سلول B2 بنویسید: تهران,کرج,قزوین
2. Enter بزنید و دوباره سلول را انتخاب کنید.
3. کلید Ctrl + J را بزنید.

Excel متن را بر اساس جداکننده می‌شکند و هر بخش را به یک عضو مستقل تبدیل می‌کند. یک آیکون کوچک List در گوشه سلول ظاهر می‌شود که نشان می‌دهد این سلول دیگر یک متن ساده نیست. Ctrl + J یک کلید Toggle است. اگر دوباره آن را بزنید، List به متن ساده برمی‌گردد.

روش دوم: از Ribbon

سلول را انتخاب کنید و از تب Insert گزینه List را بزنید. سپس اعضا را تایپ یا Paste کنید.

ویرایش اعضای List

برای افزودن، حذف یا اصلاح اعضا، روی سلول دابل‌کلیک کنید یا F2 را بزنید. برای دیدن اعضا به‌صورت فهرست، روی آیکون List داخل سلول کلیک کنید.

ارجاع به یک List چه نتیجه‌ای می‌دهد؟

یکی از رفتارهای جالب این است که اگر در سلول دیگری فقط به List ارجاع دهید، مثلاً بنویسید =B2، اعضای List در سلول‌های زیرین Spill می‌شوند. یعنی List مثل یک آرایه رفتار می‌کند و می‌توانید روی اعضایش محاسبه انجام دهید.

=B2

۵آیا «تهران,کرج,قم» واقعاً یک List است یا فقط متن؟

این مهم‌ترین مفهومی است که باید در این مقاله جا بیفتد. دو سلول ممکن است در نگاه اول دقیقاً یکسان به نظر برسند، اما Excel آن‌ها را کاملاً متفاوت ببیند.

۶Lists in Cells با Drop-down List چه تفاوتی دارد؟

کلمه «List» در Excel سابقه طولانی دارد و همین باعث سردرگمی می‌شود. وقتی بیشتر کاربران ایرانی «لیست در اکسل» را می‌شنوند، یاد Data Validation › List می‌افتند، همان فهرست کشویی که کاربر یک گزینه از آن انتخاب می‌کند. این دو قابلیت هیچ ربطی به هم ندارند.

فروش

فروش,خرید,مالی

مثلاً در فرم ثبت پرسنل، ستون «واحد اصلی» با Drop-down پر می‌شود چون هر نفر فقط یک واحد اصلی دارد. اما ستون «واحدهایی که دسترسی دارد» می‌تواند یک List باشد، چون یک کارمند ممکن است به واحدهای فروش، خرید و مالی دسترسی داشته باشد.

۷توابع آشنا روی List: COUNTA، COUNT و SUM

خبر خوب این است که لازم نیست برای کار با Listها همه چیز را از نو یاد بگیرید. توابع آشنای Excel حالا اعضای داخل List را می‌بینند. یعنی وقتی به یک سلول List ارجاع می‌دهید، تابع با آن مثل یک آرایه از مقادیر رفتار می‌کند.

COUNTA: شمارش اعضای List

=COUNTA(value1, [value2], ...)

در یک فرم ثبت پروژه، ستون B اعضای تیم را به‌صورت List نگه می‌دارد. می‌خواهیم بدانیم هر پروژه چند نفر نیرو دارد:

=COUNTA(B2)

COUNT و SUM: کار با Listهای عددی

=COUNT(value1, [value2], ...)
=SUM(number1, [number2], ...)

فرض کنید مدت‌زمان مراحل پروژه A (بر حسب روز) در سلول B2 به‌صورت List ثبت شده است: 12,15,20.

=COUNT(B2)
=SUM(B2)

۸HAS، HASANY و HASALL: سه سؤال اصلی درباره یک List

وقتی داده‌ها در قالب List ذخیره شدند، پرتکرارترین سؤال این است: «آیا فلان مقدار در این List وجود دارد؟» Microsoft برای پاسخ به همین سؤال سه تابع جدید معرفی کرده است. قبل از رفتن سراغ جزئیات، این جدول را به خاطر بسپارید:

=HAS(array, value)
=HASANY(array, values)
=HASALL(array, values)

• array: List یا آرایه‌ای که می‌خواهید داخلش جستجو کنید. معمولاً ارجاع به یک سلول List مثل B2.
• value: یک مقدار که دنبالش هستید.
• values: مجموعه مقادیری که دنبالشان هستید، مثل {"سم","بذر"} یا محدوده F2:F3.

تابع HAS: آیا این مقدار در List هست؟

کاربرد: بررسی وجود یک عضو مشخص در List، با تطبیق کامل. HAS اعضای کامل را مقایسه می‌کند، نه بخشی از متن. یعنی «علی» با «علیرضا» یکی حساب نمی‌شود.

مثال ۱: منطقه فروش

فروشنده‌ای سه منطقه دارد: تهران,کرج,قزوین در سلول B2. آیا تهران جزء مناطق اوست؟

=HAS(B2,"تهران")

مثال ۳: عضویت در تیم پروژه

اعضای تیم پروژه در B2 ذخیره شده‌اند: علی,رضا,مریم. نام شخص موردنظر را در سلول E1 می‌نویسیم تا فرمول پویا باشد:

=HAS(B2,E1)

مثال ۵: مراکز هزینه در حسابداری پروژه

در حسابداری صنعتی و پروژه‌ای، یک پروژه ممکن است به چند مرکز هزینه مرتبط باشد. پروژه «ساختمان اداری ونک» در سلول C2 این مراکز هزینه را دارد: اداری,مصالح,نیروی انسانی. قبل از ثبت سند دستمزد، حسابدار می‌خواهد مطمئن شود که «نیروی انسانی» جزء مراکز هزینه مجاز این پروژه است:

=IF(HAS(C2,"نیروی انسانی"),"مجاز","خارج از مراکز هزینه پروژه")

تابع HASANY: آیا حداقل یکی از این مقادیر هست؟

کاربرد: وقتی چند گزینه دارید و وجود هر کدام کافی است. مثل یک OR روی اعضای List.

مثال ۲ (بخش اول): خریدار سموم یا بذر

شرکتی که نهاده‌های کشاورزی می‌فروشد، برای کمپین «فصل کاشت» می‌خواهد مشتریانی را پیدا کند که حداقل یکی از گروه‌های «سم» یا «بذر» را خریده‌اند. سلول B2 گروه‌های خریداری‌شده مشتری را دارد: کود,سم,بذر.

=HASANY(B2,{"سم","بذر"})

اگر نمی‌خواهید مقادیر را داخل فرمول بنویسید، آن‌ها را در یک محدوده کمکی (مثلاً F2:F3) قرار دهید. این روش برای کاربرانی که فرمول را ویرایش نمی‌کنند امن‌تر است:

=HASANY(B2,$F$2:$F$3)

تابع HASALL: آیا همه این مقادیر هستند؟

کاربرد: وقتی وجود تمام مقادیر شرط است. مثل یک AND روی اعضای List.

مثال ۲ (بخش دوم): خریدار هم‌زمان سم و کود

بخش فروش می‌خواهد به مشتریانی که هم سم و هم کود خریده‌اند، بسته مشاوره فنی رایگان پیشنهاد دهد:

=HASALL(B2,{"سم","کود"})

جمع‌بندی سه تابع با یک مثال واحد

برای مشتری «کشت و صنعت مهر» با List کود,سم,بذر:

=HAS(B2,"سم")                 → TRUE   آیا سم خریده؟
=HASALL(B2,{"سم","کود"})      → TRUE   آیا سم و کود هر دو را خریده؟
=HASANY(B2,{"سم","بذر"})      → TRUE   آیا حداقل یکی از سم یا بذر را خریده؟

۹Arrays in Cells: آرایه کامل در یک سلول

از زمان معرفی Dynamic Arrays، به رفتار Spill عادت کرده‌ایم. فرمول =SORT(A2:A10) نُه نتیجه برمی‌گرداند و آن‌ها را در نُه سلول زیرین می‌ریزد. این رفتار عالی است، اما دو مشکل همیشگی دارد:

• اگر یکی از سلول‌های مسیر Spill پر باشد، با خطای #SPILL! روبه‌رو می‌شویم.
• اگر بخواهیم در هر ردیف یک جدول، یک نتیجه چندتایی داشته باشیم، Spillها روی هم می‌افتند و عملاً غیرممکن است.

Arrays in Cells راه‌حل این مشکل است. کافی است فرمول را بین آکولاد قرار دهید: یک { درست بعد از علامت مساوی و یک } در انتهای فرمول. Excel به‌جای پخش کردن نتایج، همه آن‌ها را به‌صورت یک آرایه داخل همان یک سلول نگه می‌دارد.

={formula_that_returns_an_array}

=SORT(A2:A10)       → Spill در 9 سلول
={SORT(A2:A10)}     → همه 9 نام، مرتب‌شده، داخل یک سلول

یک کاربرد عملی: یک ردیف، یک نتیجه چندتایی

فرض کنید برای هر پروژه می‌خواهید اعضای تیم را به‌صورت مرتب‌شده در ستون کناری نشان دهید. با Spill معمولی، اگر فرمول را برای ردیف اول بنویسید، نتایج به پایین می‌ریزند و ردیف دوم را اشغال می‌کنند. اما با آرایه در سلول، هر ردیف نتیجه خودش را دارد:

={SORT(B2)}

آرایه‌های دوبعدی در یک سلول

آرایه داخل سلول محدود به یک ستون نیست. Microsoft اعلام کرده که آرایه‌ها می‌توانند هر اندازه و هر شکلی داشته باشند. مثلاً این فرمول یک جدول ۳ ردیفی و ۲ ستونی را داخل یک سلول نگه می‌دارد:

={SEQUENCE(3,2)}

۱۰مرتب‌سازی و حذف تکراری با SORT و UNIQUE

توابع SORT و UNIQUE مستقیماً روی اعضای List کار می‌کنند. انتخاب با شماست که نتیجه Spill شود یا داخل یک سلول بماند.

=SORT(array, [sort_index], [sort_order], [by_col])
=UNIQUE(array, [by_col], [exactly_once])

مثال ۱۰: پاک‌سازی List گروه‌های کالایی

اپراتور فروش هنگام ثبت سفارش‌های یک مشتری، گروه‌های کالایی را بدون دقت وارد کرده و List سلول B2 این شکلی شده است: کود,سم,کود,بذر,سم. می‌خواهیم موارد تکراری حذف و نتیجه مرتب شود:

={SORT(UNIQUE(B2))}

=SORT(UNIQUE(B2))

={SORT(UNIQUE(B2),,-1)}

=COUNTA(UNIQUE(B2))

۱۱برگرداندن List با XLOOKUP

XLOOKUP همچنان همان کار همیشگی را انجام می‌دهد: یک مقدار را پیدا می‌کند و مقدار متناظر را برمی‌گرداند. تفاوت این است که حالا «مقدار متناظر» می‌تواند یک List کامل باشد.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

مثال ۹: اعضای تیم بر اساس Project ID

جدول پروژه‌ها در محدوده A1:C4 قرار دارد. ستون A کد پروژه، ستون B نام پروژه و ستون C اعضای تیم به‌صورت List است. کد پروژه موردنظر را در E2 می‌نویسیم:

=XLOOKUP(E2,A2:A4,C2:C4)

={SORT(XLOOKUP(E2,A2:A4,C2:C4))}

=HAS(XLOOKUP(E2,A2:A4,C2:C4),"نرگس")

۱۲فیلتر کردن رکوردها بر اساس اعضای List

FILTER در کار با Listها دو نقش دارد: فیلتر کردن اعضای یک List و فیلتر کردن ردیف‌های یک جدول بر اساس محتوای Listها.

=FILTER(array, include, [if_empty])

حالت اول: فیلتر کردن اعضای داخل یک List

List مدت مراحل پروژه C در B4 است: 8,11,13,17. مراحلی که بیش از ۱۰ روز طول می‌کشند کدام‌اند؟

={FILTER(B4,B4>10)}

حالت دوم: فیلتر کردن مشتریان بر اساس List محصولات

می‌خواهیم فهرست مشتریانی را بگیریم که «سم» خریده‌اند. مطمئن‌ترین روش استفاده از یک ستون کمکی است:

1. در C2 بنویسید =HAS(B2,"سم") و تا پایین جدول کپی کنید.
2. سپس در هر جای دلخواه بنویسید:

=FILTER(A2:A5,C2:C5,"مشتری‌ای یافت نشد")

کاربران حرفه‌ای‌تر می‌توانند ستون کمکی را حذف کنند و منطق را با MAP و LAMBDA مستقیماً داخل فرمول بنویسند. MAP هر سلول از ستون B را جداگانه به HAS می‌دهد:

=FILTER(A2:A5,MAP(B2:B5,LAMBDA(x,HAS(x,"سم"))))

۱۳Nested Arrays: آرایه‌های تودرتو به زبان ساده

تا اینجا دیدیم که یک سلول می‌تواند یک List داشته باشد. حالا یک قدم جلوتر برویم: اگر یک ستون داشته باشیم که هر سلولش یک List باشد، کل آن ستون چیست؟ جواب: یک آرایه که اعضایش خودشان آرایه هستند. به این ساختار Nested Array یا آرایه تودرتو می‌گویند.

مثال ۸: پروژه‌هایی با تعداد مراحل متفاوت

یک شرکت پیمانکاری سه پروژه دارد و مدت‌زمان هر مرحله (روز) را ثبت کرده است:

مشکل ساختار سنتی

در جدول سنتی Excel، برای این داده‌ها یا باید به اندازه بیشترین تعداد مراحل ستون بسازیم (مرحله ۱ تا مرحله ۴) که نتیجه‌اش سلول‌های خالی زیاد است، یا هر مرحله را در یک ردیف جدا ثبت کنیم. اگر فردا پروژه‌ای با ۹ مرحله اضافه شود، در روش اول باید ۵ ستون جدید بسازیم و همه فرمول‌ها را اصلاح کنیم.

از طرف دیگر، تا قبل از این قابلیت، اگر فرمولی می‌نوشتیم که برای هر ردیف یک آرایه برمی‌گرداند (مثلاً با MAP یا BYROW)، Excel با خطای #CALC! متوقف می‌شد یا فقط بخشی از نتیجه را نشان می‌داد. Microsoft اعلام کرده که با Nested Arrays، فرمول‌های پشتیبانی‌شده حالا نتیجه کامل را برمی‌گردانند.

فرض کنید می‌خواهیم برای هر پروژه، مراحلی را که بیش از ۱۲ روز طول می‌کشند جدا کنیم. خروجی هر پروژه تعداد متفاوتی عضو دارد، یعنی یک آرایه از آرایه‌ها:

=MAP(B2:B4,LAMBDA(s,FILTER(s,s>12,"")))

در این ساختار دو سطح وجود دارد. سطح بیرونی: آرایه‌ای سه‌عضوی (یک عضو برای هر پروژه). سطح درونی: آرایه مراحل هر پروژه با طول ۱، ۲ یا ۳. به همین دلیل اسمش «تودرتو» است. Microsoft حتی نمونه آرایه‌های ثابت تودرتو را هم با آکولادهای چندلایه نشان داده است. یعنی آرایه‌ای که اعضایش داخل آکولادهای جداگانه قرار دارند.

۱۴FLATTEN: تبدیل Listها به داده ساده

FLATTEN شاید کاربردی‌ترین تابع جدید این مجموعه باشد. کارش ساده است: یک یا چند سطح از تودرتویی را حذف می‌کند تا هر عضو در سلول خودش قرار بگیرد. هر جا ابزاری از Excel هنوز با Listها کنار نمی‌آید (PivotTable، Chart، Data Validation)، FLATTEN پل ارتباطی شماست.

=FLATTEN(array, [pad_value], [levels])

مثال ۱۱: باز کردن مناطق فروشندگان

ستون B مناطق سه فروشنده را به‌صورت List نگه می‌دارد. می‌خواهیم همه مناطق را به‌صورت داده ساده، هر کدام در یک سلول، داشته باشیم:

=FLATTEN(B2:B4)

ترکیب طلایی: SORT + UNIQUE + FLATTEN

رایج‌ترین ترکیبی که با آن کار خواهید کرد این است: همه Listها را باز کن، تکراری‌ها را حذف کن و مرتب کن. نتیجه، فهرست کامل و یکتای همه مقادیری است که در ستون استفاده شده‌اند:

=SORT(UNIQUE(FLATTEN(B2:B20)))

={SORT(UNIQUE(FLATTEN(B2:B20)))}

۱۵شمارش و گروه‌بندی با GROUPBY

GROUPBY یکی از توابع جدیدتر Microsoft 365 است که یک خلاصه شبیه PivotTable را فقط با یک فرمول می‌سازد. در کنار FLATTEN، تبدیل به ابزار اصلی تحلیل Listها می‌شود.

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

مثال ۱۲: هر منطقه چند بار تکرار شده؟

به جدول فروشندگان یک نفر دیگر اضافه می‌کنیم تا مثال واقعی‌تر شود:

منطق کار ساده است: ابتدا Listها را با FLATTEN باز می‌کنیم، سپس همان ستون باز شده را هم به‌عنوان فیلد گروه‌بندی و هم به‌عنوان مقدار به GROUPBY می‌دهیم و تابع COUNTA را برای شمارش انتخاب می‌کنیم:

=GROUPBY(FLATTEN(B2:B5),FLATTEN(B2:B5),COUNTA,0,0,{-2,1})

• FLATTEN(B2:B5) اول: فیلد گروه‌بندی (نام مناطق).
• FLATTEN(B2:B5) دوم: مقادیری که شمرده می‌شوند.
• COUNTA: تابع تجمیع.
• 0 اول (field_headers): داده سرستون ندارد.
• 0 دوم (total_depth): ردیف جمع کل نمایش داده نشود.
• {-2,1} (sort_order): اول بر اساس ستون دوم (تعداد) نزولی، و در صورت برابری بر اساس ستون اول (نام منطقه) صعودی.

مسیر برعکس: ساختن List برای هر گروه

GROUPBY می‌تواند کار برعکس را هم انجام دهد. اگر داده شما در قالب نرمال‌شده (هر فروشنده و منطقه در یک ردیف) است و می‌خواهید برای گزارش مدیریتی، مناطق هر فروشنده را در یک سلول ببینید، تابع تجمیع را UNIQUE قرار دهید. نتیجه هر گروه یک List خواهد بود:

=GROUPBY(A2:A10,B2:B10,UNIQUE,0,0)

۱۶سناریوی کامل: مناطق تحت پوشش فروشندگان

حالا همه چیز را در یک سناریوی واقعی کنار هم می‌گذاریم (مثال ۴). مدیر فروش یک شرکت پخش مواد غذایی، جدول زیر را در یک Excel Table به نام Reps نگه می‌دارد و سه سؤال دارد:

سؤال ۱: چه مناطقی پوشش داده شده‌اند؟

=SORT(UNIQUE(FLATTEN(B2:B5)))

=COUNTA(UNIQUE(FLATTEN(B2:B5)))

سؤال ۲: چند فروشنده در تهران فعالیت دارند؟

روش اول (ستون کمکی، توصیه‌شده): در C2 بنویسید =HAS(B2,"تهران") و تا C5 کپی کنید. سپس:

=COUNTIF(C2:C5,TRUE)

روش دوم (یک فرمول): تعداد دفعاتی را بشمارید که «تهران» در خروجی FLATTEN آمده است:

=SUM(--(FLATTEN(B2:B5)="تهران"))

سؤال ۳: چه کسانی در تهران فعالیت دارند؟

={FILTER(A2:A5,C2:C5)}

با همین چند فرمول، مدیر فروش بدون Power Query و بدون ستون‌های اضافی برای «منطقه ۱، منطقه ۲، منطقه ۳»، یک داشبورد کوچک و پویا ساخته است. هر بار که منطقه‌ای به List یک فروشنده اضافه شود، همه نتایج خودکار به‌روز می‌شوند.

۱۷شرکت بازرگانی و سفارش مشتری: کی List، کی جدول؟

مثال ۶: گروه‌های کالایی مشتریان یک شرکت بازرگانی

یک شرکت بازرگانی که نهاده‌های کشاورزی وارد و توزیع می‌کند، در فایل CRM ساده خودش برای هر مشتری گروه‌های کالایی موردعلاقه را ثبت می‌کند. این داده «ویژگی» مشتری است، نه تراکنش، و جای ایده‌آلی برای List است:

چند تحلیل پرکاربرد روی این جدول:

=GROUPBY(FLATTEN(C2:C5),FLATTEN(C2:C5),COUNTA,0,0,{-2,1})

D2:  =COUNTA(C2)
     =FILTER(A2:A5,(D2:D5=1)*(MAP(C2:C5,LAMBDA(x,HAS(x,"کود")))))

=FILTER(A2:A5,E2:E5)

مثال ۷: سفارش چندقلمی: اینجا List انتخاب درستی نیست

حالا یک سفارش را در نظر بگیرید که سه قلم کالا دارد:

کود 15.5.30,سم حشره‌کش,بذر خیار

وسوسه‌انگیز است که همه اقلام سفارش را در یک List ذخیره کنیم. اما هر قلم سفارش اطلاعات خودش را دارد: مقدار، واحد، قیمت واحد، تخفیف، انبار تحویل و شماره بچ. اگر اقلام را در List نگه دارید، این اطلاعات کجا ثبت می‌شوند؟ مجبور می‌شوید چند List موازی بسازید (List مقادیر، List قیمت‌ها و...) و امیدوار باشید ترتیبشان هیچ‌وقت به هم نریزد. این دقیقاً همان جایی است که جدول نرمال‌شده برنده است:

برای همین سفارش، اگر در گزارش خلاصه سفارش‌ها می‌خواهید اقلام هر سفارش را در یک سلول ببینید، کافی است از جدول نرمال‌شده بالا (با نام OrderLines) یک List نمایشی بسازید:

={FILTER(OrderLines[کالا],OrderLines[شماره سفارش]=H2)}

۱۸آیا Lists in Cells جایگزین مدل داده استاندارد است؟

پاسخ کوتاه: خیر.

Lists in Cells یک ابزار عالی برای نمایش فشرده، ورود سریع داده‌های برچسبی و تحلیل‌های سبک داخل خود Excel است. اما اصول مدل‌سازی داده که سال‌ها در دیتابیس‌ها، Power Query، Power BI و PivotTable جواب داده‌اند، با آمدن این قابلیت عوض نشده‌اند. این ابزارها با ساختار نرمال‌شده بهترین عملکرد را دارند. یعنی هر ردیف یک واقعیت، و هر ستون یک ویژگی.

چرا مدل نرمال‌شده هنوز ضروری است؟

• PivotTable: طبق اعلام Microsoft، PivotTable فعلاً مقادیر داخل آرایه‌ها را نمی‌خواند. ستونی از Listها در Pivot قابل تحلیل عضوبه‌عضو نیست.
• Power Query: Power Query فعلاً ستون‌های آرایه‌ای را نه بارگذاری می‌کند و نه خروجی می‌دهد. اگر Workbook شما منبع Power Query یا Power BI است، ستون‌های List را به داده ساده تبدیل کنید.
• Power BI و مدل داده: روابط بین جداول، مدل ستاره‌ای (Star Schema) و اندازه‌های DAX بر پایه یک مقدار در هر ستون از هر ردیف طراحی شده‌اند. در Power Query هم ابزار Split Column › By Delimiter › Rows دقیقاً برای تبدیل متن‌های چندمقداری به ردیف‌های نرمال وجود دارد.
• یکپارچگی داده: در جدول نرمال‌شده می‌توانید برای هر محصول اطلاعات تکمیلی (مبلغ، تاریخ، مقدار) نگه دارید. در List فقط خود برچسب را دارید.
• سازگاری: جدول نرمال‌شده در هر نسخه Excel، Google Sheets، دیتابیس و نرم‌افزار حسابداری قابل استفاده است. List فعلاً فقط در Beta Channel کار می‌کند.

۱۹محدودیت‌های فعلی

Microsoft در اعلام رسمی خود فهرست مشخصی از محدودیت‌ها را منتشر کرده است. دانستن این محدودیت‌ها قبل از طراحی یک فایل جدی ضروری است:

۲۰توصیه‌های عملی برای استفاده حرفه‌ای

1. یکسان‌سازی کاراکترها: قبل از تبدیل متن به List، «ی» و «ک» عربی را به فارسی تبدیل کنید و فاصله‌های اضافه را حذف کنید. HAS دقیق مقایسه می‌کند و «تهران » (با فاصله) را با «تهران» یکی نمی‌داند.
2. از Excel Table استفاده کنید: ارجاع‌هایی مثل Reps[مناطق] هم خواناتر هستند و هم با اضافه شدن ردیف جدید، خودکار گسترش پیدا می‌کنند.
3. معیارها را در سلول بنویسید: به‌جای =HAS(B2,"تهران") از =HAS(B2,$F$1) استفاده کنید تا گزارش با تغییر یک سلول به‌روز شود.
4. ستون کمکی عیب نیست: یک ستون HAS ساده، برای همکاران شما بسیار قابل‌فهم‌تر از یک فرمول MAP و LAMBDA طولانی است.
5. برای Pivot و نمودار، اول FLATTEN: یک شیت کمکی بسازید که Listها را باز می‌کند و ابزارهای کلاسیک را به آن وصل کنید.
6. Listها را کوتاه و برچسبی نگه دارید: اگر یک List بیش از چند ده عضو دارد یا اعضایش اطلاعات وابسته دارند، احتمالاً به یک جدول جداگانه نیاز دارید.
7. نسخه‌بندی و مستندسازی: در یک شیت «راهنما» بنویسید که فایل از Lists و Compatibility Version 3 استفاده می‌کند تا کاربر بعدی غافلگیر نشود.
8. Beta را جدی بگیرید: روی فایل‌های عملیاتی و مالی، تا انتشار عمومی صبر کنید.

۲۱جدول جمع‌بندی

۲۲سؤالات متداول

۲۳منابع

1. منبع رسمی Microsoft: Microsoft 365 Insider Blog، «Put multiple values in one cell with lists and arrays in Excel»
   techcommunity.microsoft.com/blog/Microsoft365InsiderBlog/put-multiple-values-in-one-cell-with-lists-and-arrays-in-excel/4559395
2. منبع آموزشی XelPlus: «Excel Lists in Cells: Put Multiple Values in One Cell»
   xelplus.com/excel-lists-in-cells

این مقاله بر اساس اطلاعات منتشرشده تا مهر ۱۴۰۵ (اکتبر ۲۰۲۶) نوشته شده است. Lists، Arrays in Cells و Nested Arrays در مرحله Beta هستند و Microsoft ممکن است رفتار، نام توابع یا محدودیت‌ها را تا انتشار عمومی تغییر دهد. هر جا بین منابع تفاوتی وجود داشت، مستندات رسمی Microsoft ملاک قرار گرفته است. تمام مثال‌ها، داده‌ها و نام‌های این مقاله ساختگی و برای آموزش طراحی شده‌اند.