Bendroji formulė
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))
Santrauka
Norėdami suskaičiuoti atitikmenis visoje darbaknygėje, galite naudoti formulę, pagrįstą funkcijomis COUNTIF ir SUMPRODUCT. Parodytame pavyzdyje formulė D5 yra:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))
kur „lakštai“ yra įvardytas diapazonas B8: B10.
Kontekstas
Šioje darbo knygoje yra keturi darbalapiai. Trijuose darbalapiuose „Sheet1“, „Sheet2“ ir „Sheet3“ yra 1000 atsitiktinių vardų lentelėje, kuri atrodo taip:
Pastaba: diapazonas, kurį naudojame formulėje A1: Z10000, yra savavališkas ir turėtų būti koreguojamas, kad atitiktų jūsų duomenis.
Paaiškinimas
Dirbdami iš vidaus, pirmiausia turime šią išraišką netiesioginės funkcijos viduje:
"'"&sheets&"'!A1:Z10000"
Kadangi „lakštai“ yra pavadintas diapazonas, kuriame yra „Sheet1“, „Sheet2“ ir „Sheet3“, įvertinę išraišką gausime tokį masyvą:
("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")
Masyvo viduje mes turime tris reikšmes, ir kiekviena iš jų yra lapo pavadinimas, sujungtas sujungiant A1: Z10000 diapazoną. Atkreipkite dėmesį, kad tai visos teksto vertės.
Toliau funkcija NETIESIOGINĖ naudojama konvertuoti kiekvieną teksto vertę į tinkamą nuorodą, kuri pateikiama funkcijai COUNTIF kaip diapazono argumentas, kartu su kriterijų D5 verte.
Kadangi mes suteikėme COUNTIF tris atskirus diapazonus, mes gauname tris tokius masyvo rezultatus:
(5;6;5)
Kiekvienas elementas yra skaičiuojamas vienam lapui.
Galiausiai masyvui susumuoti naudojamas SUMPRODUCT ir pateikia rezultatą 16.
