استخراج اعداد در محدوده

Question

رابرت یک سری اعداد در ستون A دارد که از 1 تا 100 متغیر است. او می خواهد فقط مقادیری بین 65 و 100 را استخراج کند و آنها را در ستون B قرار دهد. او نمی داند آیا راهی برای این کار به راحتی وجود دارد.

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

1. یک سلول در ستون A را انتخاب کنید. (این مهم نیست که کدام سلول را انتخاب می کنید، تا زمانی که یکی از سلول هایی باشد که دارای یک عدد است.)
2. تب Data روبان را نمایش دهید.
3. در گروه Sort & Filter بر روی ابزار Sort Smallest to Largest کلیک کنید. اکسل تمام اعداد موجود در ستون را مرتب می کند.
4. اعدادی را که می خواهید در ستون B قرار دهید انتخاب کنید.
5. Ctrl+X را فشار دهید تا سلول ها به کلیپ بورد بریده شوند.
6. سلول B1 (یا اولین سلول در ستون B که می خواهید مقادیر در آن ظاهر شوند) را انتخاب کنید.
7. Ctrl+V را فشار دهید تا سلول ها را در ستون قرار دهید.

خودشه؛ اکنون سلول های مورد نظر را در ستون B قرار داده اید. اگر به سادگی می خواهید سلول ها را کپی کنید، در مرحله 5 می توانید Ctrl+C را به جای آن فشار دهید.

اگر لازم است مقادیر ستون A را به ترتیب اصلی خود نگه دارید (منهای مقادیری که می خواهید جابه جا کنید)، می توانید با استفاده از ستون B به عنوان ستون "حفظ مکان" این کار را انجام دهید. در سمت راست اولین مقدار در ستون A، مقدار 1 را قرار دهید. سپس، در زیر آن در ستون B یک عدد 2، سپس یک 3، و به همین ترتیب قرار دهید، تا زمانی که هر مقدار در ستون A یک مقدار متناظر در ستون B داشته باشد که نشان می دهد محل اعداد سپس، این مراحل را دنبال کنید:

1. یک سلول در ستون A را انتخاب کنید. (این مهم نیست که کدام سلول را انتخاب می کنید، تا زمانی که یکی از سلول هایی باشد که دارای یک عدد است.)
2. تب Data روبان را نمایش دهید.
3. در گروه Sort & Filter بر روی ابزار Sort Smallest to Largest کلیک کنید. اکسل تمام اعداد موجود در ستون را مرتب می کند.
4. اعداد ستون A را که می خواهید جابجا کنید، به همراه اعداد سمت راست آنها در ستون B انتخاب کنید.
5. Ctrl+X را فشار دهید تا سلول ها به کلیپ بورد بریده شوند.
6. سلول D1 را انتخاب کنید. (انتخاب سلول D1 مهم است زیرا باید ستون C را خالی بگذارید.)
7. Ctrl+V را فشار دهید تا سلول ها را در ستون های D و E قرار دهید.
8. یک سلول در ستون B را انتخاب کنید.
9. تب Data روبان را نمایش دهید.
10. در گروه Sort & Filter بر روی ابزار Sort Smallest to Largest کلیک کنید. اکسل تمام اعداد ستون را بر اساس مقادیر ستون B مرتب می کند.
11. یک سلول در ستون E انتخاب کنید.
12. تب Data روبان همچنان باید نمایش داده شود.
13. در گروه Sort & Filter بر روی ابزار Sort Smallest to Largest کلیک کنید. اکسل تمام اعداد ستون را بر اساس مقادیر ستون E مرتب می کند.
14. ستون های B، C و E را حذف کنید.

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

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

شکل 1. کادر محاوره ای Custom AutoFilter.

1. یک سلول در ستون A را انتخاب کنید. (این مهم نیست که کدام سلول را انتخاب می کنید، تا زمانی که یکی از سلول هایی باشد که دارای یک عدد است.)
2. تب Data روبان را نمایش دهید.
3. روی ابزار Filter کلیک کنید. اکسل یک فلش کشویی فیلتر را در سمت راست هدر ستون A اضافه می کند.
4. روی فلش رو به پایین کلیک کنید و Number Filters | را انتخاب کنید بین. اکسل کادر محاوره ای Custom AutoFilter را نمایش می دهد. (شکل 1 را ببینید.)
5. در کادر بزرگتر یا برابر، عدد 65 را وارد کنید.
6. در کادر کمتر یا برابر، عدد 100 را وارد کنید.
7. روی OK کلیک کنید. اکسل آنچه را که نشان داده می شود فقط به ردیف هایی محدود می کند که معیارهایی را که در مراحل 4 تا 6 مشخص کرده اید برآورده می کنند.
8. سلول های نمایش داده شده را انتخاب کنید.
9. Ctrl+C را فشار دهید تا سلول ها را در کلیپ بورد کپی کنید.
10. سلول B1 (یا اولین سلول در ستون B که می خواهید مقادیر در آن ظاهر شوند) را انتخاب کنید.
11. Ctrl+V را فشار دهید تا سلول ها را در ستون قرار دهید.
12. یکی از سلول های ستون A را انتخاب کنید.
13. دوباره روی ابزار Filter کلیک کنید. (زبانه Data روبان همچنان باید نمایش داده شود.) اکسل فیلتری را که قبلا اعمال کرده اید حذف می کند.

همچنین می توانید از فرمول های ستون B برای بیرون آوردن مقادیری که در محدوده مورد نظر هستند استفاده کنید. یک راه آسان برای انجام این کار قرار دادن این فرمول در سلول B1 است:

=IF(AND(A1>=65, A1

فرمول را تا جایی که لازم است در ستون B کپی کنید و در نهایت با هر مقداری در محدوده 65 تا 100 مواجه می شوید که شامل "کپی" در ستون B می شود. اگر مقدار خارج از این محدوده باشد، سلول در ستون B خالی مانده است

با فرض اینکه هیچ سلول خالی در ستون B نمی خواهید، می توانید از یک فرمول آرایه برای گرفتن مقادیر استفاده کنید. اگر مقادیر شما در محدوده A1:A500 هستند، موارد زیر را در سلول B1 قرار دهید:

=IFERROR(INDEX(A$1:A$500,SMALL(IF(A$1:A$500>=65,ROW($1:$500)),ROW())),"")

آن را با استفاده از Ctrl+Shift+Enter وارد کنید و سپس فرمول را تا جایی که دوست دارید کپی کنید.

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

Sub ExtractValues1()
    Dim x As Integer

    x = 1
    For Each cell In Selection
        If cell.Value >= 65 And cell.Value 

شما با انتخاب سلول های مورد نظر برای ارزیابی در ستون A و سپس اجرای آن از ماکرو استفاده می کنید. به هر سلول نگاه می کند و مقدار را در ستون B کپی می کند. مقدار اصلی در ستون A بدون تغییر باقی می ماند.

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

Sub ExtractValues2()
    Dim iLowVal As Integer
    Dim iHighVal As Integer

    iLowVal = InputBox("Lowest value wanted?")
    iHighVal = InputBox("Highest value wanted?")

    For Each cell In Range("A:A")
        If cell.Value <= iHighVal And cell.Value >= iLowVal Then
            ActiveCell.Value = cell.Value
            ActiveCell.Offset(1, 0).Activate
        End If
    Next
End Sub

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