[لیست آموزش توابع]
دادههای افقی و قفل کردن VLOOKUP؟ ماجرا فراتر از یک ترفند است!
خیلی وقتها با فایلهایی روبهرو میشویم که دادهها بهجای ستونی، بهصورت افقی (ردیفی) ثبت شدهاند. اگر بخواهیم با VLOOKUP معمولی سراغشان برویم، اکسل تسلیم میشود، چون VLOOKUP ذاتاً عمودیگرد است!
شاید شنیده باشید که میشود با تابع TRANSPOSE دادهها را موقتاً ۹۰ درجه چرخاند و در دلِ VLOOKUP نشاند:=VLOOKUP(lookup_value, TRANSPOSE(range), col_index, ۰)
تابع TRANSPOSE چیست؟این تابع در واقع یک چرخدنده هوشمند اکسلی است که کل ساختار داده شما را ۹۰ درجه میچرخاند. یعنی سطرها را به ستون و ستونها را به سطر تبدیل میکند تا برای چشمِ VLOOKUP، دادهها از حالتِ افقیِ غیرقابلجستجو ، به حالتِ عمودیِ استاندارد تغییر شکل دهند.
اما یک متخصص واقعی اکسل، کار را الکی پیچیده نمیکند!ترکیب بالا یک ترفند جالب برای آشنایی با منطق اکسل است، ولی در کار واقعی و گزارشهای حرفهای، ۳ جایگزین استاندارد و بسیار بهینهتر داریم:
۱⃣ تابع HLOOKUP (متخصص دادههای افقی)حرف H مخفف Horizontal (*افقی*) است. این تابع دقیقاً برای همین چیدمان طراحی شده و نیازی به چرخاندن داده ندارد.=HLOOKUP(lookup_value, table_array, row_index_num, ۰)
۲⃣ تابع XLOOKUP (قدرتمند، منعطف و مدرن)اگر آفیس ۲۰۲۱ یا ۳۶۵ دارید، اصلاً نیازی به تفکیک عمودی یا افقی ندارید! XLOOKUP با هوشمندی کامل، دادههای افقی را در کسری از ثانیه جستجو میکند.
۳⃣ ترکیب INDEX + MATCH (آچار فرانسه اکسل)ترکیب کلاسیک و امن برای فایلهای سنگین، بدون وابستگی به جهت دادهها، موقعیت را دقیق پیدا میکند و هیچوقت با جابهجایی سطرها به خطا نمیخورد.
جمعبندی کاربردی:ترفند یاد بگیرید تا منطق اکسل را بفهمید، اما در کار واقعی از ابزار درست برای مسئله درست استفاده کنید تا فایلتان سبک، خوانا و سریع بماند.
فایل تمرینی مقایسهای:من یک فایل آماده کردهام که دقیقاً یک مسئله واحد را با هر سه روش (*HLOOKUP* ،*XLOOKUP و INDEX+MATCH*) حل کرده تا تفاوت سرعت و تمیزی فرمولها را خودتان لمس کنید.
برای دریافت این فایل، همین الان کلمه «*فایل*» را در قسمت دیدگاهها ارسال کنید.
اکسلدان شو | سکوی پرتاب شغلی و درآمدی
دادههای افقی و قفل کردن VLOOKUP؟ ماجرا فراتر از یک ترفند است!
خیلی وقتها با فایلهایی روبهرو میشویم که دادهها بهجای ستونی، بهصورت افقی (ردیفی) ثبت شدهاند. اگر بخواهیم با VLOOKUP معمولی سراغشان برویم، اکسل تسلیم میشود، چون VLOOKUP ذاتاً عمودیگرد است!
شاید شنیده باشید که میشود با تابع TRANSPOSE دادهها را موقتاً ۹۰ درجه چرخاند و در دلِ VLOOKUP نشاند:=VLOOKUP(lookup_value, TRANSPOSE(range), col_index, ۰)
تابع TRANSPOSE چیست؟این تابع در واقع یک چرخدنده هوشمند اکسلی است که کل ساختار داده شما را ۹۰ درجه میچرخاند. یعنی سطرها را به ستون و ستونها را به سطر تبدیل میکند تا برای چشمِ VLOOKUP، دادهها از حالتِ افقیِ غیرقابلجستجو ، به حالتِ عمودیِ استاندارد تغییر شکل دهند.
اما یک متخصص واقعی اکسل، کار را الکی پیچیده نمیکند!ترکیب بالا یک ترفند جالب برای آشنایی با منطق اکسل است، ولی در کار واقعی و گزارشهای حرفهای، ۳ جایگزین استاندارد و بسیار بهینهتر داریم:
۱⃣ تابع HLOOKUP (متخصص دادههای افقی)حرف H مخفف Horizontal (*افقی*) است. این تابع دقیقاً برای همین چیدمان طراحی شده و نیازی به چرخاندن داده ندارد.=HLOOKUP(lookup_value, table_array, row_index_num, ۰)
۲⃣ تابع XLOOKUP (قدرتمند، منعطف و مدرن)اگر آفیس ۲۰۲۱ یا ۳۶۵ دارید، اصلاً نیازی به تفکیک عمودی یا افقی ندارید! XLOOKUP با هوشمندی کامل، دادههای افقی را در کسری از ثانیه جستجو میکند.
۳⃣ ترکیب INDEX + MATCH (آچار فرانسه اکسل)ترکیب کلاسیک و امن برای فایلهای سنگین، بدون وابستگی به جهت دادهها، موقعیت را دقیق پیدا میکند و هیچوقت با جابهجایی سطرها به خطا نمیخورد.
جمعبندی کاربردی:ترفند یاد بگیرید تا منطق اکسل را بفهمید، اما در کار واقعی از ابزار درست برای مسئله درست استفاده کنید تا فایلتان سبک، خوانا و سریع بماند. فایل تمرینی مقایسهای:من یک فایل آماده کردهام که دقیقاً یک مسئله واحد را با هر سه روش (*HLOOKUP* ،*XLOOKUP و INDEX+MATCH*) حل کرده تا تفاوت سرعت و تمیزی فرمولها را خودتان لمس کنید.برای دریافت این فایل، همین الان کلمه «*فایل*» را در قسمت دیدگاهها ارسال کنید.
اکسلدان شو | سکوی پرتاب شغلی و درآمدی
۶