آموزش اکسل ویژه حسابداری

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

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

در ابتدا، برنامه Office را باید در سیستم نصب کرده باشید و بعد از نصب، Excel را اجرا کنید. گزینه اول را که Blank workbook است را انتخاب کنید.

در این قسمت، محیط برنامه را مشاهده می‌کنید که با خانه های خالی چیده شده است.

 حال مانند تصاویر عمل کرده و باید خاصیت هر خانه را انتخاب کنید. اول روی خانه A راست کلید کرده و Format Cell را انتخاب کنید پنجره ای به شکل زیر باز گردد.   مثال : اگر خانه اول را به صورت Date انتخاب کنید، هر مقداری که وارد خانه شماره A می‌شود، اکسل آن را به تاریخ تبدیل می‌کند.

برای ثبت آگهی رایگان به سایت شهر 24مراجعه کنید

حسابداری در Excel

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

بگذارید در همینجا، دیگر گزینه ها را هم به شما توضیح دهم تا در مراحل بعد، تکرار نکنم. شما می‌توانید در Font، نوع فونت، اندازه فونت و حتی استایل فونت خود را نیز به دلخواه تغییر داده و انتخاب کنید. با گزینه Alignment، شما می‌توانید فونت خود را در سمت راست، سمت چپ، وسط یک خانه ببینید. بهتر است که Center را انتخاب کرده تا محیط کار اکسل شما، زیبا تر شود.

همچنین در Fill شما می‌توانید به خانه و یا نوشته خود، رنگ و لعاب دهید. به همان شکلی که در خانه A عمل کردید، می‌توانید در خانه شماره B هم به همان شکل فونت و رنگ آن را تغییر دهید اما این بار فرمت آن مانند تصویر بالا، فرق می‌کند و شما باید Currency را انتخاب کنید. خانه شماره B مبلغ می‌یاشد و عدد وارد شده را به عنوان یک عدد ریالی یا Currency می‌شناسد.

برای ثبت آگهی رایگان به سایت شهر 24مراجعه کنید

اموزش نوشتن برنامه ساده حسابداری در Excel

حال به خانه B رفته و نام این خانه را درآمد می‌گذاریم. مانند خانه A به Format Cell رفته و تنظیمات این خانه را با هم انجام می‌دهیم.

بر روی Number رفته و این خانه را روی Currency تنظیم می‌کنیم تا عددی را که وارد می‌کنید، به صورت ریال بنویسد. حتی در اینجا می‌توانید، تعیین کنید که هر عددی که وارد کردید، به طور خودکار، عدد 0 را بعد از هر عدد به آن اضافه کند. مثال : اگر شما عدد 1 را وارد کرده باشید و در تنظیمات Decimal Place را روی 3 بگذارید، عدد 1 وارد شده، 000 را به 1 اضافه می‌کند و آن عدد به طور خودکار 1000 نوشته می‌شود.

حال که خانه شماره B را تمام کردید، برای راحتی کار، خانه شماره B را با راست کلیک کرده، و در خانه شماره C، با راست کلیک کردن گزینه Insert Copied Cell را انتخاب کنید. تا خانه‌ی B در خانه C جایگذاری یا Paste شود.

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید

نوشتن برنامه ساده حسابداری در Excel

بعد از اینکه مانند تصویر بالا، کار Copy و Paste یا همان جایگذاری را انجام دادید، می‌توانید اسم خانه را هزینه بگذارید و تنها کافیست که روی آن کلیک کنید و اسم آن را به دلخواه تغییر دهید.

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

در خانه آخر هم گزینه توضیحات را اضافه کنید اما این بار در قسمت Format Cell اوضاع کمی فرق می‌کند.

در این قسمت، شما در Number باید Text را انتخاب کنید تا هر آنچه را که می‌نویسید، به شکل Text یا حالت نوشتاری نشان دهد و برنامه با این روش متوجه می‌شود که شما فقط این خانه را برای نوشتن یا نوت برداری استفاده می‌کنید.

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

شمارش داده ها در اکسل

۴.شمارش سلول های “کمتر از” یا “بزرگتر از”

  ۱) تعداد سلولهای بزرگتر یا مساوی 

 در ابتدا سلول مورد نظر خود را انتخاب کنید (سلول A12 در این مثال) و سپس فرمول را در آن نوشته  

(“COUNTIF(B1:B10,”>=10=  

 (در این مثال ما بدنبال عدد بزرگتر یا مساوی ۱۰ هستیم و چون یک معیار عددی است، باید در علامت نقل قول دوگانه قرارگیرد) اما به جای تایپ شرط بصورت  مستقیم در یک فرمول، اگر سلول B12 حاوی شماره ۱۰ باشد، می توانیم به یک سلول ارجاع دهیم. به عنوان مثال

  =COUNTIF(B1:B10,”>=” & B12)

 ۲)شمارش سلول بین دو عدد

 شما می توانید فرمول COUNTIF را برای شمارش ردیف هایی که در محدوده ای از مقادیر هستند، ترکیب کنید. در این مثال، ردیف هایی را که در آن مقدار     بین ۵ تا ۱۰ هستند را می شماریم.

 در ابتدا سلول مورد نظر خود را انتخاب کنید (در این مثال سلول A12 ) و فرمول را تایپ کنید

  =COUNTIF(B1:B10,”>=5″)-COUNTIF(B1:B10,”>10″)

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

 شمارش داده ها در اکسل

 ۳. شمارش اعداد در اعداد

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

 در تصویر بالا، ۴ مورد حاوی عدد “۱ ” است ولی جواب فرمول COUNTIF که در سلول   A9 نوشته شده عدد ۳    می باشد زیرا در سلول A2 عدد ۱ را به   این دلیل که سلول شامل یک عدد واقعی است، نه یک رشته متنی نمی شمارد. برای حل این مشکل ما بایستی از تابع، FIND یا SEARCH برای شمارش اعداد خاص در اعداد استفاده کنیم.

 در تصویر زیر فرمول های FIND ، SEARCH، COUNTIFدر ستون های C، D و E برای جستجوی “۱” استفاده می شود.

• • (“*C2: =COUNTIF(A2,”*1 
  • ((D2: =–ISNUMBER(FIND(1,A2 
  • ((E2: =–ISNUMBER(SEARCH(1,A2 

• •   تابع ISNUMBER  در ابتدای فرمول  FINDو SEARCHبه ما بصورت مجزا  نشان میدهد که در هر ردیف در صورت موجود بودن عدد مورد نظر عدد  (۱) و ناموجود بودن، عدد(۰) را  به ما نشان دهد . راه حل بعدی این است که ما می توانیم به جای چک کردن هر ردیف به صورت جداگانه، از تابع   SUMPRODUCT در ابتدای فرمول های بالا تعداد کل را بدست آوریم

• • (((A9:    =SUMPRODUCT(–ISNUMBER(FIND(1,$A$2
  • 
    (((A10:  =SUMPRODUCT(–ISNUMBER(SEARCH(1,$A$2:$A$7

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

۷ روش برای شمارش در اکسل وجود دارد:

​ ۲.شمارش سلول هایی که با یک شرط مطابقت دارند

 · یک شرط را دقیقا مطابقت دهید

  درابتدا داده های نمونه را در صفحه کار خود را وارد کنید. سپس مثل تصویر در سلول A12 فرمول:

(“COUNTIF(A1:A10,”Pen=   

   را وارد کنید (Pen کلمه ایی است که می خواهیم بدانیم خود کلمه Pen در داده های ما چند بار تکرار شده است ) و با زدن ENTER، مشاهده می کنید که نتیجه    آن ۴ میشود که به معنای تعداد سلول هایی است که درون آن کلمه مورد نظر ما (Pen) می باشد.

  ·شرط را در یک رشته متنی مطابقت دهید

   درابتدا داده های نمونه را در صفحه کار خود را وارد کنید. سپس مثل تصویر در سلول A12 فرمول

(“*COUNTIF(A1:A10,”*Pen=   

 را وارد کنید (Pen کلمه ایی است که می خواهیم بدانیم ،خود کلمه Pen یا کلماتی که در آن Pen  وجود دارد چند بار تکرار شده است) و با زدن ENTER،     مشاهده می کنید که نتیجه آن ۶ می شود که به معنای تعداد سلول هایی است که حاوی کلمه مورد نظر (Pen) می باشد.

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

 ۷ روش برای شمارش در اکسل وجود دارد:

  ۱. شمارش سلول های عددی،سلول های حاوی داده و سلول های خالی

· شمارش سلول های عددی با COUNT

 در ابتدا داده های نمونه را در صفحه کار خود را وارد کنید.

 مثل تصویر در سلول A7، فرمول COUNT را وارد کنید.

(COUNT(A1:A5=  

  و با زدن enter ، مشاهده می کنید که  نتیجه آن عدد ۳ میشود که به معنای تعداد سلول هایی است که حاوی اعداد هستند و سلول A1 شمارش نمی شود، زیرا حاوی متن است.

· شمارش سلول های حاوی داده با COUNTA

  درابتدا داده های نمونه را در صفحه کار خود را وارد کنید.مثل تصویر در سلول A7 فرمول

(COUNTA(A1:A5=  

  را وارد کنید و با زدن ENTER، مشاهده می کنید که نتیجه آن ۴ میشود که به معنای تعداد سلول هایی است که حاوی داده هستند.

· شمارش سلول های خالی با COUNTBLANK

درابتدا داده های نمونه را در صفحه کار خود را وارد کنید.مثل تصویر در سلول A7 فرمول

(COUNTBLANK(A1:A5=  

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

نکته: سلول هایی که شبیه سلول های خالی هستند:

 فرمول COUNTBLANK سلول های خالی را شمارش می کنند، اما اگر در یک سلول به ظاهر خالی یک space  هم خورده باشد فرمول COUNTBLANK آن سلول را بعنوان متن شناخته و شمارش نمی کند.

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

در حالت عادی در صورت تایپ کردن یک عدد کسری مانند (۲/۳) در اکسل دو حالت رخ می دهد یا اکسل  آن سلول را به عنوان تاریخ می شناسد یا اینکه بصورت یک متن با آن برخورد می کند. در هر دو حالت مقدار واقعی کسر را نداریم و نمی توانیم در محاسبات از آن استفاده کنیم. در این مطلب قصد داریم ابتدا با فرمت اعداد کسری آشنا شویم و بعد یک راه میانبر برای ورود داده های کسری را یاد بگیریم. با ما همراه باشید.

روش اول:

برای وارد کردن یک عدد کسری در یک سلول ابتدا باید فرمت آن سلول را به اعداد کسری تغییر دهیم:

    روی سلول مورد نظر راست کلیک کرده و Format Cells را انتخاب کنید. (Ctrl+1  )
    از لیست Category: گزینه Fraction  را انتخاب کنید.
    در قسمت Type می تواند حالت های مختلف عدد کسری و نحوه نمایش آنرا در کادر Sample مشاهده کنید.

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

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

 

روش دوم:

برای وارد کردن یک عدد مخلوط در یک سلول، یک فاصله بین عدد صحیح و بخش کسری آن قرار دهید. (۵/۴ ۱) و اگر کسر کوچکتر از واحد بود ابتدا عدد صفر بعد فاصله و کسر را وارد کنید(۵/۴ ۰). در غیر اینصورت اکسل عدد را بصورت تاریخ در نظر می گیرد. با مشاهده فرمت این سلول متوجه می شویم که بصورت اتوماتیک حالت Fraction  فعال شده است.

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

 

                                                                     

اگر بخواهید عدد کسری بصورت اعشار نمایش داده شود کافیست از کادر Format Cells نوع داده را به Number تغییر دهید.

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

New: اضافه کردن ردیف جدید

ِDelete: حذف ردیف جاری

Restore: در صورتی که در حال ویرایش یک سلول هستید و نمی خواهید آن تغییرات اعمال شود از این گزینه استفاده کنید.

Find Prev: رفتن به رکورد قبل

Find Next: رفتن به رکورد بعد

Criteria: جستجو در جدول با شرط (یافتن شخصی با نام علی در جدول)

Close: خروج از فرم

سلول های دارای فرمول در پنجره Form غیر فعال نمایش داده می شود یعنی نمی توان از این طریق فرمول را در سلول وارد کرد.

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

حال به مرحله آخر می‌رسیم و باید مشخص کنیم که در بانک اکسل، چه مقدار موجودی وجود دارد. برای این کار، به خانه F2 کلیک کرده و همانند تصویر بالا، کد را وارد می‌کنیم. با وارد کردن این کد، به سیستم می‌فهمانیم که هر چه در خانه B2 وارد کردیم را از خانه C2 کم کن و در خانه F2 وارد کن و به من نشان بده.

اما موضوع در خانه F3 کمی فرق می‌کند. همانند تصویر بالا، کد را وارد کنید و این کد به آن معنی است که خانه B3 را از خانه C3 کم کن و حاصل این دو را با خانه F2 جمع کن و در خانه F3 قرار بده. شاید سئوالی برای شما پیش بیاید که چرا پرانتز ؟! اگر یادتان باشد، در ریاضیات، اگر 3 عدد وارد شده داشته باشیم، که فقط دو عدد باید از هم کم شوند و حاصل این دو عدد با عدد سوم ضرب، کم، جمع یا تقسیم شود، از پرانتز استفاده می‌کردیم. یعنی اینکه، اول دو عدد را از هم کم کن و بعد حاصل را از عدد سوم جمع کن.

باز هم مراحلی را که برای مجموع درآمد و مجموع هزینه، انجام دادیم، دوباره برای موجودی بانک هم انجام می‌دهیم. منظورم همان Copy, Paste کردن است.

با کپی کردن بر روی خانه F3 و تا جایی که می‌خواهیم این اعمال انجام شود، رفته و کلید shift را نگه داشته و ماوس را بر روی آخرین خانه، کلیک کرده و کلید Enter را فشار می‌دهیم.

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

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

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

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

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

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

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

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

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

بر روی خانه E3 راست کلیک کنید و عمل Copy کردن را همانند مرحله مجموع درآمد، عمل کنید و تا جایی که می‌توانید، تا آخرین خانه پیش بروید و بعد از آن با نگه داشتن کلید shift بر روی صفحه کلید کامپیوتر یا لپ‌تاپ، کلید Enter را بفشارید تا عمل Paste کردن انجام گیرد.

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

اکسل در حسابداری(پارت 5)

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

بر روی خانه شماره D2 کلیک کنید و کدی را که نوشتم را بنویسید. اگر به خانه شماره B2 دقت کنید، متوجه می‌شوید که آن خانه، آبی رنگ شده است و به این معنی است که کدی که نوشتید، به آن خانه توجه می‌کند. B2= به آن معنی است که هر زمان، در خانه شماره B2 چیزی نوشتم، آن را در خانه شماره D2 نشان بده.

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

در مرحله بعد، موضوع کمی پیچیده تر می‌شود اما این قرار نیست که شما را بترساند. همانند تصویر بالا، عمل کنید و روی خانه D3 با ماوس خود کلیک کنید تا بتوانید، همانند تصویر، کد مورد نظر را وارد کنید. زمانی که B3+D2= را وارد کردید، عمل وارد شده به آن معنی است که هر چه که در خانه B3 قرار می‌دهم را با خانه D1 جمع کن، و در خانه D3 قرار بده.

حال برای سادگی کار، همانند مراحلی که برای Copy و Paste توضیح دادم، این‌بار هم خانه D2 را راست کلیک کرده و Copy کنید.

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید.

شمارش داده ها در اکسل

۵. شمارش سلول هایی که دارای معیارهای مختلف هستند

 در این مثال، تابع SUMPRODUCT برای شمارش ردیف هایی که آیتم “Pen” و مقدار آن بزرگتر یا مساوی ده است استفاده شده است . این راه حل در نسخه های قدیمی تر Excel که تابع COUNTIFS موجود نیست کاربرد دارد.

۶.شمارش همه تاریخ ها در ماه و سال مورد نظرمان

 در این مثال، یک تاریخ در A2 وجود دارد و لیست سفارش در سلول A5: A26 دارای تاریخ است. با نوشتن تابع SUMPRODUCT در سلول D2 می توانیم تمام تاریخ هایی را که در همان ماه و سال به عنوان تاریخ در سلول A2 قرار دارند را بشماریم.

  =SUMPRODUCT((MONTH(A5:A26)=MONTH(A2))*(YEAR(A5:A26)=YEAR(A2)))

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید

۷.شمارش ردیف هایی که در یک لیست فیلتر شده اند

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

 ۱)برای شمارش ساده تعداد قابل مشاهده یا تمام داده های قابل مشاهده، تابعSUBTOTAL

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

  =SUBTOTAL(3,D2:D10)

 ۲)برای شمارش اطلاعات قابل مشاهده و نادیده گرفتن اشتباهات، از تابع AGGREGATE

پس از فیلتر کردن ردیف ها در یک لیست، می توانید از تابع AGGREGATE برای شمارش ردیف های قابل مشاهده استفاده کنید. این تابع در اکسل ۲۰۱۰ معرفی شده و شبیه به SUBTOTAL است، اما دارای ۱۹ عملکرد در مقایسه با ۱۱ توابع SUBTOTAL است. مزیت دیگر این است که میتواند اشتباهات و ردیفهای پنهان را نادیده بگیرد.

در ابتدا حداقل یکی از ستونهای جدول را فیلتر کنید. در این مثال، ستون اول برای Binders فیلتر شده است. سلول را انتخاب کنید (در این مثال سلول B1)و فرمول زیر را بنویسید

=AGGREGATE(3,3,Table1[Total])

 ۳)برای شمارش موارد خاص در یک لیست فیلتر شده، از فرمول SUMPRODUCT

 با استفاده از این تکنیک، SUMPRODUCT می تواند  برای شمارش آیتم های قابل مشاهده در یک جدول فیلتر شده مورد استفاده قرار گیرد. در مثال بالا ستون D برای مقادیر بیش از ۱۰۰ فیلتر شده است. فرمول زیر تعدادی از ردیف های قابل مشاهده که حاوی “Pen” در ستون A را شمارش می کند.

 از لیست کشویی در سلول D1 فیلتر برای ردیف های بیش از ۱۰۰را انتخاب کنید سپس

 در سلول A12، نوع:Pen را نوشته ودر سلول B12، فرمول زیر را وارد کنید:

 =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)), –(A1:A10=A12))

۴)برای شمارش آیتم هایی که در یک لیست فیلتر شده، از فرمول UNIQUE

 در این مثال، لیست برای منطقه Central فیلتر شده است، و موارد منحصر به فرد در ستون D شمارش می شوند.

   =SUM(N(IF(ISNA(MATCH(“”””,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”””,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))

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

 

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

 

این تابع را نمیتوانید مستقیما در صفحه اکسل وارد کنید و برای استفاده از آن باید در تب Formula و قسمت Name Manager یک نام تعریف کرده و در قسمت Refer to فرمول را تایپ کنید و به سلولی که درون آن فرمول شما نوشته شده است ارجاع دهید.

 

مراحل انجام کار به صورت زیر می باشد.

    - انتخاب سلول مجاور سلولی که فرمول درون آن نوشته شده.
    ۲- تب Formula گزینه Define Name
    3- در قسمت Name نام دلخواه خود را بدون اسپیس وارد کنید.
    ۴- در قسمت Refer to تابع Evaluate را نوشته و بعد از باز کردن پرانتز سلول حاوی فرمول خود را انتخاب کنید و سپس سه بار کلید F4 را بزنید تا از حالت مطلق خارج شود.

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید

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

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

در ادامه به تفاوتهای این دو نرم افزار می پردازیم.

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

عملکرد- برنده، اکسل

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

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

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

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

به اشتراک گذاری- برنده، گوگل شیت

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

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

هرگونه ویرایش یا نظر ایجاد شده بطور همان زمان دیده می شود و شما به سرعت می توانید ببینید که سلولهای دیگر کاربران چه هستند، همچنین میتوانید تغییرات را با کلیک روی گزینه Last Edit که بالای تولبار است ردیابی کنید.

اکسل اجازه به اشتراک گذاری و همکاری را می دهد اما نه به وسعت گوگل شیت. شما در به اشتراک گذاری فایل از طریق ایمیل به اندازه ای که گوگل شیت به شما اجازه می دهد دستتان باز نیست. اگر از آفیس ۳۶۵ استفاده کنید گزینه های مشابه گوگل شیت برای به اشتراک گذاری فایل با سایر کاربران را خواهید داشت و تقریبا فاصله این دو نرم افزار در به اشتراک گذاری فایل ها بهم نزدیک است اما تا الان گوگل شیت برنده است.

فضای ابری و آپدیت فایلها- برنده، گوگل شیت

یکبار دیگه گوگل شیت بالاتر از اکسل قرار میگیره اما چرا؟ گوگل شیت برخلاف اکسل از ابتدا برمبنای فضای ابری ساخته شد. همه چیز از طریق حساب گوگل شما قابل دسترس هست و شما از طریق گوگل درایو به کلیه فایلهاتون دسترسی دارید.

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

اگر از آفیس ۳۶۵ استفاده می کنید شما به همین مقدار امکان آپدیت پذیری فایل ها که در گوگل شیت دارید، دسترسی خواهید داشت اما سایر ورژن های آفیس که روی سیستم نصب می شود نیاز به تنظیمات دارید و این نقطه دیگه ای هست که گوگل شیت جلوتر هست ولی مایکروسافت هم فاصله کمی تا این نقطه داره.

دسترسی آفلاین- برنده، اکسل

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

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

عملگرها و توابع- برنده، اکسل

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

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

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

هزینه- برنده، گوگل شیت

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

خلاصه

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید

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

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

مایکروسافت اکسل چیست؟

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

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

کاربردهای عمومی اکسل

مدیریت بودجه برای خانواده‌ها یا دارایی‌ کسب و کارها

مدیریت فاکتور‌ها و رسید‌ها

پیگیری پروژه‌ها، فروشنده‌ها و مشتری‌ها و سوابق سلامت اشخاص

مدیریت برنامه‌ها و تقویم‌ها

ایجاد چک لیست و لیست وظایف

محاسبات مالی، وام، بدهی و گرونامه‌ها

مدیریت اموال

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

آشنایی با اصطلاحات اکسل

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

کتاب کار و صفحه‌ی گسترده: کتاب کار چیزی است که وقتی اکسل را باز می‌کنید آن را مورد استفاده قرار می‌دهید. کتاب کار حاوی صفحات گسترده است. یک کتاب کار می‌تواند چندین صفحه‌ گسترده داشته باشد و شما می‌توانید با استفاده از تب‌هایی [که عموما در پایین صفحه قابل مشاهده هستند] از صفحه‌گسترده‌های مختلفی که باز هستند استفاده کنید. [برای راحتی کار از این به بعد به جای صفحه‌گسترده که معادل آن در زبان انگلیسی SpreadSheet است، از گزاره‌ی صفحه استفاده می‌کنیم.]

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید

سلول: صفحه‌ها از بلوک‌های کوچکی به نام سلول تشکیل شده‌اند. هر سلول حاوی داده‌ای است که در آن وارد می‌کنید؛ از اعداد تا حروف و عکس و حتی فرمول‌. سلول‌ها وظیفه دارند این اطلاعات را در خود ذخیره کنند. شما می‌توانید یا به صورت مستقیم اطلاعات را وارد سلول‌ها کنید یا اینکه از نوار فرمول بالای صفحه استفاده نمایید.

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

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

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

تب‌ها و نوار ابزار در اکسل

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

در زیر به اختصار راجع به هر کدام توضیح می‌دهیم:

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

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

Insert: برای درج جداول، نمودارها، تصاویر، فیلتر‌ها و لینک‌ها به کار می‌رود.

Page Layout: محیط ظاهری صفحه را تنظیم می‌کند و به طور کلی به اعمال مربوط به صفحه می‌پردازد.

Formulas: می‌توانید از این طریق یک فرمول یا تابع انتخاب کرده و یا به آن‌ها رسیدگی کنید.

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

برای ثبت آگهی رایگان به سایت شهر 24 مراجعه کنید