آموزش تابع LEFTB در اکسل

معرفی تابع LEFTB

تابع LEFTB در اکسل برای جدا کردن تعداد مشخصی «بایت» از ابتدای یک متن استفاده می‌شود. این تابع شبیه تابع LEFT است، اما به جای شمارش تعداد کاراکترها، تعداد بایت‌ها را مبنا قرار می‌دهد.

کاربرد اصلی LEFTB بیشتر در زبان‌هایی است که از کاراکترهای دو بایتی استفاده می‌کنند؛ مثل ژاپنی، چینی و کره‌ای. در بسیاری از فایل‌های فارسی یا انگلیسی، مخصوصاً در نسخه‌ها و تنظیمات رایج اکسل، ممکن است LEFTB رفتاری بسیار شبیه LEFT داشته باشد. با این حال اگر با داده‌های چندزبانه، کدهای وارداتی، خروجی سیستم‌های قدیمی یا فایل‌هایی با ساختار بایتی کار می‌کنید، شناخت LEFTB می‌تواند بسیار مفید باشد.

مثلاً فرض کنید در سلول A2 کد کالا به صورت متنی ذخیره شده و می‌خواهید چند بایت اول آن را جدا کنید.

=LEFTB(A2, 3)

اگر مقدار سلول A2 برابر با ABC123 باشد، نتیجه این فرمول برابر ABC خواهد بود؛ چون سه بایت اول متن را برمی‌گرداند.

کاربردهای اصلی تابع LEFTB

• جدا کردن بخش ابتدایی متن بر اساس تعداد بایت، نه تعداد کاراکتر
• استخراج پیش‌شماره یا کد اولیه از متن‌های وارداتی
• کار با داده‌های چندزبانه، مخصوصاً زبان‌های شرق آسیا
• پاک‌سازی و استانداردسازی خروجی سیستم‌های قدیمی
• جدا کردن بخشی از شناسه‌ها، شماره‌ها، کدهای کالا یا کدهای مشتری
• استفاده در کنار توابع جستجو مانند XLOOKUP برای پیدا کردن اطلاعات بر اساس بخشی از کد
• ساخت شرط‌های متنی در کنار توابع IF، AND و OR
• شمارش یا جمع‌زدن داده‌ها بر اساس چند بایت ابتدایی یک مقدار متنی

ساختار (Syntax)

ساختار انگلیسی تابع LEFTB به شکل زیر است:

=LEFTB(text, [num_bytes])

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

=LEFTB(متن; [تعداد_بایت])

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

آرگومان‌ها

text / متن

این آرگومان متنی است که می‌خواهید چند بایت اول آن را جدا کنید. این مقدار می‌تواند مستقیماً در فرمول نوشته شود یا از یک سلول گرفته شود.

=LEFTB(A2, 4)

در این مثال، اکسل از مقدار موجود در سلول A2 شروع می‌کند و ۴ بایت ابتدایی آن را برمی‌گرداند.

num_bytes / تعداد_بایت

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

=LEFTB(A2)

در این حالت، فقط اولین بایت از متن سلول A2 برگردانده می‌شود.

مثال‌های ساده و پایه

مثال ۱: جدا کردن چند کاراکتر ابتدایی از کد کالا

فرض کنید در سلول A2 مقدار PRD-4589 قرار دارد و می‌خواهید سه بایت اول آن را جدا کنید.

=LEFTB(A2, 3)

نتیجه برابر PRD خواهد بود. این کار برای جدا کردن پیشوند کد کالا بسیار کاربردی است.

مثال ۲: استفاده مستقیم از متن داخل فرمول

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

=LEFTB("ExcelTraining", 5)

نتیجه برابر Excel خواهد بود.

مثال ۳: وقتی آرگومان تعداد بایت وارد نشود

اگر تعداد بایت را مشخص نکنید، اکسل فقط ۱ بایت ابتدایی متن را برمی‌گرداند.

=LEFTB("ABC123")

نتیجه برابر A خواهد بود.

مثال ۴: استخراج پیشوند از شماره فاکتور

فرض کنید شماره فاکتور در سلول A2 برابر با INV-1402-001 است. برای جدا کردن پیشوند INV از فرمول زیر استفاده می‌کنیم:

=LEFTB(A2, 3)

نتیجه برابر INV خواهد بود.

مثال‌های کاربردی و واقعی

مثال ۱: تشخیص نوع کالا با IF و LEFTB

فرض کنید کد کالا در سلول A2 قرار دارد. اگر سه بایت اول کد برابر PRD باشد، می‌خواهیم عبارت محصول نمایش داده شود؛ در غیر این صورت عبارت سایر نمایش داده شود.

=IF(LEFTB(A2, 3)="PRD", "محصول", "سایر")

این فرمول برای دسته‌بندی سریع کالاها بر اساس ابتدای کد بسیار مفید است.

مثال ۲: بررسی همزمان چند شرط با AND و LEFTB

فرض کنید در سلول A2 کد کالا و در سلول B2 وضعیت موجودی قرار دارد. اگر کد با PRD شروع شود و وضعیت موجودی برابر موجود باشد، نتیجه قابل فروش نمایش داده شود.

=IF(AND(LEFTB(A2, 3)="PRD", B2="موجود"), "قابل فروش", "غیرقابل فروش")

در این مثال، LEFTB بخش ابتدایی کد را بررسی می‌کند و AND مطمئن می‌شود هر دو شرط برقرار هستند.

مثال ۳: استفاده از OR برای چند نوع کد

فرض کنید کالاهایی که کد آن‌ها با PRD یا SRV شروع می‌شود، معتبر هستند.

=IF(OR(LEFTB(A2, 3)="PRD", LEFTB(A2, 3)="SRV"), "معتبر", "نامعتبر")

اگر سه بایت اول مقدار سلول A2 برابر PRD یا SRV باشد، نتیجه معتبر خواهد بود.

مثال ۴: شمارش کدهایی که با مقدار خاص شروع می‌شوند با COUNTIF

اگر لیست کدها در محدوده A2:A100 باشد و بخواهید تعداد کدهایی را بشمارید که با PRD شروع می‌شوند، می‌توانید از COUNTIF استفاده کنید.

=COUNTIF(A2:A100, "PRD*")

در این حالت COUNTIF به طور مستقیم کدهایی را می‌شمارد که با PRD شروع شده‌اند. اگر لازم باشد پیشوند را ابتدا با LEFTB در یک ستون کمکی استخراج کنید، می‌توانید در سلول B2 بنویسید:

=LEFTB(A2, 3)

سپس تعداد PRDها را در ستون کمکی بشمارید:

=COUNTIF(B2:B100, "PRD")

مثال ۵: جمع فروش بر اساس پیشوند کد با SUM و IF

فرض کنید کد کالاها در محدوده A2:A10 و مبلغ فروش در محدوده B2:B10 قرار دارد. می‌خواهید فروش کالاهایی را جمع بزنید که کد آن‌ها با PRD شروع می‌شود.

=SUM(IF(LEFTB(A2:A10, 3)="PRD", B2:B10, 0))

در نسخه‌های جدید اکسل مثل Microsoft 365، این فرمول به صورت آرایه‌ای اجرا می‌شود. در نسخه‌های قدیمی‌تر ممکن است لازم باشد آن را به صورت فرمول آرایه‌ای وارد کنید.

مثال ۶: جستجو با XLOOKUP بر اساس پیشوند استخراج‌شده

فرض کنید در سلول A2 کد کامل کالا قرار دارد. در محدوده E2:E10 پیشوند کدها و در محدوده F2:F10 نام گروه کالا قرار دارد. برای پیدا کردن گروه کالا بر اساس سه بایت اول کد، می‌توانید از فرمول زیر استفاده کنید:

=XLOOKUP(LEFTB(A2, 3), E2:E10, F2:F10, "یافت نشد")

در این مثال، LEFTB ابتدا پیشوند کد را جدا می‌کند و XLOOKUP بر اساس آن، گروه کالا را پیدا می‌کند.

ترکیب تابع LEFTB با فرمول‌های دیگر

• ترکیب با IF برای دسته‌بندی متن بر اساس ابتدای کد

=IF(LEFTB(A2, 2)="IR", "ایران", "سایر")

• ترکیب با AND برای بررسی چند شرط همزمان

=IF(AND(LEFTB(A2, 3)="VIP", C2>1000000), "مشتری ویژه", "عادی")

• ترکیب با OR برای پذیرش چند پیشوند مختلف

=IF(OR(LEFTB(A2, 2)="A1", LEFTB(A2, 2)="B1"), "مجاز", "غیرمجاز")

• ترکیب با COUNTIF برای شمارش پیشوندهای استخراج‌شده در ستون کمکی

=COUNTIF(B2:B100, "A1")

• ترکیب با XLOOKUP برای یافتن اطلاعات از جدول مرجع

=XLOOKUP(LEFTB(A2, 4), H2:H20, I2:I20, "بدون نتیجه")

• ترکیب با LENB برای بررسی طول بایتی متن

=IF(LENB(A2)>10, LEFTB(A2, 10), A2)

• ترکیب با VALUE برای تبدیل بخش عددی ابتدای متن به عدد

=VALUE(LEFTB(A2, 4))

• ترکیب با TRIM برای حذف فاصله‌های اضافی قبل از استخراج

=LEFTB(TRIM(A2), 5)

خطاهای رایج و روش رفع آنها

۱. خطای #VALUE! به دلیل منفی بودن تعداد بایت

اگر آرگومان تعداد بایت را عدد منفی وارد کنید، اکسل خطای #VALUE! نمایش می‌دهد.

=LEFTB(A2, -3)

روش رفع: همیشه مطمئن شوید تعداد بایت عددی صفر یا مثبت است.

=LEFTB(A2, 3)

۲. نتیجه غیرمنتظره در متن‌های فارسی یا چندزبانه

تابع LEFTB بر اساس بایت کار می‌کند، نه صرفاً تعداد کاراکتر. بنابراین در برخی زبان‌ها یا تنظیمات، خروجی آن ممکن است با انتظار شما متفاوت باشد.

روش رفع: اگر فقط می‌خواهید تعداد مشخصی کاراکتر از ابتدای متن جدا کنید، بهتر است از تابع LEFT استفاده کنید.

=LEFT(A2, 3)

۳. استفاده اشتباه از جداکننده آرگومان‌ها

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

=LEFTB(A2, 3)

=LEFTB(A2; 3)

روش رفع: با توجه به تنظیمات منطقه‌ای اکسل خود، جداکننده درست را انتخاب کنید.

۴. عدد ذخیره‌شده به صورت عددی، نه متنی

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

روش رفع: اگر خروجی باید عدد باشد، از VALUE استفاده کنید.

=VALUE(LEFTB(A2, 3))

۵. وجود فاصله اضافی در ابتدای متن

اگر متن دارای فاصله اضافی در ابتدا باشد، LEFTB همان فاصله‌ها را هم جزو بایت‌های ابتدایی حساب می‌کند.

روش رفع: قبل از LEFTB از TRIM استفاده کنید.

=LEFTB(TRIM(A2), 3)

۶. استفاده از LEFTB به جای LEFT در داده‌های معمولی

در بسیاری از فایل‌های فارسی و انگلیسی، هدف کاربر جدا کردن تعداد مشخصی کاراکتر است، نه بایت. در این حالت استفاده از LEFTB ممکن است ضروری نباشد.

روش رفع: اگر هدف شما شمارش کاراکتر است، از LEFT استفاده کنید.

=LEFT(A2, 3)

نکات حرفه‌ای و ترفندهای مهم

• اگر با داده‌های معمولی فارسی یا انگلیسی کار می‌کنید، اول بررسی کنید که واقعاً به LEFTB نیاز دارید یا LEFT کافی است.
• LEFTB بیشتر برای سناریوهای مرتبط با بایت، داده‌های چندزبانه و سازگاری با سیستم‌های قدیمی کاربرد دارد.
• برای تشخیص طول بایتی متن، می‌توانید LEFTB را همراه با LENB استفاده کنید.
• اگر خروجی LEFTB قرار است در محاسبات عددی استفاده شود، آن را با VALUE به عدد تبدیل کنید.
• برای جلوگیری از خطاهای ناشی از فاصله‌های اضافی، قبل از استخراج متن از TRIM استفاده کنید.
• اگر ساختار کدهای شما ثابت است، می‌توانید پیشوندها را با LEFTB در یک ستون کمکی استخراج کنید و سپس از COUNTIF، SUMIF یا XLOOKUP استفاده کنید.
• برای خوانایی بهتر فایل، از ستون‌های کمکی با عنوان‌هایی مثل پیشوند کد، گروه کالا یا نوع مشتری استفاده کنید.
• در فرمول‌های پیچیده، تعداد بایت موردنظر را در یک سلول جداگانه قرار دهید تا تغییر آن ساده‌تر شود.
• در نسخه‌های جدید Excel مانند Microsoft 365، می‌توانید LEFTB را در فرمول‌های آرایه‌ای و ترکیبی راحت‌تر استفاده کنید.
• اگر هدف شما استخراج داده بر اساس جداکننده‌هایی مثل خط تیره، فاصله یا اسلش است، توابعی مثل TEXTBEFORE یا LEFT همراه با FIND ممکن است مناسب‌تر باشند.

تفاوت تابع LEFTB با توابع مشابه

تفاوت LEFTB با LEFT

تابع LEFT تعداد مشخصی کاراکتر از ابتدای متن را جدا می‌کند، اما LEFTB تعداد مشخصی بایت را از ابتدای متن برمی‌گرداند.

=LEFT(A2, 3)

=LEFTB(A2, 3)

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

تفاوت LEFTB با RIGHTB

LEFTB از ابتدای متن بایت‌ها را جدا می‌کند، اما RIGHTB از انتهای متن این کار را انجام می‌دهد.

=RIGHTB(A2, 3)

اگر بخواهید پسوند کد، شماره انتهایی یا چند بایت آخر متن را جدا کنید، RIGHTB مناسب‌تر است.

تفاوت LEFTB با MIDB

LEFTB همیشه از ابتدای متن شروع می‌کند، اما MIDB می‌تواند از موقعیت دلخواه در متن، تعداد مشخصی بایت را جدا کند.

=MIDB(A2, 4, 3)

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

تفاوت LEFTB با LENB

LEFTB بخشی از متن را برمی‌گرداند، اما LENB طول بایتی کل متن را محاسبه می‌کند.

=LENB(A2)

اگر می‌خواهید بدانید متن از نظر بایتی چه طولی دارد، LENB مناسب است. اگر می‌خواهید بخشی از متن را جدا کنید، LEFTB مناسب است.

تفاوت LEFTB با TEXTBEFORE

TEXTBEFORE متن را قبل از یک جداکننده مشخص برمی‌گرداند، نه بر اساس تعداد بایت. برای مثال اگر کد شما با خط تیره بخش‌بندی شده باشد، TEXTBEFORE ممکن است ساده‌تر و دقیق‌تر باشد.

=TEXTBEFORE(A2, "-")

اما اگر باید دقیقاً بر اساس تعداد بایت استخراج انجام دهید، LEFTB انتخاب مناسب‌تری است.

سازگاری با نسخه‌های مختلف اکسل

تابع LEFTB در نسخه‌های مختلف اکسل برای پشتیبانی از زبان‌های دارای مجموعه کاراکتر دو بایتی ارائه شده است. این تابع معمولاً در نسخه‌های دسکتاپ Excel و بسیاری از نسخه‌های جدیدتر در دسترس است.

• Excel 2007 و نسخه‌های بعدی معمولاً از LEFTB پشتیبانی می‌کنند.
• Excel 2010، 2013، 2016، 2019 و 2021 از این تابع پشتیبانی می‌کنند.
• Microsoft 365 نیز از LEFTB پشتیبانی می‌کند.
• در Excel for Web ممکن است رفتار تابع به تنظیمات زبان و سازگاری فایل وابسته باشد.
• رفتار LEFTB در زبان‌های مختلف ممکن است متفاوت به نظر برسد؛ مخصوصاً اگر داده‌ها شامل کاراکترهای دو بایتی باشند.

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

سؤالات پرتکرار درباره تابع LEFTB

تابع LEFTB دقیقاً چه کاری انجام می‌دهد؟

این تابع تعداد مشخصی بایت از ابتدای یک متن را جدا می‌کند و به عنوان خروجی برمی‌گرداند.

آیا LEFTB با LEFT فرق دارد؟

بله. LEFT بر اساس تعداد کاراکتر کار می‌کند، اما LEFTB بر اساس تعداد بایت. در متن‌های ساده ممکن است خروجی آن‌ها یکسان باشد.

آیا برای متن فارسی باید از LEFTB استفاده کنم؟

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

اگر تعداد بایت را وارد نکنم چه می‌شود؟

اکسل به طور پیش‌فرض ۱ بایت اول متن را برمی‌گرداند.

=LEFTB(A2)

آیا خروجی LEFTB عدد است یا متن؟

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

=VALUE(LEFTB(A2, 3))

چرا فرمول LEFTB من خطای #VALUE! می‌دهد؟

یکی از رایج‌ترین دلایل، وارد کردن عدد منفی برای تعداد بایت است. تعداد بایت باید صفر یا مثبت باشد.

آیا می‌توان LEFTB را با XLOOKUP ترکیب کرد؟

بله. یکی از کاربردهای خوب LEFTB این است که ابتدا پیشوند کد را جدا کنید و سپس با XLOOKUP اطلاعات مرتبط را پیدا کنید.

=XLOOKUP(LEFTB(A2, 3), E2:E10, F2:F10, "یافت نشد")

جمع‌بندی و پیشنهاد یادگیری بعدی

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

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

برای ادامه یادگیری، پیشنهاد می‌شود توابع LEFT، RIGHT، MID، LEN، LENB، FIND، TEXTBEFORE و XLOOKUP را نیز تمرین کنید. این توابع در کنار هم به شما کمک می‌کنند متن‌ها، کدها و شناسه‌ها را در اکسل به شکل حرفه‌ای مدیریت کنید.