Хэрэв та шинэхэн хэрэглэгч биш бол Excel-ийн бүх зүйлийн 99% нь параметрүүд эсвэл шинж чанарууд (талбарууд) баганаар дамждаг, объект, үйл явдлын талаарх мэдээлэл байрладаг босоо хүснэгтүүдтэй ажиллахад зориулагдсан гэдгийг та аль хэдийн анзаарсан байх. мөрөнд. Пивот хүснэгтүүд, дэд нийлбэрүүд, хоёр товшилтоор томьёог хуулах - бүгдийг энэ өгөгдлийн форматад тусгайлан тохируулсан.
Гэсэн хэдий ч үл хамаарах дүрэм байдаггүй бөгөөд нэлээд тогтмол давтамжтайгаар хэвтээ семантик чиглэлтэй хүснэгт эсвэл мөр, баганын ижил жинтэй хүснэгтүүд ажилд тааралдвал яах вэ гэж асуудаг.
Хэрэв Excel хэвтээ байдлаар хэрхэн эрэмбэлэхээ мэддэг хэвээр байвал (командын тусламжтайгаар Өгөгдөл - Эрэмбэлэх - Сонголтууд - Багануудыг эрэмбэлэх), шүүлтүүрийн нөхцөл байдал улам дорддог - Excel-д мөр биш харин багануудыг шүүх хэрэгсэл байхгүй. Тиймээс, хэрэв та ийм даалгавартай тулгарвал янз бүрийн түвшний хүндрэлтэй тойрон гарах арга замыг олох хэрэгтэй болно.
Арга 1. Шинэ FILTER функц
Хэрэв та Excel 2021-ийн шинэ хувилбар эсвэл Excel 365-ийн захиалгатай бол шинээр нэвтрүүлсэн функцийг ашиглах боломжтой. FILTER (ШҮҮЛҮҮР), энэ нь эх өгөгдлийг зөвхөн мөрөөр төдийгүй баганаар шүүж чаддаг. Энэ функцийг ажиллуулахын тулд туслах хэвтээ нэг хэмжээст массивын эгнээ шаардлагатай бөгөөд утга тус бүр (ҮНЭН эсвэл ХУДАЛ) нь хүснэгтийн дараагийн баганыг харуулах, эсвэл эсрэгээр нь нуух эсэхийг тодорхойлдог.
Хүснэгтийнхээ дээр дараах мөрийг нэмж, багана бүрийн статусыг бичье.
- Бид үргэлж эхний ба сүүлчийн баганыг (толгой ба нийлбэр) харуулахыг хүсч байна гэж бодъё, тиймээс тэдгээрийн хувьд массивын эхний болон сүүлчийн нүднүүдэд = ҮНЭН гэсэн утгыг тавьсан.
- Үлдсэн баганын хувьд харгалзах нүднүүдийн агуулга нь функцийг ашиглан бидэнд шаардлагатай нөхцөл байдлыг шалгах томъёо байх болно. И (БА) or OR (ЭСВЭЛ). Жишээлбэл, нийт дүн нь 300-аас 500-ийн хооронд байна.
Үүний дараа зөвхөн функцийг ашиглахад л үлддэг FILTER Дээрх манай туслах массив ҮНЭН утгатай багануудыг сонгохын тулд:
Үүний нэгэн адил та багануудыг өгөгдсөн жагсаалтаар шүүж болно. Энэ тохиолдолд функц нь туслах болно COUNTIF (COUNTIF), энэ нь зөвшөөрөгдсөн жагсаалтын хүснэгтийн толгой хэсгээс дараагийн баганын нэр гарч ирэх тоог шалгадаг:
Арга 2. Ердийн хүснэгтийн оронд пивот хүснэгт
Одоогоор Excel-д зөвхөн пивот хүснэгтийн баганаар хэвтээ шүүлтүүрийг суулгасан байгаа тул хэрэв бид эх хүснэгтээ пивот хүснэгт болгон хөрвүүлж чадвал бид энэхүү суулгасан функцийг ашиглаж болно. Үүнийг хийхийн тулд манай эх хүснэгт дараах нөхцлийг хангасан байх ёстой.
- хоосон ба нэгтгэсэн нүдгүй "зөв" нэг мөртэй толгойн мөртэй байх - эс тэгвээс пивот хүснэгтийг бүтээхэд ажиллахгүй болно;
- мөр, баганын шошгонд давхардсан хуулбарыг агуулж болохгүй - тэдгээр нь хураангуйд зөвхөн өвөрмөц утгуудын жагсаалтад "нурах" болно;
- утгын мужид (мөр ба баганын огтлолцол дээр) зөвхөн тоонуудыг агуулна, учир нь пивот хүснэгт нь тэдгээрт ямар нэгэн нэгтгэх функцийг (нийлбэр, дундаж гэх мэт) ашиглах бөгөөд энэ нь тексттэй ажиллахгүй.
Хэрэв эдгээр бүх нөхцөл хангагдсан бол бидний анхны хүснэгт шиг харагдах пивот хүснэгтийг бүтээхийн тулд үүнийг (анхны хүснэгт) хөндлөн табаас хавтгай (хэвийн болгосон) болгон өргөжүүлэх шаардлагатай болно. Үүнийг хийх хамгийн хялбар арга бол 2016 оноос хойш Excel-д суулгасан өгөгдөл хувиргах хүчирхэг хэрэгсэл болох Power Query нэмэлт юм.
Эдгээр нь:
- Хүснэгтийг "ухаалаг" динамик команд болгон хөрвүүлцгээе Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах).
- Командын тусламжтайгаар Power Query руу ачаалж байна Өгөгдөл - Хүснэгтээс / Хүрээнээс (Өгөгдөл - Хүснэгтээс / Мужаас).
- Бид мөрийг нийт дүнгээр шүүдэг (дүгнэлт нь өөрийн гэсэн нийлбэртэй байх болно).
- Эхний баганын гарчиг дээр хулганы баруун товчийг дараад сонгоно уу Бусад баганыг задлах (Бусад баганыг задлах). Сонгогдоогүй бүх баганыг ажилтны нэр, түүний үзүүлэлтийн утга гэсэн хоёр болгон хувиргадаг.
- Багана руу орсон нийт дүнгээр баганыг шүүж байна шинж чанар.
- Бид командын тусламжтайгаар үүссэн хавтгай (хэвийн) хүснэгтийн дагуу пивот хүснэгтийг бүтээдэг Нүүр хуудас — Хаах ба ачаалах — Хаах ба ачаалах... (Нүүр хуудас — Хаах & Ачаалах — Хаах & Ачаалах...).
Одоо та пивот хүснэгтэд байгаа баганыг шүүх чадварыг ашиглаж болно - нэр, зүйлийн урд байрлах ердийн тэмдэглэгээ. Гарын үсгийн шүүлтүүрүүд (Шошгоны шүүлтүүр) or Утгаар нь шүүдэг (Үнэт шүүлтүүр):
Мэдээжийн хэрэг, өгөгдлийг өөрчлөхдөө та бидний асуулга болон хураангуйг гарын товчлолоор шинэчлэх шаардлагатай болно. Ctrl+Alt+F5 эсвэл баг Өгөгдөл - Бүгдийг шинэчлэх (Өгөгдөл - Бүгдийг шинэчлэх).
Арга 3. VBA дахь макро
Таны харж байгаагаар өмнөх бүх аргууд нь яг шүүх биш юм - бид анхны жагсаалтад байгаа баганыг нуудаггүй, харин эх жагсаалтаас өгөгдсөн багана бүхий шинэ хүснэгт үүсгэдэг. Хэрэв эх өгөгдлийн баганыг шүүх (нуух) шаардлагатай бол өөр арга, тухайлбал макро хэрэгтэй болно.
Хүснэгтийн толгой хэсэгт менежерийн нэр нь А4 шар нүдэнд заасан маскыг хангасан баганыг шүүнэ гэж бодъё, жишээлбэл, "А" үсгээр эхэлдэг (өөрөөр хэлбэл "Анна", "Артур" гэсэн үг. " үр дүнд нь).
Эхний аргын нэгэн адил бид эхлээд туслах муж-мөрийг хэрэгжүүлдэг бөгөөд нүд бүрт шалгуурыг томъёогоор шалгаж, харагдах ба далд баганын хувьд ҮНЭН эсвэл ХУДАЛ логик утгуудыг тус тус харуулах болно.
Дараа нь энгийн макро нэмье. Хуудасны таб дээр хулганы баруун товчийг дараад командыг сонгоно уу Эх сурвалж (Эх код). Дараах VBA кодыг хуулж гарч ирэх цонхонд буулгана уу.
Private Sub Worksheet_Change(ByVal Target As Range) Хэрэв Target.Address = "$A$4" Дараа нь муж дахь нүд бүрийн хувьд("D2:O2") Хэрэв нүд = Үнэн байвал нүд.Бүтэн багана.Hidden = Худал Өөр нүд.Бүтэн багана.Нуугдсан. = True End If Next нүд End If End Sub Үүний логик нь дараах байдалтай байна.
- Ерөнхийдөө энэ бол үйл явдал зохицуулагч юм Ажлын_хуудас өөрчлөх, өөрөөр хэлбэл энэ макро нь одоогийн хуудасны аль ч нүдэнд өөрчлөлт орсон тохиолдолд автоматаар ажиллах болно.
- Өөрчлөгдсөн нүдний лавлагаа үргэлж хувьсагчид байх болно Зорилт.
- Нэгдүгээрт, хэрэглэгч (A4) шалгууртай нүдийг яг өөрчилсөн эсэхийг шалгана - үүнийг оператор хийдэг. if.
- Дараа нь мөчлөг эхэлнэ Тус бүр… багана тус бүрийн ҮНЭН / ХУДАЛ үзүүлэлтийн утгуудаар саарал нүднүүдийг (D2:O2) давтах.
- Хэрэв дараагийн саарал нүдний утга ҮНЭН (үнэн) бол багана нуугдахгүй, эс тэгвээс бид үүнийг нууна (өмч. Нууцлагдмал).
- Office 365-ын динамик массив функцууд: ШҮҮГЭХ, SORT болон UNIC
- Power Query ашиглан олон мөрийн толгойтой пивот хүснэгт
- Макро гэж юу вэ, тэдгээрийг хэрхэн үүсгэх, ашиглах