برنامه نویسی پایتون
۲۳۸۶ نفر
۱۲ساعت
۵۰۰,۰۰۰ تومان
خرید دوره
اکسل بهواسطه ابزارها و امکانات فرمولنویسی متعدد خود امکان پیگیری بسیاری از عملیاتهای حسابداری را در کوتاهترین زمان و به سادهترین صورت فراهم کرده است. گاهیاوقات در زمان کار کردن با برنامه اکسل به یک روش سریع برای جمع زدن قیمتها یا دیگر دادههای موجود نیاز پیدا میکنید، در این شرایط میتوان سراغ انواع جمع در اکسل رفت. برای آشنایی با روش جمع در اکسل و اطلاع از فرمول جمع و تفریق دو ستون در اکسل تا انتهای این مطلب با ما در آکادمی همراه اول بمانید.
در سادهترین و سریعترین روش موجود برای جمع کردن بخشی از دادهها در نرمافزار اکسل میتوان سراغ دکمه AutoSum رفت که به صورت خودکار تابع Sum یا همان جمع را روی محدوده موردنظر شما اعمال میکند.
برای این منظور لازم است اولین سلول خالی زیر محدوده موردنظر را که در مثال ما سلول A5 است انتخاب کنید:
بعد از آن در تب Home روی دکمه AutoSum بزنید یا اینکه از کلیدهای ترکیبی = + Alt استفاده کنید:
در این مرحله از جمع ستون در اکسل، فرمول تابع SUM به همراه ارجاعات مرتبط به سلولهای موردنظر نمایش داده میشوند. اگر این اتفاق نیفتاد، میتوانید سلولها را به صورت دستی درون فرمول اضافه کنید و دکمه Enter را فشار دهید تا اجرا شود.
برای اینکه از تمامی قابلیتهای مرتبط با AutoSum بهرهمند شوید، لازم است نکات و موارد زیر را برای استفاده از فرمول جمع دو ستون در اکسل در نظر داشته باشید:
اگر قصد جمع زدن ستونی از اعداد را دارید، سلولی را انتخاب کنید که دقیقاً زیر آخرین عدد ستون قرار گرفته است. برای جمع زدن ردیف اعداد هم به سراغ سلولی بروید که در سمت راست آخرین عدد ردیف موردنظر قرار گرفته باشد.
امکان استفاده همزمان از بیشتر از یک سلول و قابلیت AutoSum وجود دارد. برای این منظور میتوانید دو سلول را انتخاب و روی این گزینه کلیک کنید تا اکسل هر دو ستون را به صورت همزمان جمع بزند.
برای استفاده از قابلیت AutoSum میتوانید سراغ قسمتهای Home > AutoSum و Formulas > AutoSum بروید.
در زمان ساخت فرمول به جای تایپ بیش از اندازه میتوانید آن را در دیگر سلولها کپی کنید. با این کار فرمول به صورت خودکار و با توجه به موقعیت تنظیم میشود و اعداد محدوده موردنظر را با هم جمع میبندد.
حال که با کلید میانبر جمع در اکسل آشنا شدید بهتر است ببینید چطور میتوانید برای تعدادی از سلولهای یک ستون در محدودهای بزرگ روند جمع کردن را به صورت دستی انجام دهید. برای این منظور مراحل زیر را دنبال کنید:
روی سلولی از جدول خود بزنید و تابع Sum را در آن به صورت زیر وارد کنید:
در این مرحله از آموزش تصویری جمع اعداد در اکسل محدودهی موردنظر خود برای جمع زدن را انتخاب کنید و در نهایت روی کلید Enter بزنید (توجه: امکان وارد کردن آدرس محدوده به صورت دستی برای محدودههای بزرگ هم وجود خواهد داشت).
حال میتوانید ستون موردنظر را به صورت جمعزده شده مشاهده کنید. البته باید در نظر داشته باشید که تابع Sum مقادیر مربوط به ردیفهای پنهان و فیلتر شده در اکسل را هم محاسبه خواهد کرد؛ بنابراین اگر قصد دارید صرفاً سلولهای قابل مشاهده را جمع کنید باید سراغ روش دیگری بروید که در ادامه مورد بررسی قرار گرفته است.
این روش یکی از بهترین گزینههای پیشرو جمع در اکسل برای سلولهای قابل مشاهده محسوب میشود. برای این منظور در اولین قدم باید جدول خود را فیلتر کنید و بعد از آن از تب Data گزینه Filter را انتخاب کنید:
حال باید فلشهایی را در بخش سربرگ ستونهای خود مشاهده کنید که میتوانند برای محدود و فیلتر کردن دادههای مورد استفاده قرار بگیرند. برای این منظور باید تیک گزینه Select All را بردارید و مقادیر موردنظر را برای فیلتر کردن علامت و در نهایت روی OK بزنید:
در این مرحله به سراغ انتخاب محدوده و اعداد موردنظر برای اضافه کردن بروید و از قسمت Home گزینه AutoSum را انتخاب کنید.
در صورتی که قصد دارید سلولهای قابل مشاهده را بدون چسباندن همهی آنها به جدول خود جمع بزنید میتوانید دامنه را انتخاب کنید و مجموع سلولهای انتخابی را در نوار وضعیت اکسل ببینید.
این تابع از نسخه ۲۰۱۰ اکسل در دسترس کاربران قرار گرفت و عملکردی مشابه تابع Subtotal دارد با این تفاوت که توابع بیشتری را پوشش میدهد و میتواند خطاهای مقداری مثل ردیفهای داده پنهان را هم نادیده بگیرد. برای استفاده از این قابلیت باید مراحل زیر را دنبال کنید:
در ابتدا سلول نتیجه را انتخاب و قسمت اول فرمول را در آن وارد کنید.
حال در لیست مربوط به اعداد تابعی روی عدد ۹ بزنید تا عملیات جمع زدن انجام شود.
بعد از قرار دادن کاما در بین گزینهها عدد ۳ یا ۷ را انتخاب کنید که در این مثال عدد ۳ دومین آرگومان را تشکیل میدهد.
با این کار ردیفهای پنهان، خطاها و توابع تو در توی Subtotal و Aggregate نادیده گرفته خواهند شد.
در این مرحله لازم است محدودهای از سلولهای حاوی داده را انتخاب کنید.
در نهایت بعد از وارد کردن پرانتز بسته دکمه Enter را بزنید تا فرمول نهایی اجرا شود.
روشهای حل مشکل جمع نشدن اعداد در اکسل
در موارد خاصی با وجود اینکه دادهها شبیه به اعداد به نظر میرسند اما امکان اعمال انواع جمع در اکسل بر روی آنها وجود ندارد، به دلیل اینکه برنامه آنها را به عنوان متن در نظر گرفته است. در بیشتر موارد میتوان با استفاده از گزینه Paste Special و راهکارهایی که در ادامه مورد بررسی قرار گرفتهاند این مسئله را برطرف کرد:
برای استفاده از قابلیت Paste Special و برطرف کردن مشکل اعداد متنی میتوانید:
روی یک سلول خالی راست کلیک و گزینه Copy را انتخاب کنید.
سلولهای حاوی اعداد متنی را انتخاب و بعد از کلیک راست کردن روی یکی از آنها به سراغ گزینه Paste Special بروید.
با این کار پنجره مربوط به این قابلیت باز میشود که باید در بخش Paste گزینه Values و در بخش Operation هم Add را انتخاب و در نهایت روی OK بزنید.
بعد از درست شدن مشکل اعداد میتوانید در بخش Home از منو کشویی Format روی Number کلیک و قالببندی مناسب را برای اعداد انتخاب کنید.
در صورتی که در زمان استفاده از کلید میانبر جمع در اکسل به تغییر مداوم قالببندی سلولها نیاز دارید، بهتر است از VBA استفاده کنید. برای این منظور باید:
پیش از هر چیز از کلیدهای ترکیبی Alt + F11 استفاده کنید تا پنجره کدنویسی مربوطه باز شود.
روی شیت موردنظر راست کلیک و گزینههای Insert > Module را انتخاب کنید.
در صفحه باز شده کد زیر را وارد کنید:
Sub ConvertToNumbers()
Dim rng As Range
‘get constants in selected range
On Error Resume Next
Set rng = Selection _
.SpecialCells(xlCellTypeConstants, 23)
On Error GoTo errHandler
If Not rng Is Nothing Then
‘copy blank cell outside used range
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(0, 1).Copy
‘add to selected cells
rng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
Else
MsgBox “Could not find Constants in selection”
End If
exitHandler:
Application.CutCopyMode = False
Set rng = Nothing
Exit Sub
errHandler:
MsgBox “Could not change text to numbers”
Resume exitHandler
End Sub
حال باید از محیط VBA خارج شوید و پس از آن به کمک تابع Convert To Numbers متن را به عدد تبدیل کنید.
سخن پایانی
همانطور که ملاحظه کردید جمع یک ستون با یک عدد ثابت در اکسل و انواعی از داده به سادگی و با دنبال کردن روشهای شرح داده شده انجام میشود. زمانی که با حجم زیادی از داده طرف هستید با استفاده از فرمول جمع در اکسل میتوانید این کار را سریعتر از حالت معمول انجام دهید. در هر صورت اگر نیاز دارید در مورد کار کردن با نرمافزار اکسل اطلاعات بیشتری به دست آورید، به شما توصیه میکنیم شرکت در دوره تحلیل داده آکادمی همراه اول را از دست ندهید تا از جدیدترین آموزشهای بهروز در این زمینه بهرهمند شوید.
