როგორ გამოვიყენოთ დინამიური დიაპაზონი Excel-ში COUNTIF და INDIRECT-ით

2024 ავტორი: Abigail Brown | [email protected]. ბოლოს შეცვლილი: 2023-12-17 06:52

რა უნდა იცოდე

• INDIRECT ფუნქცია ცვლის უჯრედების მითითებების დიაპაზონს ფორმულაში ფორმულის რედაქტირების გარეშე.
• გამოიყენეთ INDIRECT, როგორც არგუმენტი COUNTIF-ისთვის, რათა შექმნათ უჯრედების დინამიური დიაპაზონი, რომელიც აკმაყოფილებს მითითებულ კრიტერიუმებს.
• კრიტერიუმები დადგენილია INDIRECT ფუნქციით და დაითვლება მხოლოდ უჯრედები, რომლებიც აკმაყოფილებენ კრიტერიუმებს.

ეს სტატია განმარტავს, თუ როგორ გამოვიყენოთ INDIRECT ფუნქცია Excel-ის ფორმულებში, რათა შეცვალოთ ფორმულაში გამოყენებული უჯრედების მიმართვების დიაპაზონი, თავად ფორმულის რედაქტირების გარეშე. ეს უზრუნველყოფს იმავე უჯრედების გამოყენებას, მაშინაც კი, როდესაც თქვენი ცხრილი იცვლება.ინფორმაცია ვრცელდება Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel Mac-ისთვის და Excel Online.

გამოიყენეთ დინამიური დიაპაზონი COUNTIF - INDIRECT ფორმულა

INDIRECT ფუნქცია შეიძლება გამოყენებულ იქნას რამდენიმე ფუნქციით, რომლებიც მიიღებენ უჯრედის მითითებას არგუმენტად, როგორიცაა SUM და COUNTIF ფუნქციები.

INDIRECT-ის გამოყენება არგუმენტად COUNTIF-ისთვის ქმნის უჯრედების მითითებების დინამიურ დიაპაზონს, რომელიც შეიძლება დაითვალოს ფუნქციით, თუ უჯრედის მნიშვნელობები აკმაყოფილებს კრიტერიუმებს. ის ამას აკეთებს ტექსტის მონაცემების გადაქცევით, რომელსაც ზოგჯერ ტექსტის სტრიქონს უწოდებენ, უჯრედის მითითებად.

ეს მაგალითი ეფუძნება ზემოთ მოცემულ სურათზე ნაჩვენები მონაცემებს. სახელმძღვანელოში შექმნილი COUNTIF - INDIRECT ფორმულა არის:

=COUNTIF(INDIRECT(E1&":"&E2), ">10")

ამ ფორმულაში, INDIRECT ფუნქციის არგუმენტი შეიცავს:

• უჯრედის მითითებები E1 და E2, რომლებიც შეიცავს ტექსტურ მონაცემებს D1 და D6.
• დიაპაზონის ოპერატორი, ორწერტილი (:) გარშემორტყმული ორმაგი ბრჭყალებით (" "), რომელიც აქცევს ორწერტილს ტექსტად სტრიქონი.
• ორი ამპერსანდი (&), რომლებიც გამოიყენება მსხვილი ნაწლავის შესაერთებლად ან ერთმანეთთან შესაერთებლად უჯრედების E1 და E2 მითითებით.

შედეგი არის ის, რომ INDIRECT გარდაქმნის ტექსტის სტრიქონს D1:D6 უჯრედის მითითებად და გადასცემს მას COUNTIF ფუნქციას დასათვლელად, თუ მითითებული უჯრედები 10-ზე მეტია.

INDIRECT ფუნქცია იღებს ნებისმიერ ტექსტურ შეყვანას. ეს შეიძლება იყოს სამუშაო ფურცლის უჯრედები, რომლებიც შეიცავენ ტექსტს ან ტექსტურ უჯრედის მითითებებს, რომლებიც შეყვანილია პირდაპირ ფუნქციაში.

დინამიურად შეცვალეთ ფორმულის დიაპაზონი

გახსოვდეთ, მიზანია შექმნათ ფორმულა დინამიური დიაპაზონით. დინამიური დიაპაზონი შეიძლება შეიცვალოს თავად ფორმულის რედაქტირების გარეშე.

E1 და E2 უჯრედებში განთავსებული ტექსტური მონაცემების შეცვლით, D1 და D6-დან D3 და D7-მდე, ფუნქციის ჯამური დიაპაზონი მარტივად შეიძლება შეიცვალოს D1:D6-დან D3:D7-მდე. ეს გამორიცხავს ფორმულის უშუალოდ რედაქტირების საჭიროებას G1 უჯრედში.

ამ მაგალითში COUNTIF ფუნქცია ითვლის მხოლოდ რიცხვების შემცველ უჯრედებს, თუ ისინი 10-ზე მეტია. მიუხედავად იმისა, რომ D1:D6 დიაპაზონის ხუთი უჯრედიდან ოთხი შეიცავს მონაცემებს, მხოლოდ სამი უჯრედი შეიცავს ციფრებს. უჯრედები, რომლებიც ცარიელია ან შეიცავს ტექსტურ მონაცემებს, ფუნქცია იგნორირებულია.

ტექსტის დათვლა COUNTIF

COUNTIF ფუნქცია არ შემოიფარგლება რიცხვითი მონაცემების დათვლით. ის ასევე ითვლის უჯრედებს, რომლებიც შეიცავს ტექსტს, შემოწმებით, შეესაბამება თუ არა ისინი გარკვეულ ტექსტს.

ამისთვის, შემდეგი ფორმულა შეიყვანება უჯრედში G2:

=COUNTIF(INDIRECT(E1&":"&E2), "ორი")

ამ ფორმულაში INDIRECT ფუნქცია მიუთითებს B1-დან B6-მდე უჯრედებზე. COUNTIF ფუნქცია შეადგენს უჯრედების რაოდენობას, რომლებსაც აქვთ ტექსტის მნიშვნელობა ორი.

ამ შემთხვევაში შედეგი არის 1.

COUNTA, COUNTBLANK და INDIRECT

Excel-ის დათვლის ორი სხვა ფუნქციაა COUNTA, რომელიც ითვლის უჯრედებს, რომლებიც შეიცავს ნებისმიერი ტიპის მონაცემს, ხოლო უგულებელყოფს მხოლოდ ცარიელ ან ცარიელ უჯრედებს, და COUNTBLANK, რომელიც ითვლის მხოლოდ ცარიელ ან ცარიელ უჯრედებს დიაპაზონში.

რადგან ორივე ამ ფუნქციას აქვს COUNTIF ფუნქციის მსგავსი სინტაქსი, ისინი შეიძლება შეიცვალოს ზემოთ მოცემულ მაგალითში INDIRECT-ით შემდეგი ფორმულების შესაქმნელად:

=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
D1:D6 დიაპაზონისთვის COUNTA აბრუნებს 4-ის პასუხს, რადგან ხუთი უჯრედიდან ოთხი შეიცავს მონაცემებს. COUNTBLANK აბრუნებს 1 პასუხს, რადგან დიაპაზონში მხოლოდ ერთი ცარიელი უჯრედია.

რატომ გამოვიყენოთ არაპირდაპირი ფუნქცია?
INDIRECT ფუნქციის გამოყენების უპირატესობა ყველა ამ ფორმულაში არის ის, რომ ახალი უჯრედების ჩასმა შესაძლებელია დიაპაზონის ნებისმიერ ადგილას.
დიაპაზონი დინამიურად იცვლება სხვადასხვა ფუნქციების შიგნით და შედეგები განახლდება შესაბამისად.





INDIRECT ფუნქციის გარეშე, ყველა ფუნქცია უნდა იყოს რედაქტირება, რათა შეიცავდეს 7ვე უჯრედს, მათ შორის ახალს.
INDIRECT ფუნქციის უპირატესობებია ის, რომ ტექსტის მნიშვნელობები შეიძლება ჩასმული იყოს უჯრედის მითითების სახით და ის დინამიურად განაახლებს დიაპაზონებს, როდესაც თქვენი ცხრილი იცვლება.
ეს აადვილებს ცხრილების მთლიან შენარჩუნებას, განსაკუთრებით ძალიან დიდი ცხრილებისთვის.