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

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

قبل همه صحبت هایم از اینکه فرصت نشد توضیحاتی را در مورد پروژه های فایل خدمت شما تقدیم کنم پوزش می طلبم و می دانم بهتر بود شرحی هر چند مختصر، در مورد پروژه های فایل می دادم و این بخش را در اختیار خودتان قرار می دهم و اگر به سوالی برخورد کردید در ایمیل davoud.kh@gmail.com برای بنده ارسال بفرمائید تا با افتخار، در کنار شما باشم.

دانلود فایل

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

نکته: این تابع فقط برای محاسبه مالیات برحقوق سال 1401 کاربرد دارد.

استفاده از این تابع توسط Add-Ins بخش Options اکسل انجام می پذیرد و افزودن آن به اکسل بسیار راحت است و آموزش کامل روش افزودن آن را در این ویدئو ببنید

فایل تابع را از اینجـــــا دانلود کنید.

در مینی پروژه ساده با حلقه Do While - Loop یک کار کنترلی را یاد خواهیم گرفت، مثلا اگر در حلقه، عدد تولید شده از عدد 15 کوچکتر و یا مساوی باشد بیاد یک مقداری را برگرداند و در Debug عدد را Print کند.این عددی که گفتم در نتیجه چرخش حلقه بوجود میاد و تابع While شرط را بررسی می کند و در صورت برقراری شرط حلقه را متوقف می کند.

در این پروژه کوچک، متغییری به نام n داریم و در ابتدا به آن مقدار 0 را می دهیم:

n=0

در خط بعدی کد بررسی شرط را می نویسیم که بیاد عددهای تولید شده را که به متغیر n می دهد را بررسی کند:

Do While n <= 15

و اگر شرط بالا برقرار نباشد حلقه به کار خودش ادامه می دهد و در ادامه حلقه درستور ذیل اجرا می شود و آن هم افزودن عدد 3 به مقدار قبلی متغیر n است و هر بار که حقله تکرار می شود اگر شرط برقرار نباشد عدد 3 به مقدار قبلی n اضافه می شود:

n = n + 3

و این Loop است که موجب چرخش حلقه می شود و در ادامه، با اتمام حلقه، آخرین مقداری که به n داده می شود را در Debug چاپ کرده که در صفحه Immediate Window نمایش داده می شود

و در این پروژه عددی که حاصل محاسبه دستور نوشته شده است مقدار 18 است و با اجرای آن در یک فایل اکسل براحتی به جواب دست پیدا خواهید کرد.

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

در این پروژه که می خواهم راجع به آن صحبت کنم جدولی کوچک که دارای یک ستون نام کالا و سه ستون حاوی عدد را در فایل اکسل ایجاد کرده ام و هدفی را که دنبال می کنم این است که هر سلولی از سه ستون دارای عدد را با ماوس انتخاب می کنم جمع اعداد ستون سلول انتخاب شده را در سلولی تعریف شده(در این پروژه سلول G2 است) نمایش دهد.

در کدنویسی این مینی پروژه از ویژگی های Column و End و Row و Range و ActiveCell و Cells و سایر ویژگی ها استفاده شده و ترکیبی از توابع و ویژگی ها دستوری نوشته شده که وظیفه آن این است که کاربر سلولی را که در ستون موردنظر انتخاب می کند دستور بیاد محدوده بین اولین سلول ستون از جایی که عدد وارد شده تا آخرین ردیف ان ستون را استخراج کند و به متغیری به نام Sumrng تحویل دهد و این آدرس استخراج شده(که دستور به صورت پویا استخراج می کند) یه تابع SUM داده می شود تا اعداد محدوده تحویلی را مجع بزند و در داخل سلول G2 درج کند. این دستور در Procedure شیت مورد نظر در Worksheet_SelectionChange صدا زده و با انتخاب سلول با ماوس یا با کلیدهای جهت دار کیبرد دستور به راحتی اجرا و نتجه حاصل می شود.

این یک روش ساده و حرفه ای از روش کدنویسی VBA در اکسل است و روش ها و فرآیندهای پیچیده تر و خلاقانه تر این هم می توانیم متناسب با پروژه هایمان کدنویسی کنیم.

نمونه فایل پروژه را از اینجـــــــا دانلود کنید

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

در یک مینی پروژه که الان راجع به آن می خواهم توضیح دهم نمونه ای از مباحث فوق را به شکل ساده و که عمل شمارش عدد از 1 تا 10 و همچنین شمارش تعداد اجرا را اجرا می کند را ارائه دهم تا روش کدنویسی در vba را با برخی توابع و پراپرتی های پرمصرف vba برای پیاده سای فرآیند آشنا شوید و در فایل های مهم و محاسبات کنترلی بتوانید استفاده کنید.

در این پروژه، تابع OnTime از پراپرتی های Application بوده و از لحظه( Now ) تا یک مقدای از زمان(ساعت) که با تابع TimeValue به Now اضافه و به متغیر داده می شود در حافظه ذخیره و در ساعت دوم دستور حاوی پروسجیری که در تابع OnTime در آرگومنت procedure نامش ذکر می شود را اجرا می کند و این دستور مدام در حال چرخه اجرای مجدد در میاید.

حال با این شرایط دستور مدام اجرا شده و برای متوقف کردن اجرای دستور، در این پروژه از تابع IF استفاده شده و سلولی که عدد حاصل از شمارش مورد هدف قرار داده و اگر عدد به 10 برسد دستور عدم اجرای مجدد را که در آرگومت schedule تابع OnTime با ذکر گزینه False صادر و اجرای تمام دستورات داخل پروسیجر(نام پروسیجر در ایم پروژه Active_Rob_Proc است) متوقف می شود.

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

فایل مینی پروژه را از اینجــــــا دانلود کنید.

در جدول اعداد برای یافتن ، جداسازی و یا حذف برخی اعداد سلول ها در اکسل براساس مضرب اعداد موردنظر، با فرمول نویسی و کدنویسی در بخش VBA مي توانيم انجام دهیم و براحتی و با سرعت داده های بسیار زیادی را در معرض محاسبه دقیق قرار داده و نتیجه ایده آل را بدست بیاوریم.

در این مینی پروژه، سعی بر آن شده در یک ردیف 8 ستونی و یک ستون در 8 ردیفی اعداد 1 تا 8 را نوشته و با دو حلقه تو در تو For-Next محاسبه ضرب اعداد انجام می شود و سپس با تابع شرطی و مهم IF شرط مضرب اعداد ارزیابی و اگر عدد سلول مضربی از عدد موردنظر بود دستور Cells(i, j).Interior.Color رنگ سلول را تغییر می دهد(برای تغییر رنگ سلول از تابع RGB استفاده شده است).

نکته ای که در این مینی پروژه وجود دارد رنگ تک تک سلول ها قبل از محاسبه شرط، اگر سلول رنگ زمینه داشت توسط دستور Cells(i, j).Interior.Pattern = xlNone حذف شده و اگر مشمول شرط شد رنگش تغییر می کند.

فایل پروژه را از اینجـــــــــا دانلود کنید

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

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

تابعی به نام KH_Sum_CellColor که در این وبلاگ قرار داده ام و این تابع می تواند در فرمول نویسی استفاده شود و عملکردی مشابه همین پروژه را انجام می دهد.

فایل پروژه را از اینجا دانلود کنید.

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

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

روش استفاده از فایل ارائه شده به این صورت است که فایل را در مسیر مشخصی در سیستم قرار دهید و سپس یک فایل جدید و یا ذخیره شده(مهم نیست کدام اجرا شود) را اجرا کنید. از بخش File اکسل ، Options را باز کنید. از پنجره باز شده، از گزینه های سمت چپ گزینه Add-Ins را انتخاب کنید. در پایین پنجره، بخش Manage گزینه Excel Add-Ins که به صورت پیش فرض انتخاب شده، برروی کلید Go بزنید تا پنجره Add-Ins باز شود.

از پنجره باز شده کلید Browse را زده و فایل مورد بحث را انتخاب کنید. نام فایل در لیست Add-Ins available قرار می گیرد. نام فایل به منظور فعال بوده بایستی تیک دار باشد.

ماژول فایل با این روش در آفیس سیستم ایجاد می‌شود. برای دسترسی به توابع اضافه شده، از تب Formulas بخش Insert Function را انتخاب می کنیم و سپس از پنجره باز شده لیست بازشونده Or select a category گزینه User Defined را انتخاب کرده و از لیست توابع(Select a function) تابع مورد نظر را انتخاب می کنید.

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

توابع ساخته شده و اضافه به اکسل(Add-Ins)

مشخصه توابع با پیشوند KH در لیست توابع به راحتی قابل شناسایی است

1- KH_Ext_Numeric : جداسازی عدد از متن در سلول

2- KH_Adad_posv : جمع اعداد مثبت در محدوده (جایگزین فرمول آرایه ای)

3- KH_Sum_CellColor : جمع سلول های رنگی(یکسان) در محدوده

4-KH_Count_Fac: شمارش تعداد گزینه‌های تکراری در محدوده(ستون)

5- KH_FileSz: نمايش حجم فایل فعال

توضیحات - در آرگومان Show_MSG اگر مقدار یک را وارد کنید در صورت عدم شناسایی محل ذخیره فایل(در صورت ذخیره نبودن فابل) پیام بصورت Msgbox اعلام می شود و اگر مقدار صفر وارد کنیم پیام Msgbox اعلام نمی شود و فقط پیام در سلول درج می شود.

6- KH_TimeAlarm : تابع هشدار و یادآوری(بصورت متن در سلول و پیام Msgbox)

توضیحات: دارای چهار آرگومان است که آرگومان اول ساعت، آرگومان دوم دقیقه، آرگومان سوم انتخاب سلول(متن یادآوری) و آرگومان چهارم تعیین وضعیت پیام Msgbox(عدد0 عدم نمایش Msgbx و عدد 1 نمایش Msgbox)

7-KH_A_Printer: تابع اعلام چاپگر فعال در سلول(بدون آرگومان)

8-KH_2VLOOKUP: تابعی جستجوگر از جنس VLOOUP که تابع اصلی قابلیت جستجوی یک پارامتر را دارد ولی در تابع جدید(KH_2VLOOKUP) تعداد دو پارامتر را برای جستجو دریافت می کند. این تابع دارای شش آرگومان است و اگر پارامترهای مشابه در دو ستون را پیدا کند برعکس VLOOKUP آخرین مقدار یافت شده را بر می گرداند.

آرگومان ششم، اختیاری است و مقادیر 0 و 1 را دریافت می کند. در این آگومان، مقدار1، نتیجه جزئیات جستجو به همراه مقدار یافت شده را اعلام می کند در غیر این صورت، مقدار جستجو را بر می گرداند.

تفاوت اساسی تابع جدید با تابع VLOOKUP در آرگومان دوم (Table_arrya) است که محدودتی در نحوه جستجو فراهم می کرد. این مشکل در تابع KH_2VLOOKUP حل شده و مورد جستجو محدود به جدول(محدوده) و اینکه در جدول پیش از ستون مقدار یافت شده باشد نیست و در آرگومان های اول و سوم(Range_1 , Range_2 ) در تابع جدید، فقط آدرس ستون را وارد می کنید(با شماره ردیف یکسان).

9-KH_WordsCntSelection: این تابع آدرس یک سلول یا بیش از یک سلول را در آرگومنت Range دریافت می کند و تعداد کلمات موجود در محدوده را می شمارد.

10-KH_char_Count: این تابع، آدرس یک سلول را در آرگومنت cell دریافت کرده (محتوای درون سلول رشته یا فرمول باشد مهم نیست) و محتوای آرگومنت chr که نوع آن رشته تعریف شده است (کاراکتر در داخل دو " " قرار دهید) را در داخل رشته آرگومنت اول بررسی و تعداد آن را بر می گرداند.

ویدئوی آموزش تابع KH_2VLOOKUP

______________________________________________________________________

دانلود فایل

توضیحات در ویدئو

در این بخش، نمونه فرمول نویسی در  Conditional Formating را برای علاقه مندان اکسل قصد دارم ارائه دهم. این فرمول، از توابع FIND،INDIRECT،ADDRESS،ROW،MATCH ترکیب و درست شده و در قسمت Conditional Formating تعریف و اعمال می شود. موضوع این مبحث، جستوی داده(یا قسمتی از کلمه موردنظر)در سه ستون با سه عنوان مختلف بوده و با انتخاب پارامتر جستجو(عنوان)، فرمول تشخیص می دهد در کدام ستون آدرس داده شده پیدا کند و پس از یافتن، فرمت ردیف یافت شده را با توجه به تنظیمات داده شده تغییر می دهد.(تصویر زیر)

فرمول چگونه محاسبه و نتیجه را در محدوده تعریف شده اعمال می کند:

در مرحله اول، تابع ROW شماره ردیف را شناسایی می کند و مقدار به دست آمده را در آرگومان شماره ردیف(Row_num) تابع ADDRESS  قرار می دهد.

در مرحله بعدی، تابع MATCH، عنوان ستون را از سلول F4 دریافت می کندو پس از بررسی و جستجو در محدوده  A7:G7(به صورت مطلق) شماره ستون را اعلام می کند و مقدار به دست آمده را در آرگومان مربوط به شماره ستون(Column_num) تابع ADDRESS قرار می دهد. و در آرگومان سوم تابع (Abs_num)، عدد 4 را وارد می کنید تا آدرس سلولی به دست آمده از تایع ، به صورت نسبی درآید.

در مرحله بعد، آدرس به دست آمده برای تابع FIND بی معنی و نامفهوم است در اینجا تابع INDIRECT نقش خود را ایفا می کند و آدرس سلولی به دست آمده از تابع ADDRESS را دریافت و  برای تابع FIND قابل فهم و محتوای آدرس سلولی(داده) را در اختیار تابع FIND قرار می دهد.

در مرحله آخر، تابع FIND مورد جستجو را از سلول G4(بصورت مطلق) دریافت کرده (آرگومان اول) و آن را در آدرس به دست آمده از تابع INDIRECT جستجو می کند و در صورت یافتن، شماره کاراکتر آن را در داده سلول نتیجه می دهد.

___________________________________________________________________________

دانلود فایل

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

علاوه به آن، جمع تعداد عدد در لیست را هم اعلام میکند که این در آخرین سلول ستون به صورت فرمول درج می شود. در این کد، از حلقه For_Next استفاده شده و با شناسایی سلول های دارای عدد، مقدار را کپی کرده و در مقصد تعریف شده درج می کند.

در ذیل نمونه فایل را برای مطالعه، می توانید دانلود کنید.

دانلود فایل

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

این نرم افزار، با VBA و فرمول نویسی پیشرفته و هوشمند اکسل طراحی شده و برای راه اندازی و تحویل به مشتری، نیاز به استقرار دارد و قابلیت های ارتقاء براساس نیاز کاری مشتریان بدون محدودیت و همچنین در تولید گزارش های داینامیک برای مدیران از شاخصه های این نرم افزار است.

امکانات فعلی نرم افزار انبار:

1. دارای پایگاه داده مختلف 
2. دارای درخواست کد ریجستری و فعالسازی نرم افزار
3. اجرای برنامه با نام کاربری و رمز
4. صدور حواله با فرم حواله 
5. حذف و اصلاح سند
6. جستجوی کالا براساس پارامتر های تعریف شده و نمایش جمع گردش و مانده کالا برای انبارهای مختلف
7. خروجی سند حواله به صورت چاپ و PDF
8. تعریف کالا و تعریف شاخصه های کالا
9. گروه بندی کالا در دو پارامتر اصلی و فرعی
10. گزارش کل و کاردکس کالا 
11. گزارش تجمیعی گردش و مانده کالا برای انبارهای مختلف
12. گزارش تجمیعی گردش و مانده کالا برای انبارهای مختلف براساس شاخصه های تعریف شده و کنترل تاریخ انقضاء 
13. گزارش جزئیات حواله های صادر شده براساس پارامترهای انبار و قابلیت چاپ حواله با انتخاب شماره حواله
14. امکان تنظیم سیستمی عرض ستون ها به اندازه دلخواه
15. گزارش کاهش موجودی کالا برای انبارهای مختلف(بضورت خودکار) و قابلیت چاپ گزارش
16. دارای امکان تعریف نام شرکت،تعریف نام سال مالی، تعریف کاربر، تغییر رمز کاربر فعال، تنظیمات نرم افزاری ، امکان تشخیص و تنظیم بزرگنمایی تمامی صفحات
17. تهیه پشتیان از نرم افزار 
18. دارای تنظیمات عمومی نرم افزار
19. تهیه گزارش آزاد(ارسال اطلاعات گزارش به فایل اکسل)
20. امکان یادآوری موضوع در طول روز
21. نمایش ساعت داینامیک (شناور در تمامی صفحات)
22. امکان یادداشت و امکان مدیریت نمایش آن
23. نمایش ماشین حساب سیستم عامل
24. نمایش ورود و خروج آخرین کاربر و مدت زمان حضور
25. دارای آلارم هشدار کاهش موجودی در انبارهای مختلف
26. دارای آلام هشدار نزدیک شدن تاریخ انقضاء به همراه تنظیم مدت محاسبه
27. نمایش و عدم نمایش امکانات جانبی نرم افزار
28. ثبت نام کاربر در صدور اسناد و مدیریت کنترل فعالیت کاربر
29. قابلیت ارتقاء نرم افزار براساس تعدد کاربر و گزارش سازی از داده ها
30. نیاز به استقرار در سیستم در محل و تحویل با تنظیمات راه اندازی

__________________________________________________________________________

کد محصول: H-012

نام محصول: نرم افزار مدیریت انبار

قیمت محصول: 7،500،000 ریال (هفتصد و پنجاه هزار تومان)

                                 * نصب، راه اندازی، آموزش و تحویل نرم افزار*                            

__________________________________________________________________________

خرید محصول

درخواست نسخه نمایشی نرم افزار از طریق ایمیل(Davoud.kh@gmail.com)

 واتس آپ(09125197180)

__________________________________________________________________________

تصاویر مربوط به نرم افزار مدیریت انبار 

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

قابلیت نرم افزار:

1-دارای لیست محاسبه اضافه کاری

2- تهیه گزارش براساس تمامی پارامترها

3-چاپ گزارش به همراه درج جمع قابل پرداخت 

4- درج مبلغ هر ساعت بصورت فرد به فرد و گروهی

5- دریافت اطلاعات عنوان گزارش و درج در عنوان گزارش به صورت سیستمی

6-تهیه پشتیبان از نرم افزار

7- قابلیت ارتقاء براساس نیاز در تهیه گزارش های مدیریتی

8- سهولت در استفاده از نرم افزار

کد محصول: H-011

قیمت محصول: 120،000ریال

خرید نرم افزار

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

در این فرمول ترکیبی، توابع IF،COUNTA،INDEX،MID،VALUE و کاراکتر اتصال توابع و مقادیر در فرمول (&) به کار گرفته شده است.با ترکیب این توابع، می توانیم شماره اختصاصی به هر شکل و نیاز، ساخته و به عنوان شماره جدید در فرم از آن استفاده کنیم.

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

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

دانلود فایل

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

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

با آرگومان‌های ضروری و اختیاری این تابع در ذیل آشنا می شویم

آرگومان Reference: 

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

آرگومان Rows:

این آرگومان، تعداد حرکت در سطر از آدرس شروع را دریافت می کند.

آرگومان Cols:

این آرگومان، تعداد حرکت در سطر از آدرس شروع را دریافت می کند.

آرگومان Height:

دامنه حرکت محاسبه در ردیف را برای تابع مشخص می کند.

آرگومان Width:

دامنه حرکت محاسبه در ستون را برای تابع مشخص می کند.

نمونه 1- در این مثال، محل شروع محاسبه یعنی سلول D10 ،شماره سطر و ستون را وارد شده و تابع براساس آنها، در جدول آدرس را یافته و مقدار آن را بر می گرداند.

نمونه2- با استفاده از تابع MATCH ، شماره سطر در محدودهD11:D14 و شماره ستون در محدوده E10:G10 شناسایی شده و در آرگومان های مربوطه جهت محاسبه، وارد می شود. تابع MATCH، با انتخاب عنوان ستون و نام اقلام، شماره سطر و شماره ستون را جستجو کرده و مقدار عددی را بر می گرداند و در اختیار تابع OFFAET قرار می دهد.

نمونه 3- در نمونه تصویر زیر، فرمول ترکیبی از سه تابع SUM،OFFSET،MATCH است. در تابع OFFSET دو آرگومان اختیاری، دارای مقادیر بوده و براساس آنها، تابع SUM در دو ردیف و سه ستون(محدوده) مقادیر را جمع زده و با تغییر داده های دو آرگومان اختیاری تابع OFFSET نتیجه را مشاهده خواهیم کرد.

فایل نمونه مثال های بالا را می توانید دانلود کنید. 

یکی از امکاناتی که می توان با استفاده از ویژوال بیسیک(VBA) اکسل در فایل اکسل ایجاد کرد، امکان ذخیره سوابق "ذخیره فایل"(Save)، اعم از نام VBA صفحه فعال، ساعت و تاریخ (در اینجا میلادی عنوان شده ) است.

این پروژه نمونه ای از کدنویسی در محیط VBA ، برای علاقه مندان اکسل ارئه می شود. از مهم ترین بخش های یک نرم افزار، ذخیره و حفظ سوابق عملکرد آن بوده و این می تواند شروع یک کنترل عملکرد نرم افزار و حتی کاربر باشد و این کنترل به طور حتم در راستای بهبود پیشبرد اهداف یک مجموعه موثر واقع می شود.

تصویر زیر نمونه کدنویسی مبحث بالا را نمایش می دهد. و در ادامه، توضیحات مربوط به آن ذکر خواهد شد.

پس از ورود به محیط VBA اکسل(Alt+F11)، از پنجره VBAProject، ماژول ThisWorkbook را دابل کلیک کنید و در پنجره باز شده، از بخش Object، گزینه Workbook  و از بخش Procedure، گزینه BeforeSave انتخا می کنیم. با انتخاب دوم، بصورت خودکار، سابروتن(تصویر زیر) ایجاد می شود و مابین دو خط سابروتن کدهای مورد نظر را می نویسیم.

در بین دو خط، کد 

WSH = ActiveSheet.CodeName

Sheet1.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = WSH & "_" & Time & "_" & Date

را می نویسیم. در این کد، خط اول، نام VBA صفحه(Sheet) فعال را شناسایی می کند و برای استفاده در کدهای بعدی عنوان WSH را به منظور سهولت کدنویسی به آن می دهد.

خط دوم مسیر ذخیره را تعیین می کند این مسیر همان طور که پیداست در صفحه یک(Sheet1) و پایین آخرین سلول دارای داده (Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)) است و پس از یافتن، نام VBA صفحه فعال،ساعت و تاریخ(WSH & "_" & Time & "_" & Date) را درج می کند. این عملیات هم زمان اجرای ذخیره فایل در صفحه یک در ستون B اجرا می شود.

دانلود فایل

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

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

نکته1: اگر تابع VLOOKUP مورد جستجو را پیدا نکند خطایی با عنوان#N/A را نمایش می دهد

نکته2: محل مقدار جستجو(lookup_value) يعني اولين آرگومان تابع، در جدول تعریف شده(table_array ) باید اولین ستون در جدول باشد.

نکته 3: جستجوی تابع VLOOKUP بستگی به جهت صفحه گسترده دارد، همان طور که در تصویر زیر مشاهده می کنید جهت صفحه از راست به چپ است و جدول محل جستجو(اولین ستون مربوط به مورد جستجو) هم از راست به چپ می باشد.

تابع VLOOKUP دارای چهار ورودی(آرگومان) است که در ادامه توضیح خواهم داد

ساختار کلی تابع:

آرگومان اول(lookup_value): مقدار جستجو را از کاربر دریافت می کند. اگر مقدار مستقیم در تابع تعریف شود بایستی داده در داخل علامت " " قرار گیرد. این آرگومان می تواند به سلولی متصل شده و مقدار را از محتوای سلول دریافت کند( به نظر می رسد این، روش مناسبی باشد به لحاظ اینکه برای جستجوی داده های متعدد نیازی به ویرایش فرمول نیست ).

آرگومان دوم(table_array): این آرگومان، آدرس محدوده جدولی که مقدار جستجو و مورد جستجو درآن قرار دارد تعریف می شود این آدرس می تواند در همان صفحه،در صفحه دیگر آن فایل یا صفحه ای از سایر فایل ها باشد.

آرگومان سوم(col_index_num): این آرگومان، شماره ستون مورد جستجو را دریافت می کند. این آرگومان، می تواند مقدار را مستقیم دریافت کند و یا نتیجه خروجی یک فرمول یا تابع دیگر را هم به عنوان شماره ستون، در دستور کار خود می تواند قرار دهد.

آرگومان چهارم([range_lookup]): این آرگومان اختیاری است و دقت جستجوی تابع را معین می کند. این آرگومان، مقدار صفر یا یک(FALSE – TRUE) را قبول می کند.انتخاب صفر، باعث می شود تابع فقط همان مورد جستجو را در ستون مربوطه پیدا کند ولی انتخاب یک، مقادیر نزدیک به مقدار جستجو را (در صوت عدم مقدار جستجو) می یابد.

نکته4: درصورت عدم انتخاب گزینه آرگومان چهارم،تابع بصورت پیش فرض، FALSE را در نظر می گیرد.

نکته5: بهتر است آدرس جدول تعریف شده در آرگومان دوم، به صورت مطلق وارد شود و درصورت کپی کردن در سایر رکوردهای جداول گزارش ساز، در این عملیات آدرس تغییر نکند.

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

---------------------------------------------------------------------------------

امیدوارم بهره کافی را برده ببرید و با ارائه انتقاد و پیشنهاد، در راستای بهبود بخشی مطالب این وبلاگ ، بنده را مورد لطف خود قرار دهید.

دانلود فایل

تابع ارتقاء یافته VLOOKUP

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

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

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

فیلم معرفی نرم افزار مدیریت مشتریان و پیش فاکتور را دانلود کنید.

          اکسل یک صفحه گسترده است که توسط مایکروسافت برای Windows ، macOS ، Android و iOS ساخته شده است. این ویژگی شامل محاسبه ، ابزار نمودار ، جدول محوری و یک زبان برنامه نویسی کلان به نام Visual Basic for Applications است. این صفحه گسترده بسیار کاربردی برای این سیستم عامل ها است ، به خصوص از نسخه 5 در سال 1993 ، و آن را جایگزین Lotus 1-2-3 به عنوان استاندارد صنعت برای صفحات گسترده می کند. اکسل بخشی از نرم افزار مایکروسافت آفیس را تشکیل می دهد.

در بخش تنظیمات نرم افزار اکسل(Options)، برای محاسبه فرمول و نمایش نتیجه حاصل از آن، بخشی به نام Calculation Options وجود دارد که تنظیمات دلخواه خود را انتخاب می کنیم.

اولین گزینه (Automatic)، محاسبه خودکار 

دومین گزینه(Automatic except for data tables)، محاسبه خودکار به غیر از جداول و ارتباطات از سایر جداول 

سومین گزینه(Manual)، محاسبه دستی( با فشردن کلید F9 صفحه کیبرد یا ذخیره فایل "Ctrl+S" که قبل از ذخیره فایل، محاسبه فرمول انجام میشود). مزیت بروزرسانی محاسبه با حالت ذخیره فایل، حفظ داده‌های ثبت شده در فایل اکسل است.

نکته: در صورت انتخاب گزینه دستی و با انتخاب گزینه سطر پایین(Recalculate workbook before saving)، قبل از ذخیره فایل، محاسبه فرمول ها انجام می‌شود.

انتخاب روش محاسبه بصورت اختصاصی برای هر صفحه(Sheet)

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

در این روش به مراجعه به محیط VBA اکسل(کلید ترکیبی Alt+F11) ، ابتدا نام ماژول شیت را انتخاب کرده و سپس در بخش Properties ، گزینه EnableCaculation را حالت False قرار داده و به این طریق برای صفحه انتخاب شده محاسبه بصورت خودکار نخواهد بود.

موفق باشید

حسابداران و سایر مشاغل محترم در محاسبه کارکرد و حقوق به روش های مختلف با اکسل، بعضی مواقع نیاز به تبدیل جمع دقیقه کارکرد به ساعت و دقیقه را دارند و این مسئله در اکسل به راحتی و آسان قابل اجرا و محاسبه است.

 در فایل پیوست این موضوع را در فایل اکسل اجرا و در اختیار شما قرار می دهم تا مورد استفاده شما قرار گیرد.

دانلود فایل 

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

دانلود فایل

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

برای دریافت راهنمای و مشاوره، با شماره 09125197180 در WhatsApp یا Telegram  مکاتبه کنید. 

1- نرم افزار فایل یاب و فولدر یاب / طراحی شده با اکسل

2نرم افزار مدیریت فایل‌های PDF / طراحی شده با اکسل 

در فایل نمونه ای که در ذیل ارائه شده است از Userform و ابزار Toolbox بکار گرفته شده و عملیات جداسازی توسط کدهای VBA انجام می شود.

نحوه محاسبه در آن، بصورت سلیقه ای بوده و کاربران عزیز می توانند نحوه محاسبه را با متغییرات جدول خود تنظیم و استفاده کنند.

این پروژه برای منابع انسانی و حسابداران بزرگوار لازم و یک نیاز است و عزیزانی که بخواهند از این پروژه استفاده کنند به صفحه اینستاگرام kh.d_pro_excel_vba@ رجوع و از این طریق می توانند درخواست خود را مطرح کنند.

دانلود فایل

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

امیدوارم این پروژه هم مورد توجه دوستداران اکسل قرار بگیرد. 

دانلود فایل

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

ضمن اینکه منتظر نظرات و پیشنهادات ارزنده شما کاربران عزیز هستم.

دانلود فایل

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

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

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

بخش سوم، در ستون اعداد لیست شده، با فشردن کلید "شناسایی آدرس بزرگترین عدد" ، در ستون سمت چپ اعداد، آدرس سلول بزرگترین عدد در سلول جانبی درج می شود.

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

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

 Excel workshop1.xlsm

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

• نمایش تاریخ و ساعت
• نمایش نام سیستم
• شمارش تعداد اسناد وارد شده
• نمایش تاریخ راس جداگانه و ردیفی
• نمایش جمع مبلغ
• نمایش بزرگترین مبلغ واره شده
• نمایش کوچکترین مبلغ وارد شده
• نمایش نام پرینتر فعال
• نمایش محل ذخیره نرم افزار
• قابلیت کپی و درج محتوای لیست از فایل دیگر(الحاق)
• قابلیت چاپ لیست
• قابلیت تبدیل لیست به فایل PDF
• نمایش جمع کل اسناد وارد شده در ذیل صفحه
• پیماش به ابتدا و انتهای لیست با کلید جهت دار
• امکان ایجاد لیست جدید
• نصب و راندازی آسان
• قابلیت ارتقا و افزودن امکانات درخواستی مشتری در نرم افزار

 کد محصول: H-010

قیمت محصول:450،000ریال

  خرید محصول