Yi Amfanin Hanyoyin Excel ta Aiki don Ƙididdiga Matakai

Aikin COUNTIFS, wanda za'a iya amfani dashi don ƙidaya adadin lokutan bayanai a cikin biyu ko fiye da jeri na sel ya hadu da ka'idoji da yawa an fara gabatarwa a cikin Excel 2007. Kafin wannan, kawai COUNTIF, wanda aka tsara domin ƙidaya yawan adadin sel a wani kewayon da ya hadu da wani ma'auni guda ɗaya, yana samuwa.

Ga wadanda ke amfani da Excel 2003 ko tsoffin versions, ko waɗanda suke son wani zabi ga COUNTIFS, maimakon ƙoƙari su gano hanyar da za su ƙidaya ka'idodi masu yawa ta amfani da COUNTIF, za a iya amfani da aikin SUMPRODUCT a maimakon.

Kamar yadda COUNTIFS, jeri da aka yi amfani da SUMPRODUCT dole ne girman girman.

Bugu da ari, aikin kawai yana ƙididdige lokuttuka inda aka hadu da ma'auni na kowane fanni lokaci ɗaya - kamar su a jere guda ɗaya.

Yadda za a yi amfani da aikin SUMPRODUCT

Haɗin da aka yi amfani dashi don aikin SUMPRODUCT lokacin da ake amfani dashi don ƙididdige ma'auni mai yawa ya bambanta fiye da aikin da ake amfani dashi:

= SUMPRODUCT (Criteria_range-1, Criteria-1) * (Criteria_range-2, Criteria-2) * ...)

Criteria_range - rukuni na sel shine aikin nema.

Criteria - ya ƙayyade ko za a kidaya tantanin halitta ko a'a.

A cikin misalin da ke ƙasa, za mu ƙidaya kawai layuka a cikin samfurin samfurin E1 zuwa G6 wanda ya dace da ka'idodi da aka ƙayyade ga dukan ginshiƙai uku na bayanai.

Za'a kidaya layuka kawai idan sun hadu da ka'idojin da suka dace:
Shafin E: idan lambar ta kasa da ko daidai da 2;
Shafin F: idan lambar tana daidaita da 4;
Column G: idan lambar ta fi girma ko kuma daidai da 5.

Misali Yin Amfani da Ayyukan Hanyoyin Sakamakon Excel

Lura: Tunda wannan ba amfani da shi ba ne na aikin SUMPRODUCT, ba'a iya shigar da aikin ta amfani da akwatin maganganu ba , amma dole ne a danna cikin tantanin salula.

  1. Shigar da wadannan bayanan cikin sel E1 zuwa E6: 1, 2, 1, 2, 2, 8.
  2. Shigar da bayanai masu zuwa cikin sel F1 zuwa F6: 4, 4, 6, 4, 4, 1.
  3. Shigar da bayanai masu zuwa cikin sel G1 zuwa G6: 5, 1, 5, 3, 8, 7.
  4. Danna kan tantanin halitta I1 - wurin da za a nuna sakamakon aikin.
  5. Rubuta wannan zuwa cikin cell I1:
    1. = ƙaddamarwa ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) kuma danna maɓallin Shigar da ke keyboard.
  6. Amsar 2 ya kamata ya bayyana a tantanin halitta I1 tun da akwai layuka guda biyu (layuka 1 da 5) wanda ke haɗuwa da duka uku na ma'auni da aka jera a sama.
  7. Sakamakon aikin = SUMPRODUCT ((E1: E6 <= 5) * (F1: F6 = 4) * (E1: E6> = 5)) ya bayyana a cikin takaddun tsari a sama da aikin aiki lokacin da ka danna kan tantanin I1.