Арга 1. Томъёо
Дулаацахын тулд хамгийн энгийн сонголт болох томъёогоор эхэлцгээе. Хэрэв бид огноогоор эрэмбэлсэн жижиг хүснэгттэй бол тусдаа баганад ажиллаж байгаа нийт дүнг тооцоолохын тулд бидэнд энгийн томъёо хэрэгтэй:
Энд гол онцлог нь SUM функц дотор мужийг зальтай засах явдал юм - хүрээний эхлэлийг үнэмлэхүй (долларын тэмдэгтэй), төгсгөлд нь харьцангуй (долларгүй) болгосон. Үүний дагуу томьёог бүхэлд нь багананд хуулахдаа бид нийлбэрийг нь тооцдог өргөтгөх мужийг авдаг.
Энэ аргын сул талууд нь мэдээжийн хэрэг:
- Хүснэгтийг огноогоор нь эрэмбэлсэн байх ёстой.
- Өгөгдөл бүхий шинэ мөр нэмэх үед томъёог гараар сунгах шаардлагатай болно.
Арга 2. Пивот хүснэгт
Энэ арга нь арай илүү төвөгтэй боловч илүү тааламжтай байдаг. Үүнийг улам хүндрүүлэхийн тулд илүү ноцтой асуудлыг авч үзье - огнооны баганаар эрэмбэлэхгүй, харин давталттай (өөрөөр хэлбэл бид нэг өдөрт хэд хэдэн удаа зарж болно) 2000 мөр өгөгдлийн хүснэгтийг авч үзье.
Бид анхны хүснэгтээ "ухаалаг" (динамик) гарын товчлол болгон хувиргадаг Ctrl+T эсвэл баг Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах), дараа нь бид үүн дээр командын тусламжтайгаар пивот хүснэгтийг байгуулна Оруулах - Пивот хүснэгт (Оруулах - Пивот хүснэгт). Бид хураангуй хэсэгт мөрийн хэсэгт огноо, мөн утгын хэсэгт зарагдсан барааны тоог оруулна.
Хэрэв танд Excel-ийн хуучин хувилбар байхгүй бол огноог автоматаар жил, улирал, сараар бүлэглэнэ гэдгийг анхаарна уу. Хэрэв танд өөр бүлэглэл хэрэгтэй бол (эсвэл огт хэрэггүй) дурын огноо дээр хулганы баруун товчийг дараад командыг сонгох замаар үүнийг засах боломжтой. Бүлэглэх / Бүлгийг задлах (Бүлэглэх / Бүлэглэх).
Хэрэв та үр дүнгийн нийлбэрийг цэгээр болон ажиллаж байгаа нийлбэрийг хоёуланг нь тусад нь баганад харахыг хүсвэл талбарыг утгын талбарт оруулах нь зүйтэй юм. Зарагдсан талбарын хуулбарыг дахин авахын тулд бид гүйлтийн дүнгийн дэлгэцийг асаах болно. Үүнийг хийхийн тулд талбар дээр хулганы баруун товчийг дараад командыг сонгоно уу Нэмэлт тооцоолол – Хуримтлагдсан нийт (Утгыг - Ажиллаж буй нийт дүнгээр харуулах):
Тэнд та нийт дүнг хувиар нэмэгдүүлэх сонголтыг сонгож болох бөгөөд дараагийн цонхонд хуримтлал гарах талбарыг сонгох хэрэгтэй - манай тохиолдолд энэ нь огнооны талбар юм.
Энэ аргын давуу талууд:
- Их хэмжээний өгөгдлийг хурдан уншдаг.
- Томьёог гараар оруулах шаардлагагүй.
- Эх сурвалжийн өгөгдлийг өөрчлөхдөө хулганы баруун товчийг дарж эсвэл Data – Refresh All командыг ашиглан хураангуйг шинэчлэхэд хангалттай.
Сул тал нь энэ нь хураангуй учраас та хүссэн бүхнээ хийж чадахгүй (мөр оруулах, томьёо бичих, диаграмм зурах гэх мэт) цаашид ажиллахгүй болно гэсэн үг юм.
Арга 3: Power Query
"Ухаалаг" хүснэгтээ эх сурвалжийн өгөгдөл бүхий тушаалыг ашиглан Power Query query засварлагч руу ачаалцгаая Өгөгдөл - Хүснэгтээс/Мужаас (Өгөгдөл - Хүснэгтээс/Мужаас). Дашрамд хэлэхэд, Excel-ийн хамгийн сүүлийн хувилбаруудад үүнийг өөрчилсөн - одоо үүнийг нэрлэдэг Навчтай (Хуудаснаас):
Дараа нь бид дараах алхмуудыг гүйцэтгэнэ.
1. Хүснэгтийг огнооны баганаар өсөх дарааллаар эрэмбэлэх Өсөхөөр эрэмбэлэх Хүснэгтийн толгой хэсэгт байрлах шүүлтүүрийн унждаг жагсаалтад.
2. Хэсэг хугацааны дараа гүйлгээний нийлбэрийг тооцоолохын тулд бид эгнээний дугаар бүхий туслах багана хэрэгтэй. Үүнийг тушаалаар нэмж оруулъя Багана нэмэх - Индекс багана - 1-ээс (Багана нэмэх - Индекс багана - 1-ээс).
3. Мөн ажиллаж байгаа нийт дүнг тооцоолохын тулд баганын лавлагаа хэрэгтэй Зарагдсан, бидний нэгтгэсэн өгөгдөл хаана байна. Power Query-д багануудыг жагсаалт (жагсаалт) гэж нэрлэдэг бөгөөд үүнтэй холбогдох холбоосыг авахын тулд баганын толгой дээр хулганы баруун товчийг дараад командыг сонгоно уу. Илүү дэлгэрэнгүй (Дэлгэрэнгүйг харуулах). Бидэнд хэрэгтэй илэрхийлэл нь өмнөх алхамын нэрээс бүрдэх томьёоны мөрөнд гарч ирнэ #"Индекс нэмсэн", бид хүснэгт болон баганын нэрийг хаанаас авдаг [Борлуулалт] Энэ хүснэгтээс дөрвөлжин хаалтанд:
Цаашид ашиглахын тулд энэ илэрхийллийг санах ойд хуулна уу.
4. Шаардлагагүй бусад сүүлийн алхамыг устгана уу Зарагдсан ба оронд нь тушаалаар ажиллаж байгаа нийлбэрийг тооцоолох тооцоолсон баганыг нэмнэ Багана нэмэх - Тусгай багана (Багана нэмэх - Тусгай багана). Бидэнд хэрэгтэй томъёо дараах байдлаар харагдах болно.
Энд функц байна Жагсаалт.Муж анхны жагсаалтыг авна (багана [Борлуулалт]) ба түүнээс элементүүдийг эхнийхээс нь гаргаж авдаг (томьёонд энэ нь 0 байна, учир нь Power Query дахь дугаарлалт тэгээс эхэлдэг). Татаж авах элементүүдийн тоо нь баганаас авсан мөрийн дугаар юм [Индекс]. Тиймээс эхний эгнээний энэ функц нь зөвхөн баганын эхний нэг нүдийг буцаана Зарагдсан. Хоёр дахь мөрөнд аль хэдийн эхний хоёр нүд, гурав дахь мөрөнд эхний гурав гэх мэт.
За тэгээд функц Жагсаалт олборлосон утгуудын нийлбэр ба бид мөр бүрт өмнөх бүх элементүүдийн нийлбэрийг, өөрөөр хэлбэл хуримтлагдсан нийлбэрийг авна.
Бидэнд хэрэггүй болсон Индекс баганыг устгаад үр дүнг Нүүр хуудас – Хаах & Ачаалах командын тусламжтайгаар Excel рүү буцаан байршуулах л үлдлээ.
Асуудал шийдэгддэг.
Хурдан ба уур хилэн
Зарчмын хувьд үүнийг зогсоож болох байсан ч тосонд жижиг ялаа байдаг - бидний бүтээсэн хүсэлт яст мэлхийн хурдаар ажилладаг. Жишээлбэл, миний хамгийн сул биш компьютер дээр ердөө 2000 мөр бүхий хүснэгтийг 17 секундын дотор боловсруулдаг. Хэрэв илүү их өгөгдөл байвал яах вэ?
Түргэсгэхийн тулд та List.Buffer тусгай функцийг ашиглан буферлэлтийг ашиглаж болох бөгөөд энэ нь түүнд аргумент болгон өгсөн жагсаалтыг (жагсаалт) RAM-д ачаалдаг бөгөөд энэ нь ирээдүйд хандах хандалтыг ихээхэн хурдасгадаг. Манай тохиолдолд 2000 мөртэй хүснэгтийн мөр бүрт ажиллаж байгаа нийлбэр дүнг тооцохдоо Power Query-д хандах ёстой #"Нэмэгдсэн индекс"[Боруулсан] жагсаалтыг буфер болгох нь утга учиртай.
Үүнийг хийхийн тулд үндсэн таб дээрх Power Query засварлагчийн Advanced Editor товчийг дарж (Нүүр хуудас - Нарийвчилсан засварлагч) Power Query-д суулгасан M хэл дээрх бидний асуулгын эх кодыг нээнэ үү.
Тэгээд тэнд хувьсагчтай мөр нэмнэ Миний жагсаалт, утгыг буферийн функцээр буцаадаг бөгөөд дараагийн алхамд бид жагсаалтын дуудлагыг энэ хувьсагчаар солино.
Эдгээр өөрчлөлтийг хийсний дараа бидний асуулга илүү хурдан болж, 2000 мөртэй хүснэгтийг ердөө 0.3 секундын дотор даван туулах болно!
Өөр нэг зүйл, тийм үү? 🙂
- Парето диаграм (80/20) ба үүнийг Excel дээр хэрхэн бүтээх талаар
- Текст доторх түлхүүр үг хайх ба Power Query дахь асуулгын буфер