Пивот хүснэгтүүд нь хүн бүрт тохиромжтой - хурдан тооцоолж, уян хатан байдлаар тохируулагдсан бөгөөд шаардлагатай бол дизайныг гоёмсог байдлаар ороож болно. Гэхдээ тэнд бас цөөн хэдэн ялаа тос, тухайлбал, хураангуй үүсгэх боломжгүй, үнэ цэнэ талбар нь тоо байх ёсгүй, харин текст.
Энэ хязгаарлалтыг даван туулахыг хичээж, ижил төстэй нөхцөл байдалд "хос таяг" гаргаж ирцгээе.
Манай компани бүтээгдэхүүнээ манай улс болон Казахстаны хэд хэдэн хот руу чингэлэгт тээвэрлэдэг гэж бодъё. Савыг сард нэгээс илүүгүй удаа явуулдаг. Контейнер бүр үсэг, тоон дугаартай. Анхны өгөгдлийн хувьд хүргэлтийг жагсаасан стандарт хүснэгт байдаг бөгөөд хот бүр болон сар бүр илгээсэн чингэлэгийн тоог тодорхой харахын тулд та ямар нэгэн хураангуйг хийх хэрэгтэй.
Тохиромжтой болгохын тулд тушаалыг ашиглан анхны өгөгдөл бүхий хүснэгтийг урьдчилан "ухаалаг" болгоё Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах) мөн түүнд нэр өг Хүргэлтийн үйлчилгээ таб байгуулагч (Дизайн). Ирээдүйд энэ нь амьдралыг хялбаршуулах болно, учир нь. хүснэгтийн нэр болон түүний багануудыг томъёонд шууд ашиглах боломжтой болно.
Арга 1. Хамгийн хялбар нь Power Query ашиглах
Power Query бол Excel-д өгөгдлийг ачаалах, өөрчлөх маш хүчирхэг хэрэгсэл юм. Энэхүү нэмэлтийг 2016 оноос хойш Excel-д анхдагч байдлаар суулгасан. Хэрэв танд Excel 2010 эсвэл 2013 байгаа бол тусад нь татаж аваад суулгаж болно (бүрэн үнэ төлбөргүй).
Бүх үйл явцыг тодорхой болгохын тулд би дараах видеон дээр алхам алхмаар дүн шинжилгээ хийсэн.
Хэрэв Power Query-г ашиглах боломжгүй бол пивот хүснэгт эсвэл томьёо ашиглан өөр замаар явж болно.
Арга 2. Туслах хураангуй
Анхны хүснэгтдээ дахиад нэг багана нэмье, энд энгийн томьёо ашиглан хүснэгтийн мөр бүрийн тоог тооцоолно.
Мэдээжийн хэрэг, -1 хэрэгтэй, учир нь бидний хүснэгтэд нэг мөр бүхий толгой байна. Хэрэв таны хүснэгт хуудасны эхэнд байхгүй бол та одоогийн мөр болон хүснэгтийн толгойн тоонуудын зөрүүг тооцоолох арай илүү төвөгтэй, гэхдээ бүх нийтийн томъёог ашиглаж болно.
Одоо стандарт аргаар бид өгөгдөл дээрээ үндэслэн хүссэн төрлийн пивот хүснэгтийг бүтээх боловч утгын талбарт талбарыг орхих болно. Мөрийн дугаар бидний хүссэн зүйлийн оронд сав:
Нэг хотод нэг сард хэд хэдэн чингэлэг байдаггүй тул бидний хураангуй нь үнэндээ бидэнд хэрэгтэй чингэлэгийн хэмжээг биш, харин мөрийн дугаарыг өгөх болно.
Нэмж хэлэхэд, та таб дээрх ерөнхий болон дэд нийлбэрүүдийг унтрааж болно Бүтээгч - Ерөнхий нийлбэр и Дэд дүн (Дизайн - Их нийт дүн, дэд нийлбэр) мөн ижил газар товчийг ашиглан хураангуйг илүү тохиромжтой хүснэгтийн зохион байгуулалт руу шилжүүлээрэй Загварыг мэдээлэх (Тайлангийн байршил).
Тиймээс, бид үр дүнд хүрэхийн тулд аль хэдийн тал руугаа явж байна: бид хот, сарын уулзвар дээр эх хүснэгтийн мөрийн дугаартай, бидэнд хэрэгтэй контейнер код байрладаг хүснэгттэй байна.
Одоо хураангуйг (ижил эсвэл өөр хуудсанд) хуулж, утгууд болгон буулгаж, дараа нь утгын талбарт томъёогоо оруулаад хураангуйгаас олдсон мөрийн дугаараар контейнерийн кодыг гаргаж авна.
Чиг үүрэг IF (Хэрэв), энэ тохиолдолд хураангуйн дараагийн нүд хоосон биш эсэхийг шалгана. Хэрэв хоосон бол "" гэсэн хоосон текстийн мөр гарна, өөрөөр хэлбэл нүдийг хоосон орхино. Хэрэв хоосон биш бол баганаас гаргаж авна уу чингэлэг эх хүснэгт Хүргэлтийн үйлчилгээ функцийг ашиглан мөрийн дугаараар эсийн контент INDEX (INDEX).
Магадгүй энд тийм ч тодорхой бус цорын ганц зүйл бол давхар үг юм чингэлэг томъёонд. Ийм хачирхалтай бичих хэлбэр:
Хангамж[[Сав]:[Сав]]
… зөвхөн баганыг лавлахад шаардлагатай чингэлэг үнэмлэхүй байсан (энгийн "ухаалаг биш" хүснэгтэд зориулсан $ тэмдэгтэй лавлагаа гэх мэт) бөгөөд бидний томъёог баруун тийш хуулахдаа хөрш зэргэлдээ баганууд руу гулсаагүй.
Цаашид эх хүснэгтийн өгөгдлийг өөрчлөх үед Хүргэлтийн үйлчилгээ, бид туслах хураангуйгаа хулганы баруун товчийг дараад командыг сонгох замаар мөрийн дугаараар шинэчлэхээ санах хэрэгтэй. Шинэчлэх & Хадгалах (Сэргээх).
Арга 3. Томъёо
Энэ арга нь завсрын пивот хүснэгт үүсгэх, гараар шинэчлэх шаардлагагүй боловч Excel-ийн "хүнд зэвсэг" функцийг ашигладаг. SUMMESLIMN (SUMIFS). Мөрийн дугаарыг хураангуйгаар хайхын оронд та дараах томъёог ашиглан тооцоолж болно.
Зарим гадаад том хэмжээтэй бол үнэндээ энэ нь сонгомол нийлбэр функцийг ашиглах стандарт тохиолдол юм SUMMESLIMNТухайн хот, сарын эгнээний дугаарыг нэгтгэсэн A. Дахин хэлэхэд, нэг сард нэг хотод хэд хэдэн чингэлэг байдаггүй тул манай функц үнэн хэрэгтээ хэмжээг биш, харин шугамын дугаарыг өөрөө өгөх болно. Дараа нь өмнөх аргаас аль хэдийн танил болсон функц INDEX Та мөн контейнер кодыг гаргаж авах боломжтой:
Мэдээжийн хэрэг, энэ тохиолдолд та хураангуйг шинэчлэх талаар бодох шаардлагагүй, харин том хүснэгтүүд дээр функц СУММЕСЛИ мэдэгдэхүйц удаашралтай байж болно. Дараа нь та томъёоны автомат шинэчлэлтийг унтраах эсвэл эхний аргыг ашиглах хэрэгтэй болно - пивот хүснэгт.
Хэрэв хураангуйн харагдах байдал таны тайланд тохиромжгүй бол та үүнээс мөрийн дугаарыг бидний хийсэн шиг шууд биш, харин функцийг ашиглан эцсийн хүснэгтэд гаргаж болно. PIVOT.ХҮСНЭГТ.МЭДЭЭЛЭЛ АВАХ (GET.PIVOT.DATA). Үүнийг хэрхэн хийх талаар эндээс олж болно.
- Пивот хүснэгт ашиглан хэрхэн тайлан гаргах вэ
- Пивот хүснэгтэд тооцооллыг хэрхэн хийх вэ
- SUMIFS, COUNTIFS гэх мэтээр сонгон тоолох.