Чӣ тавр рӯйхати афтандаро дар Excel сохтан мумкин аст

Рӯйхати афтанда воситаи бениҳоят муфид аст, ки метавонад барои осонтар кардани кор бо иттилоот кӯмак кунад. Он имкон медиҳад, ки дар як ячейка якбора якчанд арзишҳо дошта бошанд, ки шумо метавонед бо онҳо кор кунед, ба мисли ҳама чизҳои дигар. Барои интихоби он чизе, ки ба шумо лозим аст, танҳо тасвири тирро клик кунед, ки пас аз он рӯйхати арзишҳои uXNUMXbuXNUMXbis намоиш дода мешаванд. Пас аз интихоби яки мушаххас чашмак ба таври худкор бо он пур карда мешавад ва формулаҳо дар асоси он аз нав ҳисоб карда мешаванд.

Excel усулҳои гуногуни тавлиди менюи афтандаро пешниҳод мекунад ва илова бар ин, он ба шумо имкон медиҳад, ки онҳоро ба таври фасењ танзим кунед. Биёед ин усулҳоро муфассалтар таҳлил кунем.

Раванди эҷоди рӯйхат

Барои тавлиди менюи поп-ап, ҷузъҳои менюро дар роҳи "Маълумот" - "Тасдиқи маълумот" клик кунед. Қуттии муколама кушода мешавад, ки дар он шумо бояд ҷадвали "Параметрҳо" -ро пайдо кунед ва агар он қаблан кушода нашуда бошад, онро клик кунед. Он танзимоти зиёде дорад, аммо ҷузъи "Намуди маълумот" барои мо муҳим аст. Аз ҳама маъноҳо "Рӯйхат" дуруст аст.

Миқдори усулҳое, ки тавассути онҳо маълумот ба рӯйхати поп-ап ворид карда мешавад, хеле зиёд аст.

1. Нишондиҳии мустақили унсурҳои рӯйхат, ки бо нуқта-вергул ҷудо карда шудаанд, дар майдони "Сарчашма" дар ҳамон ҷадвали як қуттии муколама ҷойгир аст.
   

   

2. Нишондиҳандаи пешакии арзишҳо. Майдони Сарчашма диапазонеро дар бар мегирад, ки дар он маълумоти зарурӣ мавҷуд аст.
   

   

3. Муайян кардани диапазони номбаршуда. Усуле, ки усули пешинаро такрор мекунад, аммо танҳо барои ном кардани диапазон пешакӣ зарур аст.
   

   

Ҳар яке аз ин усулҳо натиҷаи дилхоҳ медиҳад. Биёед усулҳои тавлиди рӯйхатҳои афтандаро дар ҳолатҳои воқеӣ дида бароем.

Дар асоси маълумот аз рӯйхат

Фарз мекунем, ки мо ҷадвале дорем, ки навъҳои меваҳои гуногунро тавсиф мекунад.

Барои сохтани рӯйхат дар менюи афтанда дар асоси ин маҷмӯи маълумот, шумо бояд амалҳои зеринро иҷро кунед:

1. Чашмакеро, ки барои рӯйхати оянда захира шудааст, интихоб кунед.
2. Дар лента ҷадвали маълумотро пайдо кунед. Дар он ҷо мо "Тасдиқи маълумот" -ро клик мекунем.
   

   

3. Ҷузъи "Намуди маълумот" -ро пайдо кунед ва арзишро ба "Рӯйхат" иваз кунед.
   

   

4. Дар майдоне, ки опсияи "Манбаъ" -ро ифода мекунад, диапазони дилхоҳро ворид кунед. Лутфан таваҷҷӯҳ намоед, ки истинодҳои мутлақ бояд нишон дода шаванд, то ҳангоми нусхабардории рӯйхат маълумот тағир наёбад.
   

Илова бар ин, функсия барои тавлиди рӯйхатҳо якбора дар зиёда аз як чашмак мавҷуд аст. Барои ноил шудан ба ин, шумо бояд ҳамаи онҳоро интихоб кунед ва ҳамон қадамҳои дар боло тавсифшударо иҷро кунед. Боз, шумо бояд боварӣ ҳосил кунед, ки истинодҳои мутлақ навишта шудаанд. Агар суроға дар паҳлӯи номҳои сутун ва сатр аломати доллар надошта бошад, пас шумо бояд онҳоро бо пахш кардани тугмаи F4 то он даме ки аломати $ дар паҳлӯи номҳои сутун ва сатр ҷойгир нашавад, илова кунед.

Бо сабти дастӣ маълумот

Дар вазъияти дар боло овардашуда, рӯйхат бо нишон додани диапазони зарурӣ навишта шудааст. Ин усули қулай аст, аммо баъзан маълумотро дастӣ сабт кардан лозим аст. Ин имкон медиҳад, ки такрори маълумот дар китоби корӣ пешгирӣ карда шавад.

Фарз мекунем, ки мо бо вазифаи эҷоди рӯйхате истодаем, ки дорои ду интихоби имконпазир аст: ҳа ва не. Барои иҷрои вазифа зарур аст:

1. Барои рӯйхат чашмакро клик кунед.
2. "Маълумот" -ро кушоед ва дар он ҷо бахши "Тафтиши маълумот" -ро пайдо кунед, ки ба мо шинос аст.
   

   

3. Боз, навъи "Рӯйхат" -ро интихоб кунед.
   

   

4. Дар ин ҷо шумо бояд ворид кунед "Ҳа; Не" ҳамчун манбаъ. Мо мебинем, ки информатсия ба таври дастӣ бо истифода аз аломати вергул барои ҳисобкунӣ ворид карда мешавад.

Пас аз пахш кардани OK, мо натиҷаи зерин дорем.

Баъдан, барнома ба таври худкор менюи афтандаро дар чашмаки мувофиқ эҷод мекунад. Ҳама маълумоте, ки корбар ҳамчун ашё дар рӯйхати поп-ап муайян кардааст. Қоидаҳои ташкили рӯйхат дар якчанд чашмакҳо ба рӯйхатҳои қаблӣ монанданд, танҳо ба истиснои он, ки шумо бояд маълумотро ба таври дастӣ бо истифода аз аломати вергул муайян кунед.

Эҷоди рӯйхати афтанда бо истифода аз функсияи OFFSET

Илова ба усули классикӣ, имкон дорад, ки функсия истифода шавад ИСТИФОДАбарои тавлиди менюҳои афтанда.

Биёед варақаро кушоем.

Барои истифодаи функсия барои рӯйхати афтанда, шумо бояд амалҳои зеринро иҷро кунед:

1. Ячейкаи таваҷҷӯҳеро, ки мехоҳед рӯйхати ояндаро ҷойгир кунед, интихоб кунед.
2. Ҷадвали "Маълумот" ва равзанаи "Тасдиқи маълумот" -ро бо навбат кушоед.
   

   

3. "Рӯйхат" -ро таъин кунед. Ин ҳамон тавре ки мисолҳои қаблӣ иҷро карда мешавад. Дар ниҳоят, формулаи зерин истифода мешавад: =ОФСЕТ (A$2$;0;0;5). Мо онро ворид мекунем, ки дар он ячейкаҳое, ки ҳамчун аргумент истифода мешаванд, муайян карда мешаванд.

Сипас, барнома менюеро бо рӯйхати меваҳо эҷод мекунад.

Синтаксиси ин аст:

=OFFSET(маълумот, ҷуброни_хат, ҷуброни_сутун,[баландӣ],[барӣ])

Мо мебинем, ки ин функсия 5 аргумент дорад. Аввалан, суроғаи аввалини ячейка дода мешавад, ки бояд ҷуброн карда шавад. Ду далели навбатӣ муайян мекунанд, ки чанд сатр ва сутунро ҷуброн кардан лозим аст. Дар бораи мо сухан ронда, далели Баландӣ 5 аст, зеро он баландии рӯйхатро ифода мекунад. 

Рӯйхати афтанда дар Excel бо ивазкунии маълумот (+ бо истифода аз функсияи OFFSET)

Дар сурати додашуда ИСТИФОДА имкон дод, ки менюи поп-ап дар диапазони муқарраршуда ҷойгир карда шавад. Камбудии ин усул дар он аст, ки пас аз илова кардани ашё, шумо бояд формуларо худатон таҳрир кунед.

Барои сохтани рӯйхати динамикӣ бо дастгирии ворид кардани маълумоти нав, шумо бояд:

1. Ячейкаи таваҷҷӯҳро интихоб кунед.
2. Ҷадвали "Маълумот" -ро васеъ кунед ва "Тасдиқи маълумот" -ро клик кунед.
3. Дар равзанаи кушодашуда, боз банди "Рӯйхат" -ро интихоб кунед ва формулаи зеринро ҳамчун манбаи маълумот муайян кунед: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. OK ро пахш кунед.

Ин дорои функсия аст COUNTIF, барои фавран муайян кардани чанд ячейка пур карда шудааст (гарчанде ки он шумораи зиёди истифодаҳо дорад, мо онро танҳо бо мақсади мушаххас дар ин ҷо менависем).

Барои он ки формула муътадил кор кунад, пайгирӣ кардан лозим аст, ки оё дар роҳи формула ячейкаҳои холӣ вуҷуд доранд. Онҳо набояд бошанд.

Рӯйхати афтанда бо маълумот аз варақи дигар ё файли Excel

Усули классикӣ кор намекунад, агар ба шумо аз ҳуҷҷати дигар ё ҳатто варақе, ки дар ҳамон файл мавҷуд аст, маълумот гиред. Барои ин, функсия истифода мешавад Бавосита, ки ба шумо имкон медиҳад, ки дар формати дуруст истинод ба чашмаки дар варақи дигар ҷойгиршуда ё умуман файл ворид кунед. Шумо бояд амалҳои зеринро иҷро кунед:

1. Ячейкаеро, ки мо рӯйхатро ҷойгир мекунем, фаъол созед.
2. Мо тирезаеро, ки аллакай медонем, мекушоем. Дар ҳамон ҷое, ки мо қаблан манбаъҳоро барои диапазонҳои дигар нишон дода будем, формула дар формат нишон дода шудааст =БАЙРОХИАМ(“[List1.xlsx]Варақаи1!$A$1:$A$9”). Табиист, ки ба ҷои Рӯйхати1 ва Ҷадвали1, шумо метавонед мутаносибан номҳои китоб ва варақи худро ворид кунед. 

Диққат! Номи файл дар қавсҳои мураббаъ оварда шудааст. Дар ин ҳолат, Excel наметавонад файлеро, ки ҳоло ҳамчун манбаи иттилоот баста аст, истифода барад.

Инчунин бояд қайд кард, ки худи номи файл танҳо дар сурате маъно дорад, ки ҳуҷҷати зарурӣ дар ҳамон ҷузвдоне, ки рӯйхат дохил карда мешавад, ҷойгир бошад. Дар акси ҳол, шумо бояд суроғаи ин ҳуҷҷатро пурра нишон диҳед.

Эҷоди афтандаҳои вобаста

Рӯйхати вобаста онест, ки мундариҷаи он ба интихоби корбар дар рӯйхати дигар таъсир мерасонад. Фарз мекунем, ки мо дар пеши мо ҷадвали кушода дорем, ки дар он се диапазон мавҷуд аст, ки ба ҳар кадоми онҳо ном дода шудааст.

Шумо бояд ин қадамҳоро иҷро кунед, то рӯйхатҳоеро эҷод кунед, ки натиҷаи онҳо аз интихоби интихобшуда дар рӯйхати дигар таъсир мерасонад.

1. Рӯйхати 1-ро бо номҳои диапазон созед.
   

   

2. Дар нуқтаи вуруди манбаъ нишондиҳандаҳои зарурӣ як ба як таъкид карда мешаванд.
   

   

3. Вобаста ба намуди растании интихобкардаи шахс рӯйхати дуюмро эҷод кунед. Интихобан, агар шумо дарахтҳоро дар рӯйхати якум муайян кунед, он гоҳ маълумот дар рӯйхати дуюм "дуб, шохоб, шоҳбулут" ва берун аз он хоҳад буд. Дар ҷои вуруди манбаи маълумот формуларо навиштан лозим аст =БАЙРУСТАМ(E3). E3 – ячейкае, ки номи диапазони 1-ро дар бар мегирад.=БАЙРУВО(E3). E3 – ячейка бо номи рӯйхат 1.

Акнун ҳама чиз тайёр аст.

Чӣ тавр арзишҳои сершуморро аз рӯйхати афтанда интихоб кардан мумкин аст?

Баъзан танҳо ба як арзиш афзалият додан ғайриимкон аст, аз ин рӯ бояд бештар аз як арзиш интихоб карда шавад. Пас шумо бояд ба рамзи саҳифа макрос илова кунед. Бо истифода аз комбинатсияи Alt + F11 Муҳаррири Visual Basic -ро мекушояд. Ва код дар он ҷо ворид карда мешавад.

Варақаи кории хусусӣ_Тағйир (Ҳадафи ByVal ҳамчун диапазон)

    Дар бораи хатогии хатогӣ Next

    Агар бурида нашавад(Ҳадаф, диапазон(«Е2:Е9»)) Ҳеҷ чиз ва ҳадаф нест. Ҳуҷайраҳо. Ҳисоб = 1 Пас

        Application.EnableEvents = False

        Агар Len (Target.Offset (0, 1)) = 0 Пас

            Ҳадаф. Офсет (0, 1) = Ҳадаф

        боз

            Target.End (xlToRight) .Offset (0, 1) = Ҳадаф

        Агар End

        Target.ClearContents

        Application.EnableEvents = Дуруст

    Агар End

End Sub 

Барои он ки мундариҷаи чашмакҳо дар зер нишон дода шавад, мо рамзи зеринро ба муҳаррир ворид мекунем.

Варақаи кории хусусӣ_Тағйир (Ҳадафи ByVal ҳамчун диапазон)

    Дар бораи хатогии хатогӣ Next

    Агар бурида нашавад(Ҳадаф, диапазон(«Н2:К2»)) Ҳеҷ чиз ва ҳадаф нест. Ҳуҷайраҳо. Ҳисоб = 1 Пас

        Application.EnableEvents = False

        Агар Len (Target.Offset (1, 0)) = 0 Пас

            Ҳадаф. Офсет (1, 0) = Ҳадаф

        боз

            Target.End (xlDown) .Offset (1, 0) = Ҳадаф

        Агар End

        Target.ClearContents

        Application.EnableEvents = Дуруст

    Агар End

End Sub

Ва ниҳоят, ин код барои навиштан дар як ҳуҷайра истифода мешавад.

Варақаи кории хусусӣ_Тағйир (Ҳадафи ByVal ҳамчун диапазон)

    Дар бораи хатогии хатогӣ Next

    Агар бурида нашавад(Ҳадаф, диапазон(«C2:C5»)) Ҳеҷ чиз ва ҳадаф нест. Ҳуҷайраҳо. Ҳисоб = 1 Пас

        Application.EnableEvents = False

        newVal = Ҳадаф

        Ариза. Бекор кардан

        oldval = Ҳадаф

        Агар Лен (oldval) <> 0 Ва oldval <> newVal Он гоҳ

            Ҳадаф = Ҳадаф & «,» & newVal

        боз

            Ҳадаф = newVal

        Агар End

        Агар Len (newVal) = 0 Пас Target.ClearContents

        Application.EnableEvents = Дуруст

    Агар End

End Sub

Диапазонҳо таҳриршаванда мебошанд.

Рӯйхати афтандаро бо ҷустуҷӯ чӣ гуна бояд сохт?

Дар ин ҳолат, шумо бояд дар аввал намуди дигари рӯйхатро истифода баред. Ҷадвали "Таҳиягар" кушода мешавад, ки пас аз он шумо бояд дар элементи "Ворид" - "ActiveX" пахш кунед ё клик кунед (агар экран ламс карда шавад). Он дорои қуттии комбинатсияи. Аз шумо хоҳиш карда мешавад, ки ин рӯйхатро тартиб диҳед, пас он ба ҳуҷҷат илова карда мешавад.

Ғайр аз он, он тавассути хосиятҳо танзим карда мешавад, ки дар он диапазон дар опсияи ListFillRange муайян карда шудааст. Ячек, ки дар он арзиши аз ҷониби корбар муайяншуда намоиш дода мешавад, бо истифода аз имконоти LinkedCell танзим карда мешавад. Баъдан, шумо танҳо бояд ҳарфҳои аввалро нависед, зеро барнома ба таври худкор арзишҳои имконпазирро пешниҳод мекунад.

Рӯйхати афтанда бо ивазкунии автоматии маълумот

Инчунин функсияе мавҷуд аст, ки маълумот пас аз илова кардани онҳо ба диапазон ба таври худкор иваз карда мешавад. Ин корро кардан осон аст:

1. Барои рӯйхати оянда маҷмӯи чашмакҳоро эҷод кунед. Дар ҳолати мо, ин маҷмӯи рангҳост. Мо онро интихоб мекунем.
   

   

2. Баъдан, он бояд ҳамчун ҷадвал формат карда шавад. Шумо бояд тугмаи ҳамон номро пахш кунед ва услуби ҷадвалро интихоб кунед.
   

   

   

Баъдан, шумо бояд ин диапазонро бо пахш кардани тугмаи "OK" тасдиқ кунед.

Мо ҷадвали натиҷагириро интихоб мекунем ва ба он тавассути майдони вуруд, ки дар болои сутуни А ҷойгир аст, ном медиҳем.

Ин аст, ҷадвал вуҷуд дорад ва он метавонад ҳамчун асос барои рӯйхати афтанда истифода шавад, ки барои он ба шумо лозим аст:

1. Чашмакеро, ки рӯйхат ҷойгир аст, интихоб кунед.
2. Муколамаи тасдиқи маълумотро кушоед.
   

   

3. Мо навъи маълумотро ба "Рӯйхат" муқаррар мекунем ва ҳамчун арзишҳо номи ҷадвалро тавассути аломати = медиҳем.
   

   

   

Ҳама чиз, ячейка омода аст ва дар он номҳои рангҳо нишон дода шудаанд, тавре ки дар аввал ба мо лозим буд. Акнун шумо метавонед мавқеъҳои навро танҳо бо навиштани онҳо дар ячейкае, ки дарҳол пас аз охирин ҷойгир аст, илова кунед.

Ин бартарии ҷадвал аст, ки ҳангоми илова кардани маълумоти нав диапазон ба таври худкор зиёд мешавад. Мувофиқи он, ин роҳи аз ҳама қулайи илова кардани рӯйхат аст.

Чӣ тавр рӯйхати афтандаро нусхабардорӣ кардан мумкин аст?

Барои нусхабардорӣ, истифодаи комбинатсияи калидҳои Ctrl + C ва Ctrl + V кифоя аст. Ҳамин тавр, рӯйхати афтанда дар баробари форматкунӣ нусхабардорӣ карда мешавад. Барои нест кардани форматкунӣ, шумо бояд хамираи махсусро истифода баред (дар менюи контекстӣ, ин интихоб пас аз нусхабардории рӯйхат пайдо мешавад), ки дар он имконоти "шартҳо оид ба арзишҳо" муқаррар карда шудааст.

Ҳама ҳуҷайраҳои дорои рӯйхати афтандаро интихоб кунед

Барои иҷрои ин вазифа, шумо бояд функсияи "Интихоби гурӯҳи чашмакҳоро" дар гурӯҳи "Ҷустуҷӯ ва интихоб" истифода баред.

Пас аз ин, қуттии муколама кушода мешавад, ки дар он шумо бояд ҷузъҳои "Ҳама" ва "Инҳо якхела" -ро дар менюи "Тасдиқи маълумот" интихоб кунед. Банди аввал ҳамаи рӯйхатҳоро интихоб мекунад ва дуюм танҳо онҳоеро интихоб мекунад, ки ба баъзе рӯйхатҳо монанданд.