یک فایل اکسل زمانی از یک جدول ساده فراتر میرود که دادهها بین چند جدول و شیت پخش شوند و برای ارتباط دادن آنها به فرمول، شناسه، فایل خارجی یا ماکرو وابسته شوید. در این شرایط مشکل اصلی معمولاً فرمول خراب نیست؛ ساختار داده است.
اگر چند نفر با فایل کار میکنند یا اطلاعات مرتباً تغییر میکند، تشخیص این نشانهها مهمتر میشود. اکسل امکاناتی مانند Power Query و Data Model دارد که میتوانند بخشی از این مشکلات را بدون انتقال فوری اطلاعات به یک پایگاه داده جداگانه حل کنند.
کپی شدن یک فهرست در چند شیت؛ نشانهای از تکرار داده
فرض کنید فهرست محصولات یک بار در شیت Products و بار دیگر در Product_List قرار گرفته است. در ابتدا این کار ساده به نظر میرسد اما با تغییر قیمت یا نام محصول، دو نسخه بهتدریج از یکدیگر فاصله میگیرند.
به عنوان مثال ممکن است یک شیت USB-C Hub و دیگری USB C Hub داشته باشد یا قیمت یک محصول در یکی از آنها بهروز شود. در چنین شرایطی فرمولی مانند نمونه زیر ممکن است برای بعضی ردیفها #N/A برگرداند یا حتی بدتر از آن، قیمت قدیمی را بدون هیچ خطایی نمایش دهد.
=VLOOKUP(E2,Product_List!$B:$D,3,FALSE)
در اصطلاح پایگاه داده، این وضعیت به تکرار و نرمالسازی ضعیف دادهها مربوط است. بهتر است اطلاعات اصلی محصول فقط یک بار ذخیره شود و گزارشها و شیتهای مختلف به همان منبع متصل باشند.
در اکسل میتوان فهرست اصلی را به یک Table تبدیل کرد و برای وارد کردن و پاکسازی دادهها از Power Query استفاده کرد. Power Query میتواند داده را از منابع مختلف وارد کند و پس از تغییرات دوباره آن را Refresh کند.
شناسههایی که دستی وارد میشوند؛ مشکل کلید اصلی
اگر شناسه سفارش یا مشتری را کاربران به صورت دستی وارد میکنند، احتمال تکراری شدن یا تغییر شکل آن وجود دارد. مثلاً ORD-1014 ممکن است دوبار ثبت شود یا یک شناسه با فاصله و خط تیره متفاوت وارد شود.
در پایگاه داده، Primary Key باید هر رکورد را به صورت یکتا مشخص کند. در Data Model اکسل نیز ستون مورد استفاده برای سمت مرتبط باید مقادیر یکتا داشته باشد.
برای پیدا کردن شناسههای تکراری میتوان یک ستون کمکی ایجاد کرد:
=IF(COUNTIF($A:$A,A2)>1,"Duplicate","OK")
همچنین Conditional Formatting میتواند موارد تکراری را سریع مشخص کند. این روشها خطا را بعد از ورود داده پیدا میکنند و جلوی ثبت آن را به شکل یک پایگاه داده واقعی نمیگیرند.
اگر چند جدول دارید، استفاده از شناسههای یکتا و سپس ایجاد Relationship در Data Model ساختار منظمتری ایجاد میکند. اکسل میتواند روابط یکبهیک و یکبهچند را در Data Model مدیریت کند.
ستونهای Item 1 و Item 2 و Item 3؛ نشانه ساختار اشتباه جدول
اگر یک جدول ستونهایی مانند Item 1 و Qty 1 و Item 2 و Qty 2 داشته باشد، احتمالاً چند رکورد متفاوت را داخل یک ردیف فشرده کردهاید.
این ساختار در ابتدا برای فرمهای ساده کاربردی است اما با اضافه شدن کالاهای بیشتر، فرمولها نیز بزرگتر و نگهداری فایل سختتر میشود. برای مثال محاسبه تعداد فروش یک محصول باید چندین جفت ستون را بررسی کند:
=SUMIF(E:E,"Wireless Mouse",F:F)+SUMIF(G:G,"Wireless Mouse",H:H)+SUMIF(I:I,"Wireless Mouse",J:J)
راهکار مناسبتر این است که سفارش و اقلام سفارش در دو جدول جدا قرار بگیرند:
- جدول سفارشها: یک ردیف برای هر سفارش
- جدول اقلام سفارش: یک ردیف برای هر محصول داخل سفارش
- ارتباط دو جدول: از طریق Order ID
این ساختار به مفهوم First Normal Form نزدیکتر است و باعث میشود هر مقدار مستقل جای مشخصی داشته باشد. همچنین افزودن محصول جدید نیازی به ساخت ستونهای جدید ندارد.
البته Excel از نظر فنی میتواند تا ۱۶٬۳۸۴ ستون در هر Worksheet داشته باشد اما این محدودیت فنی به معنای مناسب بودن جدول بسیار عریض نیست؛ هزینه نگهداری و پیچیدگی فرمولها خیلی زودتر از رسیدن به این سقف افزایش پیدا میکند.
فرمولهایی که به فایل اکسل دیگری وابستهاند
وقتی فرمولی مستقیماً به یک Workbook دیگر اشاره میکند، آن فایل خارجی به بخشی از زنجیره وابستگی اطلاعات تبدیل میشود. نمونهای مانند زیر را در نظر بگیرید:
=VLOOKUP(E2,'[Supplier_Prices.xlsx]Costs'!$A:$B,2,FALSE)
اگر فایل منبع جابهجا یا تغییر نام داده شود، ممکن است ارجاع به آن با خطاهایی مانند #REF! مواجه شود. حتی اگر اکسل آخرین مقدار ذخیرهشده را نشان دهد، نباید فرض کرد که مقدار نمایشدادهشده الزاماً از آخرین نسخه منبع آمده است.
برای چنین سناریوهایی بهتر است به جای ساخت زنجیرهای از VLOOKUP بین فایلها، داده منبع را با Power Query وارد کنید.
مسیر دسترسی برای وارد کردن یک فایل اکسل با Power Query:
Data > Get Data > From File > From Excel Workbook
سپس فایل را انتخاب کنید و جدول موردنظر را در Navigator انتخاب کنید. میتوانید داده را مستقیماً Load کنید یا ابتدا با Transform Data آن را در Power Query Editor اصلاح کنید.
Power Query برای چنین کاری مزیت مهمی دارد: اطلاعات واردشده را میتوان در زمان نیاز Refresh کرد و تغییرات منبع را دوباره به ساختار داده منتقل کرد.
وقتی اکسل با ماکرو و فرم به یک نرمافزار تبدیل میشود
اگر فایل دیگر فقط محل ورود اطلاعات نیست و یک فرم، دکمه و چند ماکرو دارد که اطلاعات را در ردیفهای خاص ذخیره میکنند و گزارشها را بازسازی میکنند، در واقع بخشی از لایه برنامه را داخل همان Workbook ساختهاید.
ماکروها ذاتاً مشکل نیستند و برای خودکارسازی کارهای تکراری میتوانند بسیار مفید باشند. مشکل زمانی ایجاد میشود که قوانین مهم داده فقط داخل VBA قرار گرفته باشند. در این حالت خراب شدن نام یک Sheet یا تغییر ساختار جدول ممکن است باعث شود خطا به جای سلولها داخل کد VBA پنهان شود.
برای فایلهای کوچک میتوان با مستندسازی ماکروها و مشخص کردن شیتها و محدودههایی که هر ماکرو تغییر میدهد، نگهداری را سادهتر کرد. اما اگر اطلاعات مهم، کاربران متعدد، سطح دسترسی متفاوت یا ویرایش همزمان دارید، Excel دیگر جایگزین کامل یک سیستم پایگاه داده نیست.
راهکار عملی؛ چه زمانی از Power Query و Data Model استفاده کنیم؟
اگر مشکل اصلی شما تکرار جدولها و ارتباط چند مجموعه داده است، لازم نیست بلافاصله فایل را به یک سیستم پایگاه داده مستقل منتقل کنید. Excel خودش Data Model دارد که میتواند چند جدول را داخل یک مدل رابطهای قرار دهد.
مسیر ساخت و مدیریت Data Model:
Power Pivot > Manage
برای مشاهده ارتباط جدولها نیز میتوانید وارد Diagram View شوید و ستونهای کلیدی را به یکدیگر متصل کنید. در Data Model معمولاً یک طرف رابطه مقدار یکتا دارد و طرف دیگر میتواند چند مقدار مرتبط داشته باشد.
در عمل اگر فقط یک فهرست محصول دارید، بهتر است همان یک فهرست مرجع را نگه دارید. اگر داده از فایلهای مختلف میآید، Power Query برای وارد کردن و پاکسازی آن مناسب است و اگر چند جدول مرتبط برای گزارشگیری دارید، Data Model میتواند رابطه بین آنها را مدیریت کند.
نشانه مهم این است که هرچه تعداد VLOOKUPها بین فایلهای مختلف، جدولهای کپیشده، ستونهای تکراری و ماکروهای وابسته به ساختار فایل بیشتر شود، باید به جای اضافه کردن فرمول جدید، ساختار داده را بررسی کنید.
اینتوتک