Jerin saukarwa a cikin tantanin halitta

Video

 Wanene yake da ɗan lokaci kuma yana buƙatar fahimtar ainihin ainihin - kalli bidiyon horo:

Waɗanda suke sha'awar cikakkun bayanai da nuances na duk hanyoyin da aka bayyana - kara ƙasa da rubutu.

Hanyar 1. Na farko

Danna dama guda ɗaya akan fanko cell a ƙarƙashin ginshiƙi tare da bayanai, umarnin menu na mahallin Zaɓi daga jerin saukewa (Zaba daga jerin abubuwan da aka saukar) ko danna gajeriyar hanyar madannai ALT+ kibiya ƙasa. Hanyar ba ta aiki idan aƙalla layin fanko ɗaya ya raba tantanin halitta da ginshiƙin bayanai, ko kuma idan kuna buƙatar samfurin da ba a taɓa shigar da shi a sama ba:

Hanyar 2. Standard

1. Zaɓi sel masu ɗauke da bayanan da yakamata a haɗa su cikin jerin abubuwan da aka saukar (misali, sunayen samfur).
2. Idan kana da Excel 2003 ko fiye, zaɓi daga menu Saka - Suna - Sanya (Saka - Suna - Ƙayyadaddun), idan Excel 2007 ko sabo, bude shafin dabarbari kuma amfani da button Mai sarrafa sunaSa'an nan Create. Shigar da suna (kowane suna yana yiwuwa, amma ba tare da sarari ba kuma fara da harafi!) don kewayon da aka zaɓa (misali. Samfur). Danna kan OK.
3. Zaɓi sel (zaku iya samun da yawa a lokaci ɗaya) waɗanda kuke son samun jerin zaɓuka kuma zaɓi daga menu (akan shafin) Bayanai - Duba (Bayani - Tabbatarwa). Daga drop down list Nau'in Bayanai (Ba da izini) zaɓi zaɓi list kuma shiga cikin layi source daidai alamar da sunan kewayon (watau =Kayayyaki).

latsa OK.

Komai! Ji dadin!

Muhimmin nuance. Kewayo mai ƙarfi mai ƙarfi, kamar lissafin farashi, kuma na iya aiki azaman tushen bayanai don jeri. Bayan haka, lokacin ƙara sabbin samfura zuwa lissafin farashin, za a ƙara su ta atomatik zuwa jerin zaɓuka. Wani dabarar da aka saba amfani da shi don irin waɗannan jerin sunayen shine ƙirƙirar abubuwan da aka haɗa (inda abun ciki na lissafin ɗaya ya canza dangane da zaɓi a cikin wani).

Hanyar 3: Sarrafa

Wannan hanyar ita ce shigar da sabon abu akan takardar - sarrafa akwatin hadawa, sannan a ɗaure shi zuwa jeri akan takardar. Don wannan:

1. A cikin Excel 2007/2010, buɗe shafin developer. A cikin sigogin da suka gabata, Toolbar Forms ta hanyar menu Duba – Toolbars – Forms (Duba – Toolbar – Forms). Idan wannan shafin bai ganuwa ba, to danna maɓallin Office - Zaɓuɓɓukan Excel - akwati Nuna Tab ɗin Mai Haɓakawa a cikin Ribbon (Maɓallin Ofishin - Zaɓuɓɓukan Excel - Nuna Tab ɗin Mai Haɓakawa a cikin Ribbon)
2. Nemo alamar zazzagewa tsakanin sarrafa tsari (ba ActiveX!). Bi abubuwan da aka nuna akwatin haduwa:

   Danna gunkin kuma zana ƙaramin rectangle a kwance - jerin abubuwan gaba.

3. Danna-dama akan jerin da aka zana kuma zaɓi umarni Tsarin Abu (Tsarin Tsarin). A cikin akwatin maganganu da ya bayyana, saita

• Ƙirƙiri jeri ta iyaka – zaɓi sel masu sunaye na kaya waɗanda yakamata a haɗa su cikin lissafin
• Sadarwar salula – saka tantanin halitta inda kake son nuna lambar serial na abun da mai amfani ya zaɓa.
• Adadin layin jeri - layuka nawa da za a nuna a cikin jerin zaɓuka. Tsohuwar ita ce 8, amma ƙari yana yiwuwa, wanda hanyar da ta gabata ba ta yarda ba.

Bayan danna kan OK ana iya amfani da lissafin.

Don nuna sunansa maimakon lambar serial na element ɗin, zaku iya amfani da aikin kuma INDEX (INDEX), wanda zai iya nuna abubuwan da ke cikin tantanin halitta da ake buƙata daga kewayon:

Hanyar 4: ActiveX iko

Wannan hanyar a wani bangare tayi kama da wacce ta gabata. Babban bambancin shi ne cewa ba iko ba ne da aka ƙara a cikin takardar, amma iko na ActiveX. "Akwatin Combo" daga akwatin zazzagewar da ke ƙasa maballin Saka daga tab developer:

Hanyar ƙarawa iri ɗaya ce - zaɓi abu daga lissafin kuma zana shi akan takardar. Amma sai an fara bambance-bambance masu tsanani daga hanyar da ta gabata.

Da fari dai, ƙirƙirar jeri mai saukarwa na ActiveX na iya kasancewa a cikin manyan jihohi biyu daban-daban - yanayin lalata, lokacin da zaku iya saita sigogi da kaddarorin, matsar da shi a kusa da takardar kuma sake girmansa, kuma - yanayin shigarwa, lokacin da kawai abin da zaku iya yi. shine zabar bayanai daga ciki. Ana yin sauyawa tsakanin waɗannan hanyoyin ta amfani da maɓallin. Yanayin Zane tab developer:

Idan an danna wannan maɓallin, to, zamu iya daidaita ma'auni na jerin zaɓuka ta danna maɓallin kusa Properties, wanda zai buɗe taga tare da jerin duk saitunan da aka zaɓa don abin da aka zaɓa:

Mafi mahimmanci da kaddarorin masu amfani waɗanda za a iya kuma yakamata a daidaita su:

• JerinFillRange - kewayon sel inda aka ɗauko bayanan lissafin. Ba zai ba ku damar zaɓar kewayon tare da linzamin kwamfuta ba, kawai kuna buƙatar shigar da shi da hannuwanku daga madannai (misali, Sheet2! A1: A5)
• LinkedCell – tantanin halitta mai alaƙa inda za a nuna abin da aka zaɓa daga lissafin
• Lissafin Lissafi – adadin layuka da aka nuna
• font - font, girman, salo (italic, layi, da sauransu sai dai launi)
• ForeColor и launi na baya – rubutu da bango launi, bi da bi

Babban da mai da wannan hanya shine ikon yin saurin tsalle zuwa abin da ake so a cikin jerin lokacin shigar da haruffan farko daga maballin madannai (!), wanda ba ya samuwa ga duk sauran hanyoyin. Kyakkyawan batu, kuma, shine ikon keɓance gabatarwar gani (launuka, fonts, da sauransu).

Lokacin amfani da wannan hanya, kuma yana yiwuwa a tantance kamar JerinFillRange ba kawai jeri-girma ɗaya ba. Kuna iya, alal misali, saita kewayon ginshiƙai biyu da layuka da yawa, yana nuna ƙari cewa kuna buƙatar nuna ginshiƙai biyu (dukiyoyi). Ƙididdigar Rukunin=2). Sannan zaku iya samun sakamako mai ban sha'awa wanda zai biya duk ƙoƙarin da aka kashe akan ƙarin saitunan:

Teburin kwatanta ƙarshe na duk hanyoyin

:

• Jerin saukarwa tare da bayanai daga wani fayil
• Ƙirƙirar Dogarorin Dogara
• Ƙirƙirar jerin zaɓuka ta atomatik ta ƙarar PLEX
• Zaɓin hoto daga jerin zaɓuka
• Cire abubuwan da aka riga aka yi amfani da su ta atomatik daga jerin zaɓuka
• Jerin saukarwa tare da ƙari ta atomatik na sabbin abubuwa