آخرین سلول پر یا سطر پر در یک جدول اکسل را چگونه پیدا کنیم؟
پیدا کردن آخرین سلول پر در اکسل یا سلول خالی در اکسل زمانی که بخواهیم یکسری اطلاعات را بصورت ترتیبی در یک جدول ثبت کنیم اهمیت ویژه ای پیدا می کند.برای مثال فرض کنید شما یک جدولی از واریزی های مشتریان خود دارید.اکنون می خواهید اطلاعات مربوط به آخرین تراکنش ها را استخراج کرده و یکسری عملیات روی این داده ها انجام دهید.برای این کار جدولی مانند جدول زیر را در نظر بگیرید:
از این جدول چه اطلاعاتی میخواهیم استخراج کنیم:
- آخرین مبلغ واریزی در چه تاریخی بوده است…؟
- شماره ردیف آخرین سلول پر کدام ردیف است…؟
- آدرس آخرین سلول خالی چیست…؟
اما قبل از شروع آموزش بهتر است با توابع مورد استفاده در این آموزش آشنا شده و کاربرد هرکدام را خوب بشناسید.
- تابع COUNTA : این تابع تعداد سلول های پر(چه عددی چه غیرعددی) در محدوده ای که ما براش تعریف می کنیم رو میشماره.
- تابع OFFSET : این تابع از یک سلول مبنا که ما تعریف میکنیم به تعداد سطر و ستون های تعریف شده، از سلول مبنا جابجا میشه
- تابع ROW: شماره ردیف یک سلول رو نشون میده.
- تابع ADDRESS : آدرس یک سلول یا محدوده رو نشون میده.
برای آشنایی بیشتر و مشاهده آموزش کامل ویدئویی هریک از توابع فوق،بعد از عضویت رایگان در وبسایت می توانید با ثبت نام در دوره بصورت رایگان به ویدئوها دسترسی داشته باشید.
آخرین مبلغ واریزی در چه تاریخی بوده است…؟
ابتدا من باید ببینم چه تعداد تراکنش در جدول ثبت شده است.برای این کار با استفاده تابع COUNTA تعداد تراکنش هارو میشمارم.دقت کنید برای این کار حتما ستونی رو به عنوان مبنای کاریتون انتخاب کنید که بین اطلاعات سلول خالی وجود نداشته باشد.که ما اینجا ستون تاریخ رو به عنوان مبنای کارم درنظر می گیرم.پس تابع رو به شکل زیر می نویسم:
(COUNTA(A:A=
نتیجه این تابع عدد ۵ خواهد بود.زیرا در ستون A ما ۵ سلول پر داریم.حال در ادامه با ترکیب این تابع با تابع OFFSET محتوای داخلی آخرین سلول پر را استخراج می کنیم.
برای استخراج مقدار آخرین سلول پر در ستون A این دو تابع را به شکل زیر ترکیب میکنیم:
(OFFSET($A$1,COUNTA(A:A)-1,0=
فرمول بالا دقیقا چکار میکند:
در فرمول بالا ما سلول A1 را بعنوان سلول مبنا به تابع OFFSET تعریف کردیم و گفتیم به تعداد نتیجه تابع (COUNTA(A:A که ۵ هست، در ستون A از سلول مبنا جابجا شود.یعنی از سلول A1 تعداد ۵ سلول بیا پایین.عدد ۰ هم در تابع به این معناست که در ستون هیچ جابجایی نداشته باشد.
اما علت اینکه ما نتیجه تابع (COUNTA(A:A را منهای ۱ کردیم این است که چون مبنای شمارش و جابجایی تابع OFFSET از صفر می باشد، برای جبران این اختلاف یک واحدی آنرا از نتیجه تابع COUNTA کم می کنیم.
شما همچنین می توانید تابع رو به شکل زیر هم استفاده کنید:
(OFFSET($A$1,COUNTA($A$2:$A$1000),0=
در فرمول بالا مقدار A1000 یک مقدار پیش بینی شده است که می توانید این آدرس را بزرگتر مثلا A1000000 و… درنظر بگیرید.که نتیجه هردو تابع فوق تاریخ ۱۳۹۷/۰۱/۱۷ خواهد بود.
شماره ردیف آخرین سلول پر کدام ردیف است…؟
ما با استفاده از تابع OFFSET محتوای یک سلول را استخراج کردیم.حال اگر بخواهیم شماره ردیف آن سلول را استخراج کنیم کافیه تابع فوق را داخل تایع ROW قرار دهیم تا بجای محتوای سلول آخر، شماره ریف اون سلول رو برای ما نشون بده.
((ROW(OFFSET($A$1,COUNTA(A:A)-1,0=
نتیجه تابع فوق عدد ۵ خواهد بود.زیرا آخرین سلول پر در ستون A، در ردیف ۵ شیت قرار دارد.
آدرس آخرین سلول خالی چیست…؟
برای بدست آوردن آدرس آخرین سلول پر می توانیم از تابع ADDRESS استفاده کنیم.خاصیتی که این تابع دارد این است که با دادن شماره ردیف و ستون یک سلول آدرس اون سلول رو برای ما نشان می دهد.
ما که شماره ردیف آخرین سلول پر در اکسل رو قبلا با تابع ((ROW(OFFSET($A$1,COUNTA(A:A)-1,0= بدست آوردیم که ۵ بود.شماره ستون هم که مشخصه.چون میخواهیم در ستون A آدرس یک سلول رو بدست بیاوریم و چون ستون A اولین ستون شیت می باشد پس شماره ستون عدد ۱ خواهد بود.
فرمول نهایی به شکل زیر خواهد بود:
(ADDRESS(ROW(OFFSET($A$1,COUNTA(A:A)-1,0)),1=
اگر بخواهیم فرمول بالا رو ساده کنیم به شکل زیر خواهد بود:
(ADDRESS(5,1=
فرمول بالا میگه آدرس سلولی که شماره ردیفش ۵ باشه و شماره ستونش ۱ باشه رو نشون بده که نتیجه A5 خواهد بود.
نکته:
دقت داشته باشید که ما اینجا ستون A را مبنای آدرس دهی تعیین کردیم.شما می توانید هرستونی که میخواهید را بعنوان ستون مبنا درنظر بگیرید.مثلا بجای ستون A ستون C را درنظر بگیرید.
درباره جاوید
عاشق اکسل هستم و بیش از 12 ساله دارم اکسل کار می کنم. در این مسیر آموزش ها و فایل های حرفه ای ارائه دادم و همچنان در این مسیر با علاقه پیش میرم چون امروزه اکسل به یک مهارت انکار نشدنی تبدیل شده
سایر نوشته های جاویدمطالب زیر را حتما مطالعه کنید
۴ گام تا شماره گذاری صفحه چاپ در اکسل با یک نکته عالی
ایجاد لیست شیت ها در ComboBox اکسل با دستورات VBA
در کمتر از ۱ دقیقه چک لیست کارهای روزانه در اکسل بساز
فایل اکسل محاسبه مالیات حقوق سال ۱۴۰۱ + آموزش ویدئویی فرمولنویسی مالیات
راست چین در اکسل و نحوه راست به چپ کردن شیت اکسل
برای چاپ اطلاعات داخل فرم از قبل پرینت شده در اکسل چکار کنیم؟
14 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام
برای رفتن میانبر به آخرین دیف پر شده در اکسل از کدام کلید توابع استفاده کنم؟
متشکرم
سلام و عرض ادب
وقت بخیر و خسته نباشید
یک سوال داشتم
من تو اکسل یک جدولی ساختم
کاری که می خوام انجام بدم اینکه با استفاده از vba و یوزر فرم اکسل یتونم که اطلاعات داخل جدول رو در یک کمبو باکس داشته باشم و هز لحظه این اطلاعات به طور اتوماتیک داخل کمبود باکس آپدیت بشه
نکته دوم اینکه
می خوام با ا استفاده از این فرم بتونم که به اخریم اطلاعات داخل جدول دسترسی داشته باشم و بتونم که حذف کنم
مثلا اگر جدولم شامل اطلاعات زیر باشه
ردیف …… نام و نام خانوادگی
۱ ۲
۲ ۶
می خوام وقتی که روی دکمه حذف کلیک شد
اولا یک پیام بده که آیا می خواهید ردیف ۲ را حذف کنید یا خیر اگر روی اوکی کلیکک شد ردیف ۲ که اخرین ردیف جدله از داخل جدول حذف بشه و پیغام بده که از اطلاعات حذف شد
نکته بعدی اینکه اگر هیچ اطلاعاتی در جدول وجود نداشته باشه پیغام بده که اطلاعاتیبرای حذف در جدول نیست
ممنونم میشم راهنمایی کنید که دستورات اینو بتویسیم
سلام. وقت بخیر
پاسخ به این سوال شما بحث مفصلی لازم داره که در قالب کامنت ممکن نیست.
ولی دوره آموزش طراحی دفترچه تلفن در اکسل میتونه کمک زیادی به شما کنه
سلام
اگه بخواهیم موقع اجرای اکسل کد زیر خودبخود اجرابشه باید چکار کنیم؟
=OFFSET($A$1؛COUNTA(A:A)-1؛۰)
سلام. وقت بخیر
باید همین فرمول رو در محیط VBA پیاده کنید.
سلام ، برای پیدا کردن آخرین ستون پر در یک ردیف مشخص ، این ترکیب به چه صورت باید تغییر یابد ؟
سلام اگه اخرین سلول پر شده از دیتا در یک سطر بخوام پیدا کنم از چه فرمولی استفاده کنم . دیتای توی سلول ها سطر پیوسته نیست و با فاصله هستند. مرسی
سلام.
با تابع COUNTBLANK تعداد سلول های خالی رو بشمارید و با تابع CountA تعداد سلول های پر شده رو
حاصل جمع این دو تابع میشه ردیف آخرین سلول پر
سلام وقت به خیر . ستونی دراکسل دارم که فرموله هست و برخی از سلولهای ان به طبع فرمول صفرند ولی من می خواهم آخرین اطلاعات غیر صفر انرا درسلولی دیگر داشته باشم. از چه دستوری باید استفاده کنم. با تشکر
سلام. وقت بخیر
برای نمونه میتونید از کد زیر استفاده کنید
=INDEX(A1:A11,COUNTIF(A1:A11,"<>۰"))
با سلام و درود و عرض ارات و خسته نباشید، چگونه میتوانیم از جدولی که در برخی از فیلدها دارای اطلاعات پرتکرار میباشد گزارشی از آخرین تاریخ ردیفها
تهیه نماییم!؟
سلام. وقت بخیر
اینکار نیاز به فرمولنویسی آرایه ای دارد.
به نام خدا
با سلام. یک جدول اکسل داریم که از ستون a تا d آن مورد استفاده قرار گرفته است. چگون بوسیله کد بفهمیم که جدول ما تا ستون d مورد استفاده قرار گرفته و بقیه اش خالی است؟ یعنی این d چطور بدست می آید؟”
سلام. وقت بخیر
برای بدست آوردن شماره ستون آخرین محدوده استفاده شده میتونید ار کد زیر استفاده کنید
ActiveCell.SpecialCells(xlLastCell).Column