Excel дээр унадаг жагсаалтыг хэрхэн хийх вэ

Унждаг жагсаалт нь мэдээлэлтэй ажиллахад илүү тохь тухтай болгоход туслах гайхалтай хэрэгтэй хэрэгсэл юм. Энэ нь бусадтай адил ажиллах боломжтой хэд хэдэн утгыг нэг нүдэнд нэг дор багтаах боломжийг олгодог. Танд хэрэгтэй нэгийг нь сонгохын тулд сумны дүрс дээр товшоод дараа нь uXNUMXbuXNUMXbis утгуудын жагсаалт гарч ирнэ. Тодорхой нэгийг сонгосны дараа нүдийг автоматаар дүүргэх бөгөөд үүн дээр үндэслэн томъёог дахин тооцоолно.

Excel нь унждаг цэс үүсгэх олон янзын аргуудыг санал болгодог бөгөөд үүнээс гадна энэ нь танд тэдгээрийг уян хатан байдлаар өөрчлөх боломжийг олгодог. Эдгээр аргуудыг илүү нарийвчлан авч үзье.

Жагсаалт үүсгэх үйл явц

Попап цэс үүсгэхийн тулд "Өгөгдөл" - "Өгөгдлийн баталгаажуулалт" зам дээрх цэсийн зүйлүүдийг дарна уу. "Параметрүүд" табыг олох шаардлагатай харилцах цонх нээгдэж, өмнө нь нээгдээгүй бол үүн дээр дарна уу. Энэ нь маш олон тохиргоотой боловч "Өгөгдлийн төрөл" зүйл нь бидний хувьд чухал юм. Бүх утгын дотроос "Жагсаалт" нь зөв юм.

Попап жагсаалтад мэдээлэл оруулах аргын тоо нэлээд их байна.

1. Нэг харилцах цонхны нэг таб дээр байрлах "Эх сурвалж" талбарт цэг таслалаар тусгаарлагдсан жагсаалтын элементүүдийн бие даасан заалт.
   

   

2. Үнэт зүйлийн урьдчилсан үзүүлэлт. Эх сурвалж талбар нь шаардлагатай мэдээлэл байгаа мужийг агуулна.
   

   

3. Нэрлэсэн мужийг зааж байна. Өмнөх аргыг давтдаг арга, гэхдээ зөвхөн мужийг урьдчилан нэрлэх шаардлагатай.
   

   

Эдгээр аргуудын аль нэг нь хүссэн үр дүнд хүрэх болно. Бодит нөхцөл байдалд унадаг жагсаалт үүсгэх аргуудыг авч үзье.

Жагсаалтын өгөгдөл дээр үндэслэсэн

Янз бүрийн жимсний төрлийг тодорхойлсон хүснэгт бидэнд байна гэж бодъё.

Энэ мэдээллийн багц дээр үндэслэн доош унах цэсэнд жагсаалт үүсгэхийн тулд та дараах зүйлийг хийх хэрэгтэй.

1. Ирээдүйн жагсаалтад зориулж нөөцлөгдсөн нүдийг сонгоно уу.
2. Тууз дээрх Өгөгдлийн табыг олоорой. Тэнд бид "Өгөгдлийг шалгах" дээр дарна уу.
   

   

3. "Өгөгдлийн төрөл" гэсэн зүйлийг олоод утгыг "Жагсаалт" руу шилжүүлнэ үү.
   

   

4. "Эх сурвалж" сонголтыг харуулсан талбарт хүссэн мужаа оруулна уу. Жагсаалтыг хуулах үед мэдээлэл шилжихгүйн тулд үнэмлэхүй лавлагааг зааж өгөх ёстой гэдгийг анхаарна уу.
   

Үүнээс гадна нэгээс олон нүдэнд нэг дор жагсаалт үүсгэх функц байдаг. Үүнд хүрэхийн тулд та бүгдийг нь сонгоод, өмнө дурдсантай ижил алхмуудыг хийх хэрэгтэй. Дахин хэлэхэд та үнэмлэхүй лавлагаа бичигдсэн эсэхийг шалгах хэрэгтэй. Хэрэв хаяг нь багана, мөрийн нэрний хажууд долларын тэмдэггүй бол багана, мөрийн нэрний хажууд $ тэмдэг байх хүртэл F4 товчийг дарж нэмэх шаардлагатай.

Гарын авлагын өгөгдөл бичлэгийн тусламжтайгаар

Дээрх нөхцөл байдалд шаардлагатай мужийг тодруулж жагсаалтыг бичсэн. Энэ нь тохиромжтой арга боловч заримдаа өгөгдлийг гараар бүртгэх шаардлагатай болдог. Энэ нь ажлын дэвтэр дэх мэдээллийн давхардлаас зайлсхийх боломжтой болно.

Бид тийм, үгүй ​​гэсэн хоёр боломжит сонголтыг агуулсан жагсаалт үүсгэх даалгавартай тулгарлаа гэж бодъё. Даалгаврыг биелүүлэхийн тулд дараахь зүйлийг хийх шаардлагатай.

1. Жагсаалтыг харахын тулд нүдэн дээр дарна уу.
2. "Өгөгдөл" хэсгийг нээгээд бидэнд танил болсон "Өгөгдөл шалгах" хэсгийг олоорой.
   

   

3. Дахин хэлэхэд "Жагсаалт" төрлийг сонгоно уу.
   

   

4. Энд та "Тийм; Үгүй” гэж бичжээ. Мэдээллийг тоолохдоо цэг таслал ашиглан гараар оруулдаг болохыг бид харж байна.

OK дарсны дараа бид дараах үр дүнг гаргана.

Дараа нь програм нь тохирох нүдэнд унах цэсийг автоматаар үүсгэх болно. Гарч ирэх жагсаалтад хэрэглэгчийн тодорхойлсон бүх мэдээлэл. Хэд хэдэн нүдэнд жагсаалт үүсгэх дүрмүүд нь өмнөхтэй төстэй бөгөөд зөвхөн цэгтэй таслал ашиглан мэдээллийг гараар зааж өгөх ёстойг эс тооцвол.

OFFSET функцийг ашиглан унадаг жагсаалт үүсгэх

Сонгодог аргаас гадна функцийг ашиглах боломжтой ХӨГЖИЛунадаг цэсүүдийг үүсгэх.

Хуудсыг нээцгээе.

Унждаг жагсаалтын функцийг ашиглахын тулд та дараах зүйлийг хийх хэрэгтэй.

1. Ирээдүйн жагсаалтыг байрлуулах сонирхолтой нүдийг сонгоно уу.
2. "Өгөгдөл" таб болон "Өгөгдлийн баталгаажуулалт" цонхыг дарааллаар нээнэ үү.
   

   

3. "Жагсаалт"-ыг тохируулна уу. Энэ нь өмнөх жишээнүүдийн нэгэн адил хийгддэг. Эцэст нь дараахь томъёог ашиглана. =OFFSET(A$2$;0;0;5). Бид үүнийг аргумент болгон ашиглах нүднүүдийг зааж өгсөн газарт оруулна.

Дараа нь програм нь жимсний жагсаалт бүхий цэсийг бий болгоно.

Үүний синтакс нь:

=OFFSET(лавлагаа, шугамын_офсет, баганын_офсет,[өндөр],[өргөн])

Энэ функц нь 5 аргументтай болохыг бид харж байна. Эхлээд офсет хийх эхний нүдний хаягийг өгнө. Дараагийн хоёр аргумент нь хэдэн мөр, баганыг нөхөхийг зааж өгнө. Бидний тухай ярихад, Height аргумент нь жагсаалтын өндрийг илэрхийлдэг тул 5 байна. 

Өгөгдлийн орлуулалт бүхий Excel-ийн унадаг жагсаалт (+ OFFSET функцийг ашиглан)

Өгөгдсөн тохиолдолд ХӨГЖИЛ тогтмол мужид байрлах попап цэс үүсгэхийг зөвшөөрсөн. Энэ аргын сул тал нь тухайн зүйлийг нэмсний дараа томьёог өөрөө засах хэрэгтэй болно.

Шинэ мэдээлэл оруулах дэмжлэг бүхий динамик жагсаалт үүсгэхийн тулд та дараахь зүйлийг хийх ёстой.

1. Сонирхсон нүдийг сонгоно уу.
2. "Өгөгдөл" табыг өргөжүүлж, "Өгөгдлийн баталгаажуулалт" дээр дарна уу.
3. Нээгдсэн цонхон дээр "Жагсаалт" гэсэн зүйлийг дахин сонгоод дараах томъёог өгөгдлийн эх сурвалж болгон зааж өгнө үү. =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. OK дарна уу.

Энэ нь функцийг агуулдаг COUNTIF, хэдэн нүд дүүрсэнийг нэн даруй тодорхойлохын тулд (хэдийгээр энэ нь илүү олон тооны хэрэглээтэй боловч бид үүнийг тодорхой зорилгоор энд бичдэг).

Томъёо хэвийн ажиллахын тулд томъёоны зам дээр хоосон нүд байгаа эсэхийг хянах шаардлагатай. Тэд байх ёсгүй.

Өөр хуудас эсвэл Excel файлын өгөгдөл бүхий унадаг жагсаалт

Хэрэв та өөр баримт бичиг, тэр ч байтугай нэг файлд агуулагдсан хуудаснаас мэдээлэл авах шаардлагатай бол сонгодог арга ажиллахгүй. Үүний тулд функцийг ашигладаг ҮНЭГҮЙ, энэ нь өөр хуудас эсвэл ерөнхийдөө файлд байрлах нүдний холбоосыг зөв форматаар оруулах боломжийг олгодог. Та дараах зүйлийг хийх хэрэгтэй.

1. Жагсаалтыг байрлуулах нүдийг идэвхжүүлнэ үү.
2. Бид аль хэдийн мэддэг цонхоо нээнэ. Бид өмнө нь бусад мужуудын эх сурвалжийг зааж өгсөн газарт томъёог форматаар зааж өгсөн болно =ШУУД БУС(“[Жагсаалт1.xlsx]Хуудас1!$A$1:$A$9”). Мэдээжийн хэрэг, List1 болон Sheet1-ийн оронд та өөрийн ном, хуудасны нэрийг тус тус оруулж болно. 

Анхаар! Файлын нэрийг дөрвөлжин хаалтанд бичнэ. Энэ тохиолдолд Excel нь одоогоор хаалттай байгаа файлыг мэдээллийн эх сурвалж болгон ашиглах боломжгүй болно.

Шаардлагатай баримт бичиг нь жагсаалт оруулах хавтсанд байгаа тохиолдолд л файлын нэр өөрөө утга учиртай гэдгийг тэмдэглэх нь зүйтэй. Хэрэв тийм биш бол та энэ баримт бичгийн хаягийг бүрэн зааж өгөх ёстой.

Хамааралтай уналт үүсгэх

Хараат жагсаалт гэдэг нь өөр жагсаалтад байгаа хэрэглэгчийн сонголтын агуулгад нөлөөлсөн жагсаалт юм. Бидний өмнө гурван мужийг агуулсан, тус бүрд нь нэр өгсөн хүснэгт байна гэж бодъё.

Өөр жагсаалтад сонгосон сонголтын үр дүнд нөлөөлсөн жагсаалтыг гаргахын тулд та эдгээр алхмуудыг дагах хэрэгтэй.

1. Мужийн нэр бүхий 1-р жагсаалтыг үүсгэ.
   

   

2. Эх сурвалжийн нэвтрэх цэг дээр шаардлагатай үзүүлэлтүүдийг нэг нэгээр нь тодруулна.
   

   

3. Тухайн хүний ​​сонгосон ургамлын төрлөөс хамааран 2-р жагсаалтыг гарга. Эсвэл, хэрэв та эхний жагсаалтад модыг зааж өгсөн бол хоёр дахь жагсаалтад "царс, эвэр, хүрэн" гэх мэт мэдээлэл орно. Өгөгдлийн эх сурвалжийг оруулах газарт томъёог бичих шаардлагатай =ШУУД БУС(E3). E3 – 1 мужын нэрийг агуулсан нүд.=ШУУД (E3). E3 – жагсаалтын нэр бүхий нүд 1.

Одоо бүх зүйл бэлэн боллоо.

Унждаг жагсаалтаас олон утгыг хэрхэн сонгох вэ?

Заримдаа зөвхөн нэг утгад давуу эрх олгох боломжгүй байдаг тул нэгээс илүүг сонгох шаардлагатай. Дараа нь та хуудасны кодонд макро нэмэх хэрэгтэй. Alt + F11 товчлуурын хослолыг ашиглан Visual Basic Editor програмыг нээнэ. Мөн кодыг тэнд оруулсан болно.

Хувийн дэд ажлын хуудасны_Өөрчлөлт (Хүрээ болгон өөрчилсөн зорилт)

    Алдааг үргэлжлүүлнэ үү

    Хэрвээ огтлолцоогүй бол(Зорилтот, хүрээ(«Е2:Е9»)) нь юу ч биш ба зорилт.Эс.Тоо = 1 Дараа нь

        Application.EnableEvents = Худал

        Хэрэв Len (Target.Offset (0, 1)) = 0 Дараа нь

            Target.Offset (0, 1) = Зорилтот

        бас

            Target.End (xlToRight) .Offset (0, 1) = Зорилтот

        Хэрэв үгүй ​​бол

        Target.ClearContents

        Application.EnableEvents = Үнэн

    Хэрэв үгүй ​​бол

Төгсгөл дэд 

Нүднүүдийн агуулгыг доор харуулахын тулд бид дараах кодыг засварлагч руу оруулна.

Хувийн дэд ажлын хуудасны_Өөрчлөлт (Хүрээ болгон өөрчилсөн зорилт)

    Алдааг үргэлжлүүлнэ үү

    Хэрэв огтлолцоогүй бол(Зорилтот, хүрээ(«Н2:К2»)) нь юу ч биш ба зорилт юм.Эс.Тоо = 1 Дараа нь

        Application.EnableEvents = Худал

        Хэрэв Len (Target.Offset (1, 0)) = 0 Дараа нь

            Target.Offset (1, 0) = Зорилтот

        бас

            Target.End (xlDown) .Offset (1, 0) = Зорилтот

        Хэрэв үгүй ​​бол

        Target.ClearContents

        Application.EnableEvents = Үнэн

    Хэрэв үгүй ​​бол

Төгсгөл дэд

Эцэст нь хэлэхэд, энэ кодыг нэг нүдэнд бичихэд ашигладаг.

Хувийн дэд ажлын хуудасны_Өөрчлөлт (Хүрээ болгон өөрчилсөн зорилт)

    Алдааг үргэлжлүүлнэ үү

    Хэрэв огтлолцоогүй бол(Зорилтот, хүрээ(«C2:C5»)) нь юу ч биш ба зорилт.Эс.Тоо = 1 Дараа нь

        Application.EnableEvents = Худал

        newVal = Зорилтот

        Програм. Буцаах

        oldval = Зорилтот

        Хэрэв Len (oldval) <> 0 Мөн oldval <> newVal Дараа нь

            Зорилтот = Зорилтот & «,» & newVal

        бас

            Зорилтот = newVal

        Хэрэв үгүй ​​бол

        Хэрэв Len (newVal) = 0 бол Target.ClearContents

        Application.EnableEvents = Үнэн

    Хэрэв үгүй ​​бол

Төгсгөл дэд

Хүрээг засах боломжтой.

Хайлтын тусламжтайгаар унадаг жагсаалтыг хэрхэн гаргах вэ?

Энэ тохиолдолд та эхлээд өөр төрлийн жагсаалтыг ашиглах ёстой. "Хөгжүүлэгч" таб нээгдэх бөгөөд үүний дараа та "Insert" - "ActiveX" элемент дээр товших эсвэл товших хэрэгтэй (дэлгэц мэдрэгчтэй бол). Энэ нь хосолсон хайрцагтай. Та энэ жагсаалтыг зурахыг хүсэх бөгөөд үүний дараа энэ нь баримт бичигт нэмэгдэх болно.

Цаашилбал, ListFillRange сонголтод мужийг заасан шинж чанаруудаар дамжуулан тохируулдаг. Хэрэглэгчийн тодорхойлсон утгыг харуулах нүдийг LinkedCell сонголтыг ашиглан тохируулна. Дараа нь програм автоматаар боломжит утгыг санал болгох тул та эхний тэмдэгтүүдийг бичих хэрэгтэй.

Мэдээллийг автоматаар солих боломжтой унадаг жагсаалт

Өгөгдлийг мужид нэмсний дараа автоматаар орлуулах функц бас бий. Үүнийг хийхэд хялбар:

1. Ирээдүйн жагсаалтад зориулж нүднүүдийн багц үүсгэ. Манай тохиолдолд энэ нь өнгөний багц юм. Бид үүнийг сонгодог.
   

   

2. Дараа нь хүснэгт хэлбэрээр форматлах шаардлагатай. Та ижил нэртэй товчлуур дээр дарж хүснэгтийн хэв маягийг сонгох хэрэгтэй.
   

   

   

Дараа нь та "OK" товчийг дарж энэ хүрээг баталгаажуулах хэрэгтэй.

Бид үүссэн хүснэгтийг сонгоод А баганын дээд талд байрлах оролтын талбараар дамжуулан нэр өгнө.

Энэ бол хүснэгт байгаа бөгөөд үүнийг доош буулгах жагсаалтын үндэс болгон ашиглаж болох бөгөөд үүнд танд хэрэгтэй болно:

1. Жагсаалт байрлах нүдийг сонгоно уу.
2. Өгөгдлийн баталгаажуулалтын харилцах цонхыг нээнэ үү.
   

   

3. Бид өгөгдлийн төрлийг "Жагсаалт" болгож, утгын хувьд = тэмдгээр дамжуулан хүснэгтийн нэрийг өгдөг.
   

   

   

Бүх зүйл, нүд бэлэн болсон бөгөөд бидэнд анх хэрэгтэй байсан шиг өнгөний нэрийг харуулсан болно. Одоо та шинэ байрлалуудыг сүүлчийнхээс арай доогуур байрлах нүдэнд бичээд л нэмж болно.

Хүснэгтийн давуу тал нь шинэ өгөгдөл нэмэгдэхэд хүрээ автоматаар нэмэгддэг. Үүний дагуу энэ нь жагсаалт нэмэх хамгийн тохиромжтой арга юм.

Унждаг жагсаалтыг хэрхэн хуулах вэ?

Хуулбарлахын тулд Ctrl + C ба Ctrl + V товчлуурын хослолыг ашиглахад хангалттай. Тиймээс унждаг жагсаалтыг форматтай хамт хуулах болно. Форматыг арилгахын тулд та тусгай оо ашиглах хэрэгтэй (контекст цэсэнд, энэ сонголт нь жагсаалтыг хуулж авсны дараа гарч ирнэ), тэнд "утгад хамаарах нөхцөл" гэсэн сонголтыг тохируулсан болно.

Унждаг жагсаалт агуулсан бүх нүдийг сонгоно уу

Энэ даалгаврыг биелүүлэхийн тулд та "Ол, сонгох" бүлгийн "Бүлэг нүдийг сонгох" функцийг ашиглах ёстой.

Үүний дараа харилцах цонх нээгдэх бөгөөд та "Өгөгдлийн баталгаажуулалт" цэсний "Бүгд" ба "Эдгээр" гэсэн зүйлийг сонгох хэрэгтэй. Эхний зүйл нь бүх жагсаалтыг сонгох бөгөөд хоёр дахь нь зөвхөн тодорхой жагсаалттай төстэй зүйлийг сонгоно.