Yadda ake yin jerin zaɓuka a cikin Excel

Jerin abubuwan da aka saukar kayan aiki ne mai ban sha'awa mai fa'ida wanda zai iya taimakawa yin aiki tare da bayanai cikin kwanciyar hankali. Yana ba da damar ƙunsar dabi'u da yawa a cikin tantanin halitta lokaci guda, waɗanda za ku iya aiki da su, kamar sauran su. Don zaɓar wanda kuke buƙata, kawai danna gunkin kibiya, bayan haka an nuna jerin ƙimar uXNUMXbuXNUMXbis. Bayan zabar wani tantanin halitta, tantanin halitta yana cika da shi kai tsaye, kuma ana sake ƙididdige ƙididdiga bisa ga shi.

Excel yana ba da hanyoyi daban-daban don ƙirƙirar menu mai saukewa, kuma ƙari, yana ba ku damar daidaita su cikin sassauƙa. Bari mu bincika waɗannan hanyoyin dalla-dalla.

Tsarin ƙirƙirar lissafi

Don samar da menu mai tasowa, danna abubuwan menu tare da hanyar "Data" - "Tabbacin Bayanai". Akwatin maganganu zai buɗe inda kake buƙatar nemo shafin "Parameters" kuma danna shi idan ba a buɗe ba a baya. Yana da saitunan da yawa, amma abu "Nau'in Bayanai" yana da mahimmanci a gare mu. Daga cikin dukkan ma'anar, "List" shine daidai.

Adadin hanyoyin da ake shigar da bayanai a cikin jerin faɗowa suna da girma sosai.

1. Nuni mai zaman kanta na abubuwan jerin abubuwan da ke raba su da wani yanki a cikin filin “Source” da ke kan wannan shafin na akwatin maganganu iri ɗaya.
   

   

2. Alamar farko ta ƙima. Filin Tushen ya ƙunshi kewayon inda ake samun bayanin da ake buƙata.
   

   

3. Ƙayyadaddun kewayon mai suna. Hanyar da ke maimaita ta baya, amma kawai dole ne a fara suna kewayon.
   

   

Duk waɗannan hanyoyin zasu haifar da sakamakon da ake so. Bari mu dubi hanyoyin samar da jerin abubuwan da aka saukar a cikin yanayi na ainihi.

Dangane da bayanai daga lissafin

Bari mu ce muna da tebur da ke kwatanta nau'ikan 'ya'yan itatuwa daban-daban.

Don ƙirƙirar jeri a cikin menu mai buɗewa dangane da wannan saitin bayanai, kuna buƙatar yin waɗannan abubuwa:

1. Zaɓi tantanin halitta da aka tanada don lissafin gaba.
2. Nemo shafin Data akan kintinkiri. A can za mu danna "Tabbatar da bayanai".
   

   

3. Nemo abu "Nau'in Bayanai" kuma canza darajar zuwa "Jeri".
   

   

4. A cikin filin da ke nuna zaɓin "Source", shigar da kewayon da ake so. Lura cewa cikakkun bayanai dole ne a ƙayyade ta yadda lokacin yin kwafin jeri, bayanin ba zai canza ba.
   

Bugu da kari, akwai aiki don samar da lissafin lokaci guda a cikin tantanin halitta fiye da ɗaya. Don cimma wannan, ya kamata ku zaɓi su duka, kuma kuyi matakai iri ɗaya kamar yadda aka bayyana a baya. Hakanan, kuna buƙatar tabbatar da cewa an rubuta cikakkun bayanai. Idan adireshin ba shi da alamar dala kusa da ginshiƙi da sunayen jeri, to kuna buƙatar ƙara su ta danna maɓallin F4 har sai alamar $ tana kusa da ginshiƙi da sunayen jere.

Tare da rikodin bayanan hannu

A cikin halin da ake ciki a sama, an rubuta lissafin ta hanyar nuna alamar da ake bukata. Wannan hanya ce mai dacewa, amma wani lokacin yana buƙatar yin rikodin bayanai da hannu. Wannan zai ba da damar guje wa kwafin bayanai a cikin littafin aiki.

A ce mun fuskanci aikin ƙirƙirar jerin abubuwan da ke ɗauke da zaɓuɓɓuka biyu masu yiwuwa: i da a'a. Don kammala aikin, dole ne:

1. Danna kan tantanin halitta don lissafin.
2. Bude "Data" kuma a can nemo sashin "Duba Bayanai" wanda muka saba.
   

   

3. Hakanan, zaɓi nau'in "List".
   

   

4. Anan kuna buƙatar shigar da “Eh; A'a" a matsayin tushen. Mun ga cewa an shigar da bayanai da hannu ta amfani da wani ɗan ƙaramin abu don ƙididdigewa.

Bayan danna Ok, muna da sakamako mai zuwa.

Bayan haka, shirin zai ƙirƙiri menu mai saukewa ta atomatik a cikin tantanin halitta da ya dace. Duk bayanan da mai amfani ya ayyana azaman abubuwa a cikin jerin faɗowa. Dokokin ƙirƙirar jeri a cikin sel da yawa sun yi kama da waɗanda suka gabata, tare da banda cewa dole ne ka ƙirƙiri bayanin da hannu ta amfani da semicolon.

Ƙirƙirar jerin zaɓuka ta amfani da aikin OFFSET

Baya ga hanyar gargajiya, yana yiwuwa a yi amfani da aikin JUYAWAdon samar da menu na zaɓuka.

Mu bude takardar.

Don amfani da aikin don jerin zaɓuka, kuna buƙatar yin waɗannan abubuwa:

1. Zaɓi tantanin halitta mai ban sha'awa inda kake son sanya lissafin gaba.
2. Bude shafin "Data" da taga "Data Tabbatarwa" a jere.
   

   

3. Saita "List". Ana yin haka ne kamar yadda aka yi a cikin misalan da suka gabata. A ƙarshe, ana amfani da dabara mai zuwa: = KASHE(A$2;0;0;5). Muna shigar da shi inda aka ƙayyade sel waɗanda za a yi amfani da su azaman hujja.

Sa'an nan shirin zai haifar da menu tare da jerin 'ya'yan itatuwa.

Ma'anar wannan shine:

= Offset

Mun ga cewa wannan aikin yana da hujjoji guda 5. Na farko, an ba da adireshin tantanin halitta na farko da za a kashe. Hujjoji biyu na gaba sun fayyace yawan layuka da ginshiƙan da za a biya su. Da yake magana game da mu, hujjar Tsawo shine 5 saboda yana wakiltar tsayin jerin. 

Jerin saukarwa a cikin Excel tare da sauya bayanai (+ ta amfani da aikin OFFSET)

A cikin lamarin da aka bayar JUYAWA an yarda ya ƙirƙiri menu mai faɗowa wanda ke cikin tsayayyen kewayo. Lalacewar wannan hanyar ita ce bayan ƙara kayan, dole ne ku gyara dabarar da kanku.

Don ƙirƙirar jeri mai ƙarfi tare da goyan baya don shigar da sabbin bayanai, dole ne ku:

1. Zaɓi tantanin halitta na sha'awa.
2. Fadada shafin "Data" kuma danna kan "Data Tabbatarwa".
3. A cikin taga da ke buɗewa, sake zaɓi abu "List" kuma saka wannan dabarar azaman tushen bayanai: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Danna Ya yi.

Wannan ya ƙunshi aiki COUNTIF, don ƙayyade adadin ƙwayoyin da aka cika nan da nan (ko da yake yana da yawan amfani da yawa, kawai mu rubuta shi a nan don takamaiman dalili).

Domin tsarin ya yi aiki bisa ga al'ada, ya zama dole a gano ko akwai sel fanko akan hanyar dabarar. Kada su kasance.

Jerin saukarwa tare da bayanai daga wani takarda ko fayil na Excel

Hanyar gargajiya ba ta aiki idan kuna buƙatar samun bayanai daga wata takarda ko ma takardar da ke cikin fayil iri ɗaya. Don wannan, ana amfani da aikin GASKIYA, wanda ke ba ka damar shigar da madaidaicin tsari hanyar haɗi zuwa tantanin halitta da ke cikin wani takarda ko gaba ɗaya - fayil. Kuna buƙatar yin waɗannan abubuwa:

1. Kunna tantanin halitta inda muka sanya lissafin.
2. Mun bude taga da muka sani. A daidai wurin da a baya muka nuna maɓuɓɓuka don wasu jeri, ana nuna dabara a cikin tsari = GASKIYA ("[List1.xlsx] Sheet1! $ A$1: $ A$9"). A zahiri, maimakon List1 da Sheet1, zaku iya saka littafinku da sunaye, bi da bi. 

Hankali! Ana ba da sunan fayil ɗin a maƙallan murabba'i. A wannan yanayin, Excel ba zai iya amfani da fayil ɗin da aka rufe a halin yanzu azaman tushen bayanai ba.

Hakanan ya kamata a lura cewa sunan fayil ɗin da kansa yana da ma'ana kawai idan takaddar da ake buƙata tana cikin babban fayil iri ɗaya da wanda za a saka jerin. Idan ba haka ba, to dole ne ku saka adireshin wannan takaddar gaba ɗaya.

Ƙirƙirar Dogarorin Dogara

Lissafin dogara shine wanda zaɓin mai amfani ya shafe abun ciki a cikin wani jeri. A ce a gabanmu akwai tebur da aka bude wanda ke dauke da jeri uku, kowannensu an sanya masa suna.

Kuna buƙatar bin waɗannan matakan don ƙirƙirar jerin sunayen waɗanda zaɓin da aka zaɓa a cikin wani lissafin ya shafa sakamakonsu.

1. Ƙirƙiri jeri na 1 tare da kewayon sunaye.
   

   

2. A wurin shigarwar tushe, ana nuna alamun da ake buƙata ɗaya bayan ɗaya.
   

   

3. Ƙirƙiri jeri na 2 dangane da irin shukar da mutumin ya zaɓa. A madadin, idan kun ƙayyade bishiyoyi a cikin jerin farko, to, bayanin da ke cikin jerin na biyu zai zama "oak, hornbeam, chestnut" da kuma bayan. Wajibi ne a rubuta dabarar a wurin shigar da tushen bayanai = GASKIYA (E3). E3 – cell mai dauke da sunan kewayon 1.=INDIRECT(E3). E3 – cell tare da sunan lissafin 1.

Yanzu komai ya shirya.

Yadda za a zabi mahara dabi'u daga drop down list?

Wani lokaci ba zai yiwu a ba da fifiko ga ƙima ɗaya kawai ba, don haka dole ne a zaɓi fiye da ɗaya. Sannan kuna buƙatar ƙara macro zuwa lambar shafi. Yin amfani da haɗin maɓalli Alt + F11 yana buɗe Editan Kayayyakin Kayayyakin Kayayyaki. Kuma an saka lambar a can.

Canje-canje na Sub Worksheet_Mai zaman kansa (ByVal Target As Range)

    A kan Kuskuren Zaima A gaba

    Idan Ba ​​Intersect (Manufa, Range («Е2: Е9»)) Ba Komai Kuma Target.Cells.Count = 1 Sa'an nan

        Application.EnableEvents = Karya

        Idan Len (Target.Offset (0, 1)) = 0 Sai

            Target.Offset (0, 1) = Target

        wani

            Target.Ƙarshe (xlToRight) .Offset (0, 1) = Target

        Ƙare Idan

        Target.Clear Abun ciki

        Application.EnableEvents = Gaskiya

    Ƙare Idan

karshen Sub 

Domin a nuna abinda ke cikin sel a ƙasa, muna saka lambar mai zuwa a cikin editan.

Canje-canje na Sub Worksheet_Mai zaman kansa (ByVal Target As Range)

    A kan Kuskuren Zaima A gaba

    Idan Ba ​​Intersect (Manufa, Range («Н2: К2»)) Babu Komai Kuma Target.Cells.Count = 1 Sai

        Application.EnableEvents = Karya

        Idan Len (Target.Offset (1, 0)) = 0 Sai

            Target.Offset (1, 0) = Target

        wani

            Target.Ƙarshe (xlDown) .Kasa (1, 0) = Target

        Ƙare Idan

        Target.Clear Abun ciki

        Application.EnableEvents = Gaskiya

    Ƙare Idan

karshen Sub

Kuma a ƙarshe, ana amfani da wannan lambar don rubutawa a cikin tantanin halitta ɗaya.

Canje-canje na Sub Worksheet_Mai zaman kansa (ByVal Target As Range)

    A kan Kuskuren Zaima A gaba

    Idan Ba ​​Intersect (Manufa, Range («C2: C5»)) Ba Komai bane Kuma Target.Cells.Count = 1 Sannan

        Application.EnableEvents = Karya

        newVal = Target

        Aikace-aikace.Buɗe

        oldval = Target

        If Len (oldval) <> 0 And oldval <> newVal Sai

            Target = Target & «,» & newVal

        wani

            Target = newVal

        Ƙare Idan

        Idan Len (newVal) = 0 Sai Target.ClearContents

        Application.EnableEvents = Gaskiya

    Ƙare Idan

karshen Sub

Ana iya daidaita jeri.

Yadda ake yin jerin zaɓuka tare da bincike?

A wannan yanayin, dole ne ka fara amfani da wani nau'in jeri na daban. Shafin "Developer" yana buɗewa, bayan haka kuna buƙatar danna ko taɓa (idan allon taɓawa) akan ɓangaren "Saka" - "ActiveX". Yana da akwatin haduwa. Za a sa ka zana wannan jeri, bayan haka za a ƙara shi cikin takaddar.

Bugu da ari, an saita shi ta hanyar kaddarorin, inda aka ƙayyade kewayon a cikin zaɓi na ListFillRange. An saita tantanin halitta inda aka bayyana ƙimar mai amfani ta amfani da zaɓi na LinkedCell. Na gaba, kawai kuna buƙatar rubuta haruffan farko, saboda shirin zai ba da shawarar ƙimar ƙima ta atomatik.

Jerin saukarwa tare da sauya bayanan atomatik

Hakanan akwai aikin da ake musanya bayanan ta atomatik bayan an ƙara su zuwa kewayon. Yana da sauƙi don yin wannan:

1. Ƙirƙiri saitin sel don lissafin gaba. A cikin yanayinmu, wannan saitin launuka ne. Muna zabar shi.
   

   

2. Na gaba, yana buƙatar tsara shi azaman tebur. Kuna buƙatar danna maɓallin sunan ɗaya kuma zaɓi salon tebur.
   

   

   

Na gaba, kuna buƙatar tabbatar da wannan kewayon ta latsa maɓallin "Ok".

Muna zabar tebirin da aka samu kuma mu ba shi suna ta filin shigar da ke saman shafi A.

Shi ke nan, akwai tebur, kuma ana iya amfani da shi azaman tushen jerin abubuwan da aka saukar, wanda kuke buƙatar:

1. Zaɓi tantanin halitta inda lissafin yake.
2. Bude maganganun Tabbatar da Bayanai.
   

   

3. Mun saita nau'in bayanan zuwa "List", kuma a matsayin dabi'u muna ba da sunan tebur ta hanyar = alamar.
   

   

   

Komai, tantanin halitta yana shirye, kuma an nuna sunayen launuka a ciki, kamar yadda muke bukata a asali. Yanzu zaku iya ƙara sabbin mukamai ta hanyar rubuta su a cikin tantanin halitta da ke ƙasa kaɗan nan da nan bayan na ƙarshe.

Wannan shine fa'idar teburin, cewa kewayon yana ƙaruwa ta atomatik lokacin da aka ƙara sabbin bayanai. Saboda haka, wannan ita ce hanya mafi dacewa don ƙara jeri.

Yadda ake kwafi jerin saukewa?

Don kwafa, ya isa a yi amfani da haɗin maɓalli Ctrl + C da Ctrl + V. Don haka za a kwafi jerin abubuwan da aka saukar tare da tsarawa. Don cire tsarawa, kuna buƙatar amfani da manna na musamman (a cikin menu na mahallin, wannan zaɓi yana bayyana bayan kwafin jerin), inda aka saita zaɓin "sharadi akan ƙima".

Zaɓi duk sel masu ɗauke da jerin faɗuwa

Don cim ma wannan aikin, dole ne ku yi amfani da aikin "Zaɓi ƙungiyar sel" a cikin rukunin "Nemi kuma Zaɓi".

Bayan haka, akwatin maganganu zai buɗe, inda za ku zaɓi abubuwan "Duk" da "Waɗannan iri ɗaya" a cikin menu na "Data Tabbatarwa". Abu na farko yana zaɓar duk lissafin, na biyu kuma yana zaɓar waɗanda suke kama da wasu kawai.