آموزش Data Validation در اکسل : هنگام ثبت اطلاعات مالی در اکسل، ممکن است یک هزینه با چند نام مختلف وارد شود؛ مثلاً «حمل‌ونقل»، «هزینه حمل و نقل» و «حمل و نقل». این تفاوت‌های ظاهری باعث می‌شوند گزارش‌گیری دشوارتر شود و جمع‌بندی هزینه‌ها همیشه نتیجه دقیقی نداشته باشد.

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

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

Data Validation در اکسل چیست؟

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

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

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

مثال: ساخت جدول مالی در اکسل

فرض کنید می‌خواهید درآمدها و هزینه‌های روزانه یک کسب‌وکار را ثبت کنید. ساختار جدول می‌تواند به این صورت باشد:

در این نمونه، ستون‌های «نوع»، «سرفصل» و «روش پرداخت» گزینه‌های استاندارد دارند. با ساخت لیست کشویی برای این ستون‌ها، احتمال تفاوت در شیوه ثبت اطلاعات کاهش می‌یابد.

نکته: تاریخ‌های نمونه صرفاً برای نمایش ساختار جدول هستند. اگر تاریخ شمسی را به‌صورت متن ثبت می‌کنید، برای مرتب‌سازی و محاسبات تاریخ باید روش ذخیره‌سازی تاریخ را جداگانه بررسی کنید.

آموزش ساخت لیست کشویی در اکسل

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

مرحله اول: آماده‌کردن گزینه‌های لیست

برای نمونه، در یک بخش جداگانه از شیت یا در شیتی با نام «تنظیمات»، گزینه‌های روش پرداخت را وارد کنید:

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

مرحله دوم: انتخاب سلول‌های مقصد

فرض کنید ستون «روش پرداخت» در محدوده E2:E100 قرار دارد. این محدوده را انتخاب کنید تا لیست کشویی برای تمام سلول‌های آن ساخته شود.

مرحله سوم: بازکردن Data Validation

از نوار بالای اکسل وارد مسیر زیر شوید:

Data → Data Validation

در پنجره بازشده، تب Settings را انتخاب کنید.

مرحله چهارم: تنظیم فهرست کشویی

در بخش Allow گزینه List را انتخاب کنید. سپس در کادر Source محدوده گزینه‌ها را وارد کنید:

=$J$2:$J$6

گزینه In-cell dropdown را فعال نگه دارید تا فلش انتخاب فهرست در سلول نمایش داده شود. سپس روی OK کلیک کنید.

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

روش جایگزین: واردکردن گزینه‌ها به‌صورت مستقیم

اگر فهرست شما کوتاه است، می‌توانید گزینه‌ها را مستقیماً در کادر Source وارد کنید؛ برای نمونه:

نقدی,کارت بانکی,کارت‌خوان,انتقال بانکی,چک

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

ساخت لیست کشویی برای نوع تراکنش

برای اینکه هر تراکنش مشخص کند درآمد است یا هزینه، یک لیست کشویی در ستون «نوع» ایجاد کنید.

1. در بخشی از شیت، دو گزینه «درآمد» و «هزینه» را بنویسید.
2. محدوده ستون نوع، مثلاً C2:C100، را انتخاب کنید.
3. از مسیر Data → Data Validation وارد تنظیمات شوید.
4. در بخش Allow گزینه List را انتخاب کنید.
5. محدوده گزینه‌ها را در کادر Source وارد کنید.
6. روی OK کلیک کنید.

با این کار، کاربر برای هر ردیف یکی از دو گزینه را انتخاب می‌کند و نیازی به تایپ دوباره آن‌ها ندارد.

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

ساخت لیست کشویی برای سرفصل‌های حسابداری

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

• فروش
• خرید کالا
• اجاره
• حقوق و دستمزد
• حمل‌ونقل
• ملزومات
• قبوض و خدمات

ابتدا این گزینه‌ها را در محدوده‌ای مانند L2:L8 وارد کنید. سپس محدوده سرفصل‌های جدول، مثلاً D2:D100، را انتخاب کنید و در بخش Source بنویسید:

=$L$2:$L$8

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

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

ممکن است بعداً بخواهید گزینه جدیدی مانند «پرداخت آنلاین» را به فهرست روش پرداخت اضافه کنید.

اگر Source به محدوده J2:J6 اشاره می‌کند، گزینه جدید را در J7 وارد کنید و محدوده منبع را به شکل زیر تغییر دهید:

=$J$2:$J$7

برای تغییر منبع:

1. سلول‌های دارای لیست کشویی را انتخاب کنید.
2. وارد Data → Data Validation شوید.
3. محدوده جدید را در کادر Source وارد کنید.
4. تنظیمات را تأیید کنید.

اگر قرار است مرتب گزینه‌های جدیدی اضافه کنید، بهتر است فهرست منبع را به یک جدول اکسل (Table) تبدیل کنید یا از یک محدوده نام‌گذاری‌شده و قابل توسعه استفاده کنید. این روش نگهداری فایل را ساده‌تر می‌کند.

جلوگیری از ورود اطلاعات نامعتبر

Data Validation فقط برای نمایش لیست کشویی نیست؛ می‌توانید از آن برای کنترل داده‌های واردشده نیز استفاده کنید.

محدودکردن مبلغ به اعداد مثبت

فرض کنید مبلغ تراکنش در ستون F ثبت می‌شود و نباید عدد منفی وارد شود.

1. محدوده F2:F100 را انتخاب کنید.
2. وارد Data → Data Validation شوید.
3. در بخش Allow گزینه Decimal یا Whole number را انتخاب کنید.
4. در بخش Data گزینه greater than or equal to را انتخاب کنید.
5. مقدار 0 را وارد کنید.

اکنون اکسل می‌تواند ورود مقادیر کمتر از صفر را رد کند. اگر می‌خواهید مبلغ حتماً بیشتر از صفر باشد، شرط greater than و مقدار صفر را انتخاب کنید.

نمایش پیام راهنما هنگام انتخاب سلول

در پنجره Data Validation، تب Input Message را باز کنید. می‌توانید عنوان و پیامی مانند این وارد کنید:

• عنوان: «روش پرداخت»
• پیام: «لطفاً یکی از گزینه‌های فهرست را انتخاب کنید.»

این پیام هنگام انتخاب سلول به کاربر کمک می‌کند داده را به شکل مورد انتظار وارد کند.

نمایش هشدار هنگام ورود داده نامعتبر

در تب Error Alert می‌توانید پیامی برای ورودی نامعتبر تنظیم کنید. برای مثال:

«لطفاً یک گزینه معتبر از فهرست انتخاب کنید.»

اگر می‌خواهید ورود مقدار نامعتبر متوقف شود، سبک هشدار Stop را انتخاب کنید. توجه داشته باشید که برخی روش‌های کپی و جای‌گذاری ممکن است محدودیت‌های اعتبارسنجی را دور بزنند؛ بنابراین Data Validation جایگزین بررسی و کنترل اطلاعات مالی نیست.

استفاده از لیست کشویی در کنار فرمول‌های مالی

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

محاسبه مجموع درآمدها

اگر نوع تراکنش در ستون C و مبلغ در ستون F باشد، فرمول زیر مجموع ردیف‌هایی را محاسبه می‌کند که نوع آن‌ها «درآمد» است:

=SUMIF(C2:C100,"درآمد",F2:F100)

محاسبه مجموع هزینه‌ها

=SUMIF(C2:C100,"هزینه",F2:F100)

شمارش تعداد تراکنش‌های یک روش پرداخت

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

=COUNTIF(E2:E100,"کارت‌خوان")

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

نکات حرفه‌ای برای طراحی جدول مالی

۱. گزینه‌های تکراری و مشابه نسازید

برای مثال، «کارت بانکی» و «پرداخت با کارت» ممکن است به یک مفهوم اشاره کنند. پیش از ساخت لیست، نام‌گذاری گزینه‌ها را استاندارد کنید.

۲. گزینه‌ها را در شیت جداگانه نگه دارید

ساخت شیتی با نام «تنظیمات» برای فهرست روش‌های پرداخت، نوع تراکنش و سرفصل‌ها باعث می‌شود تغییرات آسان‌تر انجام شوند.

۳. داده‌ها را به Table تبدیل کنید

محدوده جدول مالی را انتخاب کنید و از مسیر Insert → Table آن را به جدول اکسل تبدیل کنید. این کار مدیریت داده‌ها و گسترش محدوده را ساده‌تر می‌کند؛ با این حال، پس از اضافه‌شدن ردیف‌های جدید، تنظیمات Data Validation را بررسی کنید تا مطمئن شوید برای ردیف‌های تازه نیز اعمال شده‌اند.

۴. ورود اطلاعات را از محاسبات جدا کنید

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

۵. از فایل پشتیبان استفاده کنید

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

خطاهای رایج در Data Validation

چرا فلش لیست کشویی نمایش داده نمی‌شود؟

سلول را انتخاب کنید و به Data → Data Validation بروید. مطمئن شوید در بخش Allow گزینه List انتخاب شده و گزینه In-cell dropdown فعال است.

چرا گزینه‌های لیست کشویی ناقص هستند؟

محدوده Source را بررسی کنید. ممکن است فهرست فقط تا ردیف مشخصی را پوشش دهد؛ برای مثال J2:J6 درحالی‌که گزینه‌های جدید در ردیف‌های بعدی قرار دارند.

چرا فهرست با اضافه‌کردن گزینه جدید به‌روز نمی‌شود؟

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

آیا می‌توان لیست کشویی را روی چندین سلول اعمال کرد؟

بله. می‌توانید پیش از بازکردن Data Validation چندین سلول یا یک محدوده کامل را انتخاب کنید و تنظیمات یکسانی را برای آن‌ها اعمال کنید.

پرسش‌های متداول

Data Validation در اکسل چه کاربردی دارد؟

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

چگونه در اکسل لیست کشویی بسازم؟

محدوده مقصد را انتخاب کنید، به Data → Data Validation بروید، گزینه List را انتخاب کنید و منبع گزینه‌ها را در کادر Source وارد کنید.

آیا می‌توان گزینه‌های لیست کشویی را در شیت دیگری قرار داد؟

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

آیا لیست کشویی جلوی تمام اشتباهات را می‌گیرد؟

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

جمع‌بندی

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

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