Faragiri
فارسی

همهٔ زبان‌ها

آموزش اکسل نرم‌افزار اداری · فرمول‌نویسی اکسل، ماکرو و وی‌بی‌ای

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

۲شاخه
۲زیرمجموعه
۳عمق

یک جدول محوری چهار پله دارد و سه‌تا از خرابی‌های معمولش پله‌هایی است که زود برداشته شده‌اند. پله اول: منبع را تمیز کنید، یعنی یک سطر عنوان، بدون سطر خالی، بدون سلول ادغام‌شده؛ یک سطر خالی محدوده را همان‌جا قطع می‌کند. پله دوم: Insert و بعد PivotTable، که همان محدوده‌ای را پیشنهاد می‌دهد که می‌بیند، و برای همین پله قبلی تعیین می‌کند چه چیزی ظاهر شود.

پله سوم: فیلدها را داخل Rows و Values بکشید، یعنی چیزی که با آن گروه‌بندی می‌کنید در Rows و شمارش یا جمع در Values؛ Filters فقط اگر واقعا قرار است فیلتر کنید. پله چهارم: بعد از هر تغییر در منبع، Refresh. راست‌کلیک و Refresh، یا کلید ترکیبی که در نسخه فارسی هم همان است، و هیچ‌چیز به شما نمی‌گوید که جدول قدیمی شده.

هیچ پله‌ای برای قالب‌بندی اینجا نیست، و بیشترِ وقت واقعا آنجا می‌رود. قالب‌بندی از Refresh جان سالم به در می‌برد و عددها بدون Refresh به‌روز نمی‌شوند، که دقیقا وارونه جایی است که توجه معمولا خرج می‌شود.

چهار حلقه یک جست‌وجو، و آن یکی که بی‌صدا می‌شکند

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

یک جست‌وجو، چهار حلقه

چهار چیزی که همه باید درست باشند، و آن یکی که بی‌صدا می‌شکند

هیچ‌کدام از دیگری مهم‌تر نیست، و سه تایشان وقتی می‌شکنند خودشان را اعلام می‌کنند. حلقه باز این کار را نمی‌کند.

حلقه باز سخت‌ترین برای درست کردن نیست و با ISNUMBER در ده ثانیه معلوم می‌شود. باز کشیده شده چون تنها یکی از چهارتاست که شکستنش شبیه یک سلول خالی است نه شبیه یک خطا.

حلقه چهارم بی‌صدا می‌شکند: هر دو کلید باید از یک جنس باشند. کد ۰۰۴۱۲ به‌صورت متن، در برابر ۴۱۲ به‌صورت عدد. هیچ‌چیز گزارش نمی‌شود و جواب فقط غایب است. سخت‌ترین حلقه برای درست کردن نیست و با ISNUMBER در ده ثانیه معلوم می‌شود؛ باز کشیده شده چون تنها حلقه‌ای است که شکستنش شبیه یک سلول خالی است نه شبیه یک خطا.

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

ایست: نسخه‌ای که روی آن نشسته‌اید

اینجا جایی است که آموزش‌ها می‌ایستند. XLOOKUP در نسخه‌های ۳۶۵ و ۲۰۲۱ به بعد هست و در نسخه‌های قدیمی‌تر نیست، و روی رایانه‌های اداری اینجا نسخه‌های قدیمی‌تر هنوز رایج‌اند. آموزشی که با XLOOKUP شروع کند، برای کسی که ۲۰۱۶ دارد از سطر اول بی‌فایده است، و آن آدم فکر می‌کند خودش اشتباه می‌کند.

سازگاری فرمول

پیش از نوشتن فرمول جست‌وجو: فایل روی کدام نسخه باز می‌شود؟

آموزشی که با XLOOKUP شروع می‌شود روی رایانه با نسخه ۲۰۱۶ کار نمی‌کند. انتخاب تابع به نسخه میزهای دیگر دفتر بستگی دارد نه به سلیقه شما.

آیا همه کسانی که فایل را باز می‌کنند اکسل ۲۰۲۱ یا ۳۶۵ دارند؟
  • خیر، نسخه ۲۰۱۶ یا قدیمی‌تر هست
    ترکیب INDEX و MATCHروی هر نسخه‌ای از دهه گذشته کار می‌کند و هرگز کهنه نمی‌شود.
    • هر دو سمت چپ و راست را جست‌وجو می‌کند
    • اگر VLOOKUP می‌نویسید، کلید حتماً ستون چپ بماند
  • بله، همه روی ۲۰۲۱ یا ۳۶۵ هستند
    تابع XLOOKUPکوتاه‌تر است و جهت جست‌وجو را محدود نمی‌کند، اما روی نسخه قدیمی خطا می‌دهد.
    • جایگزین ساده برای INDEX و MATCH
    • پیش از ارسال فایل به همکار، نسخه مقصد را بپرسید

شکل برای یادآوری این است که خراب شدن XLOOKUP در اکسل ۲۰۱۶ خطای کاربر نیست، نبودن آن تابع در نسخه مقصد است.

جواب قابل حمل ترکیب INDEX و MATCH است که در هر نسخه‌ای از دهه گذشته کار می‌کند و هیچ‌وقت کهنه نمی‌شود. VLOOKUP هم هست و یک ضعف دارد که باید بدانید: فقط به راست نگاه می‌کند، پس ستون کلید باید سمت چپ باشد. اگر نسخه‌تان XLOOKUP دارد، از آن استفاده کنید و بدانید فایل روی رایانه همکارتان ممکن است بازش نکند.

با چه چیزی، و روی چه چیزی

Microsoft Excel تا ۲۰۲۶ پیش‌فرض دفترهای اینجاست و جای دفتر ستونی کاغذی را گرفت؛ نسخه رایگان رومیزی ندارد و نسخه‌ای که روی بیشتر رایانه‌های اینجا هست خریداری نشده. LibreOffice Calc رایگان است، فایل xlsx را باز می‌کند، و همان چهار پله جدول محوری را دارد با نام‌های کمی متفاوت؛ فرمول‌های INDEX و MATCH در آن یکی هستند و ماکروهای VBA نه.

چهار پله روی دو نرم‌افزار

چهار پله ثابت جدول محوری، چه در اکسل و چه در جایگزین رایگان

این چهار پله هم در Microsoft Excel تا ۲۰۲۶ و هم در LibreOffice Calc یکسان اجرا می‌شوند، ولی تکرار ماهانه‌اش جایی است که باید جایگزین شود.

  1. ۰۱منبع را تمیز کنیدیک سطر عنوان، بدون سطر خالی، بدون سلول ادغام‌شده. مدل هوش مصنوعی ۲۰۲۳ جنس متن یا عدد ستون را نمی‌بیند
  2. ۰۲Insert، بعد PivotTableدر اکسل و Calc با نام‌های کمی متفاوت، همان محدوده را می‌بیند و از پله قبل اثر می‌گیرد
  3. ۰۳فیلدها را در Rows و Values بکشیدگروه‌بندی در Rows، شمارش یا جمع در Values. فیلتر فقط اگر واقعاً فیلتر می‌کنید
  4. ۰۴بعد از هر تغییر منبع، Refreshراست‌کلیک و Refresh. اگر هر ماه از نو می‌سازید، وقت انتقال کار به Power Query رسیده

فرمول‌های INDEX و MATCH در Calc هم یکی هستند اما ماکروهای VBA نه. تفاوت واقعی در تکرار ماهانه است: یک بار نوشتن جریان در Power Query جای ساختن دوباره جدول در هر ماه را می‌گیرد.

چیزی که از ۲۰۲۳ تازه است، این است که مدل فرمول را از توصیف فارسی می‌نویسد و مهم‌تر از آن، فرمولی را که کار نمی‌کند توضیح می‌دهد. جای گشتن در انجمن‌ها برای یک خطا را می‌گیرد؛ نسخه رایگان برای این کافی است و دسترسی مستقیم به بیشترشان از ایران بسته است. کاری که نمی‌کند دیدن داده شماست: مدل نمی‌داند ستون کد شما متن است یا عدد، و همان یک چیز است که جست‌وجوی شما را خالی برمی‌گرداند.

صفحه‌هایی که هر پله را جدا باز می‌کنند

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

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

پرسشی که این چهار پله برنمی‌دارند این است که جدول محوری وقتی منبع هر ماه عوض می‌شود چه می‌شود. هر ماه دوباره ساختنش کار نیم‌ساعت است و هر ماه انجام می‌شود، تا وقتی کسی بفهمد همان نیم‌ساعت را می‌شد یک بار در Power Query نوشت و دیگر انجام نداد. تفاوت این دو در مهارت نیست، در این است که کسی جمع آن نیم‌ساعت‌ها را در یک سال حساب کرده باشد.

درون این، ۲

کنار این

بالای این