excel

اگر با برنامه پاورپوینت کار کرده‌باشید و برای مدیریت ارشد گزارش تهیه نموده باشید، حتما با واژگانی چون تاپ‌تِن(Top10) و یا نمودار پاراتو(Parato) و ... برخورد داشته‌اید، در حالت معمولی بدون استفاده از ساخت اتوماتیک گزارش(در ادامه مقاله هرکجا از گزارش نام برده‌شد منظور همان پرزنتیشن پاورپوینت می‌باشد) که در مقاله‌های سه‌گانه (1 و 2 و 3) توضیح‌داده شد برای تهیه نمودار تاپ‌تِن در اکسل اطلاعات مورد نیاز را از بیشترین به کمترین مرتب نموده و 10 اطلاعات نخست را برداشته به نمودار می‌افزاییم، اما اگر بخواهیم همین کار را به صورت خودکار انجام‌دهیم چگونه می‌توان این‌کار را انجام‌داد؟ حتی اگر فرض‌کنیم شما این توان را دارید که توسط یک ماکرو(VBA Macro) درون اکسل، عملیات مرتب سازی را انجام دهید، باز مشکلی حل نمی‌شود زیرا نیاز است این ماکرو توسط پاورپوینت اجراگردد. در ادامه خواهید دید با دو روش متفاوت این کار انجام خواهد شد یکی انجام مرتب‌سازی از طریق فرمول‌های اکسل و دیگری بکارگیری فیلتر اکسل!!!، خواهید دید چگونه می‌توان به ستون‌های یک شیت اکسل فیلتر افزود و از طریق آن، اقدام به مرتب‌سازی اطلاعات براساس نتایج دلخواه نمود و سپس ماکروهای درون اکسل را از درون پاورپوینت اجرا کرد.

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

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

اگر ورژن 2021 آفیس را نصب کرده باشید، جتما با توابع UNIQE و FILTER آشنا شده‌اید(اگر هم نشده‌اید، مهم نیست هم‌اکنون در حال آشنا شدن هستید)، این دو تابع قدرتمند و توانای آفیس بسیار کاربردی و مفید هستند، جلوگیری از تکرار داده، جمع‌آوری داده‌هایی که یک صفت مشترک دارند و ...، اما اگر این نسخه یا ورژن را نداشته باشید چه؟ محرومیت از این دو تابع در عمل کار را برایتان سخت می‌کند، شاید تا حدودی بتوانید با استفاده از توابع IF،  LOOKUP، INDEX و MATCH کار خود را راه اندازی کنید، اما تقریبا غیرممکن است مشابه UNIQE را با این توابع ساخت، در ادامه نخست به تعریف و کاربرد این توابع خواهم پرداخت و پس از آن روش‌های مختلف برای تولید این توابع در ورژن های پایین‌تر آفیس را خواهم گفت.

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

یکی از رایج‌ترین کارها در اکسل، جستجوی مقادیر در یک جدول بر اساس معیارهای خاص است. برای مثال، ممکن است بخواهید قیمت یک محصول، نام یک مشتری یا مقدار فروش برای یک ماه مشخص را پیدا کنید. برای این منظور در اکسل فرمول‌های فراوانی وجود دارد، از جمله توابع LOOKUP، HLOOKUP، VLOOKUP، INDEX و MATCH(همگی از اکسل 2003 به بعد ظاهر شدند) و ورژن‌ جدیدتر XLOOKUP (از 2021 به بعد ظاهر شد) بهترین توابع برای جستجو هستند، در گذشته(تا پیش از 2021)، کاربران اکسل برای این کار(جستجو) به تابع VLOOKUP تکیه می‌کردند. با این حال، VLOOKUP محدودیت‌های خاص خود را داشت(و دارد) که باعث شد علاقه‌مندان به اکسل راه‌حلی مقاوم‌تر با استفاده از ترکیب INDEX و MATCH ایجاد کنند(خود من همیشه از این ترکیب استفاده می‌کنم و به ندرت به سراغ VLOOKUP می‌روم). اکنون، با ظهور نسخه‌های جدیدتر، تابع XLOOKUP به عنوان جانشینی قدرتمندتر و همه‌کاره‌تر برای VLOOKUP معرفی شده است(برای استفاده از آن نیاز به نصب آفیس ورژن 2021و یا بالاتر دارید). در ادامه به ویژگی‌ها و ظرافت‌های این توابع بپردازم.

ابزارهای آفیس یکی از کاربردی‌ترین نرم‌افزارهای کار با کامپیوتر می‌باشند، ابزاری چون اکسل(Excel) که یک صفحه گسترده(Spread Sheet) بسیار کارا است که با استفاده از آن می‌توان اعداد را در جدول‌ها و یا در نمودارهای گوناگون نمایش داد، از قابلیت‌های خوب اکسل استفاده از فرمول در جدول‌ها می‌باشد. ابزار دیگر پاورپوینت می‌باشد، پاورپوینت نرم‌افزاری است که با کمک آن می‌توان متن، تصویر، نمودار، جدول‌، صدا، فیلم و ... را در اسلایدهای مختلف با بکارگیری افکت‌های دیداری گوناگون قرار داده و میان اسلایدها حرکت نمود. کاربرد اصلی آن، ارائه مقاله و مطالب مورد نظر در کنفرانس‌ها و سمینارها و یا در جلسات مدیریتی می‌باشد، چرا که سادگی و زیبایی و امکانات موجود در آن این امکان را به سخنران می‌دهد تا مفهوم مورد نظر خود را بهتر و قابل فهم‌تر به مخاطب ارائه نماید. بسیاری از شرکت‌ها در جلسه‌های مدیریتی خود از این نرم‌افزار برای ارائه مسائل مالی، نیروی انسانی و سایر موارد استفاده‌می‌کنند. اما برخی از شرکت‌ها(تقریبا همه شرکت‌های ایران) بجای بکارگیری این نرم‌افزار تنها برای مطالب مهم و جلسات مدیریتی، از آن به عنوان یک برنامه گزارش‌گیری استفاده می‌کنند. برای نمونه به صورت روزانه گزارش مبالغ پرداختی و دریافتی، میزان فروش و خرید، هزینه روزانه و ... را در پاورپوینت‌های جدا(فایل‌های جداگانه) تهیه‌می‌کنند و برای استفاده در اختیار دیگر همکارانشان قرارمی‌دهند. ساختن گزارشی آن‌هم با پاورپوینت(چرا که  پاورپوینت نرم‌افزار گزارش‌گیری نیست) و آن‌هم روزانه واقعا کاری سخت و طاقت‌فرسا می‌باشد. اما اگر دقت شود این گزارش‌ها(منظور گزارش‌های روزانه) دارای شکل و فرمت یکسان می‌باشند. بدین‌معنا که اسلایدها همگی تقریبا(یا بیشتر اسلایدها) مشابه هستند. لذا می‌توان با استفاده از برنامه‌نویسی VBA که مخفف Visual Basic for Application می‌باشد، تولید گزارش‌ روزانه را ساده‌تر کرد.

در این مقاله سعی بر آن است چند ترفند و راه‌کار برای کار با فایل‌های اکسل آموزش داده‌شود، شاید برخی را بدانید و شاید برای برخی دیگر راه بهتری سراغ داشته باشید(که اگر آن را برای سایت بفرستید تا با نام خودتان در سایت اضافه شود ممنون خواهم شد).

یک فایل اکسل دارید، شامل تعداد بسیار بسیار زیادی شیت. یافتن شیت مورد نظرتان در میان آن‌همه شیت مشکل‌ساز است. تعداد شیت‌ها بحدی زیاد است که با چشم بدنبال آن گشتن سخت است و بخصوص اسکرول کردن تب نام شیت‌ها کسالت‌بار، اگر امکانی باشد که لیست تمام شیت‌ها دردسترس باشد و با زدن کلیک روی آن‌ها به شیت موردنظر رفت بسیار عالی‌ست. روش‌هایمختلفی در این‌باره وجود دارد یکی استفاده از ماکرو(که نیاز است فایل اکسل از نوع Macro Enable باشد) و یا از دستورات و فرمول‌های درونی اکسل که ساده‌تر است.

خواندن داده برای یک برنامه‌نویس بخصوص اگر بخواهد برنامه‌هایی که برپایه دیتا(دیتابیس) هستند بنویسد بسیار مهم خواهدبود، بخش مهمی از این نوع برنامه‌ها عملیات‌هایی چون جستجو، درج, بروزرسانی‌ و حذف داده‌ها را شامل می‌شوند. این عملیات‌ها اگر با استفاده از اوراکل(Oracle)، اس‌کیوال(SQL) و یا برنامه‌هایی مشابه باشد تا حدود زیادی ساده و راحت است اما اگر داده‌ها در یک فایل متنی یا یک اکسل ذخیره شده‌باشد این‌گونه عملیات‌ها سخت‌تر و مشکل‌تر خواهدبود چرا که ساختار داده خوانده شده، یک ساختار جدولی نیست و امکاناتی چون جستجو، درج، بروزرسانی و حذف را باید توسط برنامه شبیه‌سازی کرد. در اینجا هدف فقط خواندن داده‌ها از یک شیت(Sheet) خاص از یک فایل اکسل است و نمایش آن‌ها درون یک کامبوباکس است، البته می‌خواهیم کامبوباکس‌مان عکس را نیز نشان دهد. در گذشته(منظور برنامه‌های فرم‌بیس) گذاشتن عکس در کامبوباکس مشکلات فراوانی داشت و نیاز به استفاده از APIها بود.

بسیاری از شرکت‌ها برای اطلاعات مورد نیازشان از اکسل و شیت‌های آن استفاده می‌کنند. اکسل قابلیت‌های بی‌شماری در نگهداری داده‌ها، آنالیز و ارزیابی آن‌ها دارد. از دستور‌های آماری چون sum و average و ... که با آن‌ها می‌توان اطلاعات آماری چون میانگین و انحراف‌معیار و از این قبیل بدست‌آورد و یا دستورهای lookup که امکان جستجوی داده‌ی خاصی در انبوهی از داده‌ها به ما می‌دهد و یا دستورهای مهندسی که این امکان را فراهم‌می‌کند تا تابع‌های مهندسی مانند تابع بسل و یا عددهای مختلط بر روی داده‌ سلول‌های اکسل بکارگرفت. دراینجا هدف آموزش اکسل نیست اما فرض کنید که در یک شرکت، اطلاعات فروش درون یک فایل اکسل ذخیره شده باشد، البته نه در یک شیت بلکه در چندین شیت مختلف. در گذشته نحوه اتصال به یک فایل اکسل و دریافت اطلاعات از یک شیت(Sheet) را آموزش داده‌ایم. اگر از دو یا چند شیت مجزا(تعداد شیت‌ها محدودباشد) بخواهیم اطلاعات را دریافت کنیم با استفاده از دستورLOAD به تعداد شیت‌ها می‌توان اطلاعات را خواند، اما نکته اینجاست که گاهی تعداد شیت‌ها بسیار زیاد می‌باشد و یا تعداد شیت‌ها متغیر بوده و امکان دارد با گذشت زمان کم و زیاد شود حال چه باید کرد و یا حتی از آن بدتر نام شیت‌ها متغیر باشند(چرا که دستور LOAD از نام شیت استفاده می‌کند). به‌راستی در این وضعیت چه‌باید کرد؟

با یک مثال مسئله بازتر خواهد شد: فرض کنید اطلاعات فروش برای هر بازاریاب در یک شیت جدا ذخیره‌شده باشد و مثلا اگر یک بازاریاب روزی مرخصی بود، هیچ شیتی برای آن بازاریاب در فایل اکسل وجود نخواهد داشت. پس در عمل اگر روزی 5 بازاریاب سرکارباشند ما 5 شیت مجزا داریم و اگر روزی 4 بازاریاب داشته‌باشیم(یکی مرخصی باشد) فقط 4 شیت خواهیم داشت.