وبلاگ

آخرین سلول پر یا سطر پر در یک جدول اکسل را چگونه پیدا کنیم؟

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

جدول واریزی ها در اکسل
شکل شماره 1 – جدول مربوط به تراکنش های مالی در اکسل

از این جدول چه اطلاعاتی میخواهیم استخراج کنیم:

  1. آخرین مبلغ واریزی در چه تاریخی بوده است…؟
  2. شماره ردیف آخرین سلول پر کدام ردیف است…؟
  3. آدرس آخرین سلول خالی چیست…؟
اما قبل از شروع آموزش بهتر است با توابع مورد استفاده در این آموزش آشنا شده و کاربرد هرکدام را خوب بشناسید.
  1. تابع COUNTA : این تابع تعداد سلول های پر(چه عددی چه غیرعددی) در محدوده ای که ما براش تعریف می کنیم رو میشماره.
  2. تابع OFFSET : این تابع از یک سلول مبنا که ما تعریف میکنیم به تعداد سطر و ستون های تعریف شده، از سلول مبنا جابجا میشه
  3. تابع ROW: شماره ردیف یک سلول رو نشون میده.
  4. تابع ADDRESS : آدرس یک سلول یا محدوده رو نشون میده.

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

آخرین مبلغ واریزی در چه تاریخی بوده است…؟

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

=COUNTA(A:A)

نتیجه این تابع عدد 5 خواهد بود.زیرا در ستون A ما 5 سلول پر داریم.حال در ادامه با ترکیب این تابع با تابع OFFSET محتوای داخلی آخرین سلول پر را استخراج می کنیم.

برای استخراج مقدار آخرین سلول پر در ستون A این دو تابع را به شکل زیر ترکیب میکنیم:

=OFFSET($A$1,COUNTA(A:A)-1,0)

فرمول بالا دقیقا چکار میکند:

در فرمول بالا ما سلول A1 را بعنوان سلول مبنا به تابع OFFSET تعریف کردیم و گفتیم به تعداد نتیجه تابع (COUNTA(A:A که 5 هست، در ستون A از سلول مبنا جابجا شود.یعنی از سلول A1 تعداد 5 سلول بیا پایین.عدد 0 هم در تابع به این معناست که در ستون هیچ جابجایی نداشته باشد.

اما علت اینکه ما نتیجه تابع (COUNTA(A:A را منهای 1 کردیم این است که چون مبنای شمارش و جابجایی تابع OFFSET از صفر می باشد، برای جبران این اختلاف یک واحدی آنرا از نتیجه تابع COUNTA کم می کنیم.

تابع OFFSET و تابع COUNTA برای پیدا کردن آخرین سلول پر و خالی
شکل شماره 2 – پیدا کردن تاریخ آخرین مبلغ واریزی

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

=OFFSET($A$1,COUNTA($A$2:$A$1000),0)

در فرمول بالا مقدار A1000 یک مقدار پیش بینی شده است که می توانید این آدرس را بزرگتر مثلا A1000000 و… درنظر بگیرید.که نتیجه هردو تابع فوق تاریخ 1397/01/17 خواهد بود.

شماره ردیف آخرین سلول پر کدام ردیف است…؟

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

=(ROW(OFFSET($A$1,COUNTA(A:A)-1,0)

نتیجه تابع فوق عدد 5 خواهد بود.زیرا آخرین سلول پر در ستون A، در ردیف 5 شیت قرار دارد.

آدرس آخرین سلول خالی چیست…؟

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

ما که شماره ردیف آخرین سلول پر در اکسل رو قبلا با تابع ((ROW(OFFSET($A$1,COUNTA(A:A)-1,0= بدست آوردیم که 5 بود.شماره ستون هم که مشخصه.چون میخواهیم در ستون A آدرس یک سلول رو بدست بیاوریم و چون ستون A اولین ستون شیت می باشد پس شماره ستون عدد 1 خواهد بود.

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

=ADDRESS(ROW(OFFSET($A$1,COUNTA(A:A)-1,0)),1)

اگر بخواهیم فرمول بالا رو ساده کنیم به شکل زیر خواهد بود:

=ADDRESS(5,1)

فرمول بالا میگه آدرس سلولی که شماره ردیفش 5 باشه و شماره ستونش 1 باشه رو نشون بده که نتیجه A5 خواهد بود.

پیدا کردن اطلاعات آخرین سلول در اکسل
شکل شماره 3 – نتیجه نهایی اطلاعات آخرین سلول پر

نکته:

دقت داشته باشید که ما اینجا ستون A را مبنای آدرس دهی تعیین کردیم.شما می توانید هرستونی که میخواهید را بعنوان ستون مبنا درنظر بگیرید.مثلا بجای ستون A ستون C را درنظر بگیرید.
اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

53 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • سلام.خسته نباشید.ممنون بابته آموزشتون.من همین فرمول وارد کردم.اما تو نتیجه تابع صفر مینه!!!!
    علت چیه؟

  • با عرض سلام و خسته نباشید به فرض هربار یک عددبین سلولهای b1تاb10 وارد می کنیم و میخواهیم همیشه آخرین عدد وارد شده در سلول َa1درج گردد روش کار چطوره

    • سلام.

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

  • سلام برای پیدا کردن آخرین ستون بکار گیری شده اگر بخواهیم از بین چند نفر مشتری آخرین واریزی یک مشتری خاص را بررسی کنیم از چه فرمولی باید استفاده کرد

    • سلام.

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

  • سلام من میخواهم دیتا در یک شیت از خونه مثلا g3 تا g7 به ترتیب زیر هم ثبت بشوند و درصورت ورود داده ششم پیغام داده شود و ثبت نگردد.

  • سلام من میخوستم بوسیله تابع ()sum چند ستون جداگانه رو جمع بزنم و در عین حال اگر در آخرین سطر هر ستون داده ای اضافه شد به مقدار جمع اضافه شود.
    چگونه باید تابع رو بنویسم؟

    • سلام. وقت بخیر

      بهترین و راحت ترین گزینه استفاده از ابزار Table هست.

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

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

        • سلام. وقت بخیر
          متاسفانه ابزار Table یکسری ایراداتی داره که مایکروسافت باید برطرفش کنه.

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

          شما میتونید با استفاده از توابع جستجو و ابزار Name Manager هم اینکار رو انجام بدین اما ساده ترین روش اینه :

          1- محدوده خودتون رو به Table تبدیل کنید.
          2- ستون موردنظر داخل تیبل (بدون انتخاب عنوان ستون) رو انتخاب کنید.
          3- در قسمت Name Box (گوشه سمت چپ – بالای شیت) نام محدوده رو تایپ کنید و Enter رو بزنید. مثلا mylist
          4- حال به Data Validation برید و در قسمت Source مقدار mylist= را بنویسید و OK کنید

  • Sub copy()

    x = Sheets(“Line3”).Range(“R1”).Value
    Sheets(“Line3”).Range(“L1:Q1”).copy
    Workbooks.Open “D:\Data.xlsx”, Activate
    Sheets(“sheet3”).Cells(x, 1).PasteSpecial xlPasteValues
    ActiveWorkbook.save
    ActiveWindow.Close
    End Sub
    سلام وقت بخیر خسته نباشید ماکد ماکرویی میخواییم وقتی که دردیتا رنج هاروpast میکنیم بیاد تک به تک سلول هاروبررسی کنه اگر حتی یکی خالی باشد بگه فرم خالی است ممنوع میشم راهنمایی کنید

    • سلام.

      در یکی از سلو ها با تابع CountA محدوده L1:Q1 و بشمارید و با استفاده از دستور IF بررسی کنید اگر مقدار CountA مساوی 6 بود دستورها اجرا بشه.
      برای مثال:

      If WorksheetFunction.CountA(Range(“L1:Q1”)) = 6 Then
      ‘your Actions
      End If

  • Sub copy()
    x = ThisWorkbook.Sheets(“Line1”).Range(“k1”).Value
    Workbooks.Open “D:\Production Raw-Data.xlsx”, Activate
    ThisWorkbook.Sheets(“Line1”).Range(“L1:BF1”).copy
    Sheets(“Data”).Cells(x, 1).PasteSpecial xlPasteValues
    ThisWorkbook.Sheets(“Line2”).Range(“L1:AW1”).copy

    Sheets(“Data”).Cells(x, 48).PasteSpecial xlPasteValues

    ThisWorkbook.Sheets(“Line3”).Range(“L1:p1”).copy

    Sheets(“Data”).Cells(x, 86).PasteSpecial xlPasteValues

    ActiveWorkbook.Close savechanges:=True
    END SUB
    سلام وقت بخیر خسته نباشید ما دراین ماکرو K1برای هر سه شیت قراردادیم که ردیف به ردیف کپی میکنه ولی برای هر سه شیت یک دکمه ثبت قرار دادیم اینجا میخواییم وقتی صفحه هاروپرمیکنیم اگه حتی یک صفحه خالی باشه هیچکدوم ثبت نکنه بگه محدوده خالیه وهرکدوم ازرنج ها که مقدارصفربود ثبت کنه ولی خالی باشه ثبت نکنه

    • منظورتون از “صفحه خالی” رو متوجه نمیشم.فایلتون رو برای ما ایمیل کنید تا بررسی کنیم.

      • Sub copy()
        x = ThisWorkbook.Sheets(“Line1”).Range(“k1”).Value
        Workbooks.Open “D:\Production Raw-Data.xlsx”, Activate
        ThisWorkbook.Sheets(“Line1”).Range(“L1:BF1”).copy
        Sheets(“Data”).Cells(x, 1).PasteSpecial xlPasteValues
        ThisWorkbook.Sheets(“Line2”).Range(“L1:AW1”).copy

        Sheets(“Data”).Cells(x, 48).PasteSpecial xlPasteValues

        ThisWorkbook.Sheets(“Line3”).Range(“L1:p1”).copy

        Sheets(“Data”).Cells(x, 86).PasteSpecial xlPasteValues

        ActiveWorkbook.Close savechanges:=True
        END SUB
        این هم ماکرو
        همون رنج هاست که دادیمL1:BF1و L1:AW1و L1:p1 توماکرو نگاه کنید میخواییم اینارو کپی کنیم وpastکنیم بایک دکمه تودیتاحالا میخواییم همه رنج ها پر باشد اگه نباشه بگه محدوده خالیه صفر هم باشه کپی کنه ولی صفر نباشه هیچی نباشه کپی نکنه

        • بنده چند کامنت بالاتر پاسختون رو دادم.گفتم از تابع CountA استفاده کنید

          If WorksheetFunction.CountA(Range(“L1:Q1”)) = 6 Then
          ‘your Actions
          End If

  • Sub past()
    x = Sheets(“line1”).Range(“m1”).Value
    Range(“A6:G6″).Select
    Selection.COPY
    Workbooks.Open Filename:=”D:\k1.xlsm”
    Sheets(“sheet1”).Cells(x, 2).PasteSpecial xlPasteValues
    Range(“A5:A5”).End(xlDown).Offset(1, 0).Value = Range(“A5:A5”).End(xlDown).Value + 1
    ActiveWorkbook.Save
    ActiveWindow.Close
    Range(“a6:G6”).ClearContents
    End Sub
    سلام وقت بخیر خسته نباشید دراین ماکرو میخواییم باهر بار زدن دکمه ثبت شماره ردیف خودکار ایجاد بشه این مشکلش چیه که درست عمل نمیکنه باتشکر

    • سلام. وقت بخیر

      خود این تابع IF که نوشتید اشتباهه.

      ولی ساختارش به شکل زیر هست :

      IF Range(“B60”)=10 then
      Range(“B60”)=ActiveCell.Row
      Else
      “”=(“Range(“B60
      end if

  • =if(b60;row()_5;””سلام اره درسته اشتباه شده منظورم این فرموله که سلول روبرو بررسی میکنه پرباشه
    شماره میزاره

  • سلام وقت بخیر ماررنج های پراکنده ای داریم مثلا”
    Range(“i5:i8,n5:m8,u5:u8,aj5,ap5,as8,ay8”).ClearContents این کد چطوری میتونیم بنویسیم خطانده وپاک کنه

    • سلام. وقت بخیر

      این کد هیچ مشکلی ندارد و درست می باشد.

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

  • اگر بخواهیم آخرین داده وارد شده در یک ردیف را مشخص کنیم از چه تابعی باید استفاده کنیم؟

    • سلام. وقت بخیر.

      راه زیاده.

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

  • سلام وقت بخیر این کد
    Range( _
    “BL1,B5:C10,I5:L7,I8:L10,R5:U7,U8:AA9,U10:AA10,Y5:AA7,AJ5:AL7,AJ8:AL10,AP8:AR10,AP5:AR7,AS8:AY10,AZ8:BG10” _چطوری میتونیم با ارایه بنویسیم

    • سلام.

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

  • سلام وقت بخیر خسته نباشید دوتا شیت درست کرده ایم بانام a,b کدی نوشتیم که باانتخاب نام شیت اطلاعات یک رنج مشاهده میکنیم.حالا میخواییم طوری بنویسیم که با انتخاب نام رنج مثلا محدوده a1:a12نشان بده
    Private Sub Range_Change(ByVal Target As Range)
    If Target.Address = “$A$1” Then
    sheets( Sheets(1).Range(“A1”).Value).Range(“a:a”).Copy Sheets(1).Range(“B:B”)
    End If
    End Sub

  • سلام وقت بخیر خسته نباشید نحوه به کار بردن فرمول هرچقدر رکورد ماکرو میکنم متوجه نمیشم راهنمایی فرمایید باتشکر
    FormulaR1C1vba

    • سلام. وقت بخیر.

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

  • سلام وقت بخیر خسته نباشید دراین کد ما فایل را براساس تاریخ ذخیره مکنیم که داخل b5 است حالا میخواییم طوری باشه که کنار تاریخ یه شماره هم ایجادبشه مثلا 11-991020و991021-12 تشکر میکنم راهنمایی کنید.
    x = [b5]
    Selection.copy
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    “y:\Daily production\save rgb\” & x & “pdf”
    ActiveWorkbook.SaveAs Filename:=”y:\Daily production\save rgb\” & x & “”

    • سلام. وقت بخیر

      یکی از راهها این هست که در یک سلول(مثلا C5 ) شماره خودتون رو با توابع موردنیاز بسازید و بجای X آدرس سلول رو در کدتون بنویسید. مثلا (“Range(“C5

  • با سلام
    اگر جدولمون رو فیلتر کنیم دستور پیدا کردن آخرین سطر پر را چطوری باید پیدا کنیم ؟

    • در حالت فیلتر شما نباید از تابع COUNTA خالی استفاده کنید.با استفاده از تابع SUBTOTAL که یکی از توابع داخلش همون تابع COUNTA هست، میتونید عمل شمارش رو انجام دهید.چون این تابع به فیلتر کردن حساس می باشد.بقیه ش هم مثل توضیحات داخل مطلب جلو برید.

  • سلام وقت بخیر ما یه شرطی تعریفی کردیم که اگر نامساوی صفرهست ثبت کند دراکسل هم سل های خالی صفر شناخته میشه حال مااون چیزی که میخواییم ثبت بشه هم عدد توش هست هم حروف حال مجوربیم اون چیزی که وجود نداراه درسل * بزاریم که صفرنشناسه حالا یه فرمول از این بهتر چی میشه نوشت .مافرمول بزرگ ازصفرنوشتیم سل های خالی ثبت میکنه
    COUNTIF(BS1:CQ1;”>0″)
    COUNTIF(BS1:CQ1;”0″)

    • سلام. وقت بخیر

      باید از تابع AND استفاده بشه.شما با استفاده از این تابع شرط دوم نیز تعریف می کنی که اگر مقدار سلول خالی هم بود ثبت نکنه.

  • سلام استاد خسته نباشید درمورد کپی کردن رنج های پراکنده مالتی سلکت مثلا 40تارنج یه آموزشی بذارید.باتشکر

  • ) Range
    “B5:C10,I5:K7,N5:P7,U5:AA7,AI5:AL7,AP5:AR7,AS8:AY10,AO8:AR10,AF8:AJ10,U8:AA10,I8:L10,C33:E34,F33:K34,L33:P34,Q33:U34,V33:AB34,AC33:AJ34,AK33:AP34,AQ33:AV34” _
    )سلام مثلا چطوری این رنج هاروکپی کنیم

    • سلام. وقت بخیر

      متاسفانه اینمورد یکی از محدودیت های اکسل می باشد که نمی توان محدوده های غیرپیوسته را بصورت مستقیم کپی-پیست کرد.برای همین باید از یکسری ترفند ها استفاده کرد.یکی از این ترفند ها استفاده از ابزار Clipboard می باشد.یعنی شما تک تک محدوده هارو کپی کنید سپس از طریق کلیپبورد آنهارو Paste All کنید یا همینکار رو با کدنویسی انجام دهید. و یا از افزونه هایی مثل kutools استفاده کنید.

  • سلام استاد وقت بخیر باکلیبرد مرج سل نمیشه میخواام همش یک جا کپی کنم ببرم جایی دیگه تویک سطرکپی کنم میتونین با کدنویسی مثال بزنید.تشکر

  • سلام این توابع ای که گفتین رو چگونه داخل برنامه نویسی vbaاستفاده کنم اونجا نمیاره ولی در داخل اکس با = میاره

    • سلام.وقت بخیر

      برای استفاده از توابع اکسل در VBA برای مثال از کد زیر استفاده کنید:


      WorksheetFunction.Sum(Range("A1:A15"))

  • من يه فرمولي ميخوام كه هميشه آخرين عدد ثبت شده در آخرين سلول يه ستون رو در يك سلول خاص نمايش بده مثلا در ستون A1 آخرين عدد كه در ستون B نمايش داده ميشه رو به ما نشون بده

  • سلام و وقت بخیر چطور میتونم آخرین سلول نوشته شده در یک ردیف و ستون خاص رو نمایش بدم؟

دیدگاهتان را بنویسید