مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin
تبليغات
۳۰
آبان ۹۳
نظرات
۲۴
آشنایی با توابع شمارش سلول‌های پر و خالی در اکسل
مرادی
حالت ۱) شمارش تعداد کاراکتر‌های داخل پرانتز که با ویرگول از هم جدا شده‌اند اعم از حروف و اعداد فارسی و انگلیسی و حتی فاصله. که در این حالت دستور این تابع به صورت زیر است:
=COUNTA(value1, [value2], ...)
مثلا جواب فرمول زیر برابر است با ۳
=COUNTA(a,100,ش)
حالت ۲) شمارش تعداد سلول‌های پر شامل هر نوع کاراکتری حتی فاصله (منظور سلول خالی نیست، بلکه منظور کاراکتر فاصله است). در این حالت دستور این تابع به صورت زیر است:
=COUNTA(range)
مثل:
=COUNTA(B2:B10)
ترکیب دو حالت ۱ و ۲ را نیز می‌توان برای تابع COUNTA استفاده کرد مثل:
۲- تابع COUNT: شمارش تعداد اعداد و تعداد سلول‌های شامل عدد از جمله تاریخ‌ها
حالت ۱) شمارش تعداد اعداد داخل پرانتز که با ویرگول از هم جدا شده‌اند. دستور این تابع در این حالت به صورت زیر است:
=COUNT(value1, [value2], ...)
مثلا جواب فرمول زیر برابر است با ۲
=COUNT(1, 5, ش)
حالت ۲) شمارش تعداد سلول‌های پر شامل عدد از جمله تاریخ. دستور این تابع در این حالت به صورت زیر است:
=COUNT(range)
مثل:
=COUNT(B2:B10)
ترکیب دو حالت ۱ و ۲ را نیز می‌توان برای تابع COUNT استفاده کرد مثل:
۳- توابع COUNTIF و COUNTIFS: شمارش شرطی
برای شمارش تعداد سلول‌هایی که دارای معیار مشخصی باشند از توابع COUNTIF و COUNTIFS استفاده می‌شود. در واقع این دو تابع، توابع شمارش شرطی هستند.
تابع COUNTIF:
از این تابع زمانی استفاده می‌شود که دارای یک شرط باشیم. ساختار این تابع به صورت زیر میباشد:
=COUNTIF(range, criteria)این تابع دو آرگومان یا ورودی دارد. اولین آرگومان آدرس فهرستی است که شمارش باید برای آن انجام شود. دومین آرگومان. معیار یا شرط شمارش است. مثلا، خروجی فرمول زیر، تعداد سلول‌هایی است که در ناحیه B2:B7 دارای عددی برابر با ۲۰۰ می‌باشند. یا:
=COUNTIF(B2:B7,200)
یا خروجی فرمول زیر، تعداد سلول‌هایی است که در ناحیه B2:B7 دارای کاراکتری برابر با کاراکتر موجود در سلول E2 می‌باشند.
=COUNTIF(B2:B7,E2)
بجای حالت برابری در فرمولهای فوق می‌توان از حالت‌های کوچک‌تر و کوچک‌ترمساوی و همچنین بزرگ‌تر و بزرگ‌ترمساوی نیز استفاده کرد. مثال:
=COUNTIF(B2:B7,">200")
یا
=COUNTIF(E2:E7,"<=300")
اما اگر بخواهیم بجای مقایسه با یک عدد با عدد موجود در یک سلول خاص این مقایسه انجام شود، باید فرمول فوق را کمی تغییر داد. بطور مثال میخواهیم بدانیم مطابق تصویر زیر در محدوده E2:E7 چند سلول بیشتر و چند سلول کمتر از سلول B7 هستند. برای اینکار از فرمولهای زیر استفاده میکنیم:
=COUNTIF(E2:E7,">"&B7)
=COUNTIF(E2:E7,"<"&B7)
خروجی فرمولهای فوق به ترتیب ۴ و ۱ می‌باشند.
این تابع همچنین می‌تواند برای داده‌های غیر عددی نیز استفاده شود. مثلاً تابع
=COUNTIF(B2:B25,"Excel")
سلول‌هایی را شمارش می‌کند که در آن‌ها کلمه “Excel” قرار داشته باشد. (این تابع به کوچکی و بزرگی حروف حساس نیست).
تابع COUNTIFS:
این تابع زمانی استفاده می‌شود که دارای بیش از یک شرط باشیم. ساختار این تابع به صورت زیر میباشد:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
مثال:
=COUNTIFS(B2:B7,200,E2:E7,300)
که در این صورت اگر هر دو شرط همزمان برقرار باشند آنگاه این سلول‌ها شمارش خواهند شد.
تمام حالت‌های گفته شده برای تابع Countif، برای تابع Countifs نیز قابل اجرا است.
مثال ۱) در تصویر فوق، در ناحیه E2:E7، تعداد سلول‌های حاوی عدد ۱۰۰ یا ۳۰۰ را محاسبه کنید.
به نظر می‌رسد که مشابه مثال قبل با دو شرط روبرو هستیم و باید از تابع Countifs استفاده کنیم:
=COUNTIFS(E2:E7,100,E2:E7,300) اما استفاده از فرمول فوق اشتباه است، این فرمول بیانگر این است که باید هر دو شرط با هم برقرار باشد یعنی تابع Countifs پس از بررسی شرط اول یعنی برابری با ۱۰۰، شرط دوم یعنی برابری با ۳۰۰ را بررسی می‌کند که ما چنین چیزی نمی‌خواهیم. فرمول باید طوری باشد که در صورتی که یکی از دو حالت نیز برقرار بود شمارش انجام شود. البته این نکته را نیز باید دانست که درون تابع Countifs نمی‌توان از OR استفاده کرد. برای حل این مشکل دو راهکار وجود دارد:
در واقع ما با یک شرط روبرو هستیم. باید دو عدد را درون کوشه بگذارید و همراه با تابع Countif از تابع Sum هم استفاده کنید:
=SUM(COUNTIF(E2:E7,{100,300}))
یا اینکه از مجموع دو Countif استفاده کنید:
=COUNTIF(E2:E7,100)+COUNTIF(E2:E7,300)
خروجی هر دو فرمول فوق برابر است با ۴.
مثال ۲) شمارش تعداد سلول‌های شامل متن (اعداد شمارش نشود) و تعداد سلول‌هایی که دارای متن نیستند:
برای مشاهده مثال‌های بیشتر از کاربرد Countif همراه با استفاده از * و ? به اینجا مراجعه کنید.
دستور این تابع به صورت زیر است:
=COUNTBLANK(range)
مثل:
=COUNTBLANK(A1:A9)
منابع با تغییر: wikipg.com
excel2013.blogfa.com
مقاله کامل‌تر را میتوانید در اینجا مشاهده کنید.
برای مطلع شدن از جدیدترین مطالب سایت، کانال پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و تلگرام با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آی‌دی تلگرام: pctarfand_admin@ |
نظرات (۲۴)
-
رضا
سلام.روزتون بخیر و شادی
می خواستم بدونم روشی وجود داره که ما فرمولهای شرطی رو وقتی تعریف می کنیم و اعداد مشخصی بدست میاریم با اعمال یک فیلتر در منبع اطلاعاتمون، داده های بدست امده از فرمولها بر اساس مقادیر فیلتر شده باشه؟
مثل subtotal
البته توی subtotal استفاده برای تابع های count ifs و if وجود نداره.چطوری می تونم این کار رو انجام بدم؟-
پاسخ:
سلاممی تونید یک مثال بزنید؟
-
-
محمد
سلام مطالب سایت شما بسیار مفید و آموزنده است . موفق و موئید باشید. -
احمد
سلام.
می خواستم بدونم آیا امکان این هست که بشه در یک فاکتور سطر تکراری رو یکپارچه کرد به طوری که به تعداد اون اضافه بشه و سر حذف بشه البته لازم به ذکره که از جدول استفاده می کنم و جدول من دارای چند ستون هستش.-
پاسخ:
سلام
لطفا سوالتان را با مثال توضیح دهید.
-
-
علی
باسلام استاد عزیز یه سوال-من اگه بخوام از شیت 1 تا 30 عبارت "فعال" رو بشماره از چه عبارتی برای تابع استفاده بکنم خواهشا عین عبارت رو بنویسید وارجاع ندین به صفحه مورد نظر ممنون میشم-
پاسخ:
سلام
مراحل آن کمی طولانی است. به این سایت مراجعه کنید اگر متوجه نشدید اطلاع دهید تا برایتان توضیح دهم.
موفق باشید.
-
-
فرزو
سلام وقت بخیر
می خواستم بپرسم فرمول یا تابعی در اکسل وجود دارد که فرمان این را بدهد که اگر سطر یا یک سلول (مثلا سلول A1) در یک شیت پر است کلمه بعدی را در سلول بعدی (یعنی A2) بنویس.
با تشکر و احترام-
پاسخ:
سلام
وارد کردن اطلاعات بصورت دستی انجام می‌شود؟
-
-
سعادت
سلام خسته نباشید
فرض کنید لیستی داریم که در اون مشخصات افرادی که از فروشگاه جنسی رو خریداری میکنند ثبت کردیم حالا میخواهیم بدانیم بر فرض مثال ارسلان مجیدی که یکی از مشتریان این فروشگاه میباشد چند بار به فروشگاه مراجعه کرده و جنس تهیه کرده
لطفا در صورت امکان فرمول و توضیحات رو قرار بدید ممنون-
پاسخ:
سلام
از دستور countif استفاده کنید. مثلا=COUNTIF(A1:A10;"=ارسلان مجیدی")
موفق باشید.
-
-
farkhansh
با سلام و خسته نباشید
اگر بخواهم در یک ستون از اکسل ، به ترتیب 5 ردیف 1 زیر هم ، 5 ردیف 2 زیر هم ، 5ردیف 3 زیر هم و..... داشته باشم چگونه باید عمل کنم؟-
پاسخ:
-
-
فاطمه کریمی
بسیار متشکرم
مطالب شما کاملا واضح و کاربردیست
من مدتها بود دنبال توضیحات اینچنینی برای تکمیل کارم میگشتم اما بهترین سایتی که مشکل بنده رو حل کرد سایت شما بود
با آرزوی موفقیت -
الهه
سلام
یه سوال داشتم خواهش میکنم زود جواب بدید مهممه
فرض کنید 2 تا ستون داریم تو اولی اعداد بین 1 تا 5 هست درهم و قاطی مثلاً 4 تا 1 6 تا 2 5 تا 3 2تا4 1 دونه هم 5 بعد اینا همه قاطی پاتی زیر همن روبروشونم یه عددیه
میخوام یه فرمول باشه که مثلاً عدد روبروی همه 1 ها جمع بشه یا عدد روبروی همه 4 ها با هم جمع بشن-
پاسخ:
-
-
مهدی
سلام میخوام سلول یک عدد یک بزارم و سلول صد عدد صد و خود برنامه بقیشو پر کنه ایا راهی هست-
پاسخ:
سلام
باید از کدنویسی ماکرو استفاده کنید.
-
-
م. کفایتی
سلام میشه فرمولی را ارائه کنید که باهاش مورد زیر را پیدا کنم؟
از شیت 1 تا شیت 10 در سلول A2 چند بار عدد 1 تکرار شده؟-
پاسخ:
-
-
navid
سلام. ممنون بابت مطالبتوناگر بخوام با استفاده از تابع countif در یک ستون با اعداد تکراری، اعداد تکراری شمارش نشوند چه کار باید کنم.ممنون-
پاسخ:
-
-
علی
سلام یه تعداد نمره دارم میخوام تعداد نمرات بین 0تا 5 و یا 5 تا 10 محاسبه کنم لطف کنید راهنمایی کنید-
پاسخ:
سلام
با فرض اینکه نمرات در محدوده A1:A5 قرار دارد از فرمول زیر استفاده کنید.=COUNTIFS(A1:A5,"<=5",A1:A5,">=0")
موفق باشید.
-
-
Elahe
سلام وقت بخیر،ممنون از پیگیریتون و پاسخ سوال قبلیاگر در فایل اکسل که در هر ستون تعدادی شماره تلفن داشته باشیم و بخوایم بدونیم که شماره تلفن ها کم یا زیاد نباشد چگونه عمل میکنیمبه طور مثال شماره موبایل که 11 رقم باید باشه به اشتباه 10 یا 12 رقم وارد نشده باشدبا تشکر-
پاسخ:
-
-
Elahe
سلام، وقت بخیرمن میخوام از یک فایل اکسل که در هوستون از آن تعدادی شماره ثبت شده است، شماره های تکراری رو شمرده و یا شماره های تکراری مشخص شود.اگر امکان داره راهنمایی بفرماییدبا تشکر-
پاسخ:
سلام
برای انجام اینکار باید از ابزار فرمت دهی شرطی (Conditional Formatting) استفاده کنید که در سایت‌های زیر توضیح داده شده است.
http://farsilearn.ir/education/170
http://farsaran.blogfa.com/post/54/%D9%BE%DB%8C%D8%AF%D8%A7-%DA%A9%D8%B1%D8%AF%D9%86-%D8%AA%DA%A9%D8%B1%D8%A7%D8%B1%DB%8C%E2%80%8C%D9%87%D8%A7-%D8%AF%D8%B1-Excel-
موفق باشید.
-
-
میلاد
عالیه خیلی بدردم میخوره -
محمد
سلامبا تشکر از جواب شمابرای شمارش سطرها در حالت فیلتر دستور COUNT جواب نداد و دستور (SUBTOTAL(3,B8:B4000 جواب داد(مثلا تعداد سطر از B8 تا B4000) -
محمد
با تشکر از توضیحاتتان . اگر بخواهیم تعداد سطرهای پر یا خالی بعد از فیلتر شدن را بدست آوریم چکار می کنیم؟-
پاسخ:
سلام
پس از فیلتر کردن هم می‌توان از فرمولهای فوق استفاده کرد.
موفق باشید.
-
-
محسن
سپاسخسلی خوب توضیح داده بودین -
رزیتا
سلام ممنون اطلاعات مفیدتوناگر بخوایم مکان یه سلول خالی رو تو یه ستون مشخص کنیم چه دستوری میخواد-
پاسخ:
-
-
سعید
با سلام و تشکر از اطلاعات مفیدتون.
یه سوال: اگه بخواهیم در یک رنج مثلا A1:C50 تعداد اعداد مساوی یا بیشتر از 15 و مساوی یا کمتر از 20 را بطور همزمان در یک سلول بشماریم، تابع و فرمول را چگونه باید بنویسیم؟
متشکرم-
پاسخ:
سلام
از تابع زیر استفاده کنید:=COUNTIFS(A1:C50,">=15",A1:C50,"<=20")
موفق باشید.
-
-
ازیتا
سلام من میخواستم ببینم میشه تعداد دفعات استفاده از دکمه spaceرو در اکسل فهمید
ممنون میشم راهنماییم کنید-
پاسخ:
-
-
مریم
بسیار ممنون. هم بابت پاسخ گویی سریع و هم مطالب کاربردی .
پیروز و سربلند باشید. -
مریم
با سلام.
بابت مطالب مفید ارائه شده بسیار ممنونم.
در صورتیکه در دستور COUNFIT که فرضا برای شمارش تعداد اعداد کمتر مساوی 300 گفته شده اگر بخواهیم به جای عدد 300، مقدار یک سلول را بدهیم که دستور اعداد کمتر از آن را شمارش کند باید چگونه عمل شود.
با تشکر.-
پاسخ:
سلام
برای این حالت باید دستور را بصورت زیر بنویسید.=COUNTIF(A3:A14,"<"&C1)
بعد از درخواست شما به متن بالا اضافه شد.
موفق باشید.
-
