Tanlangan katak bo'yicha diagramma

Aytaylik, siz va men 2021 yilda turli mamlakatlarda avtomobil sotish qiymatlari bilan quyidagi jadvaldagi ma'lumotlarni ko'rishimiz kerak (haqiqiy ma'lumotlar shu yerdan olingan):

Ma'lumotlar seriyalari (mamlakatlar) soni ko'p bo'lganligi sababli, ularning barchasini bir vaqtning o'zida bitta grafikaga to'plashga urinish yoki dahshatli "spagetti diagrammasi" ga olib keladi yoki har bir seriya uchun alohida jadvallarni tuzishga olib keladi, bu juda mashaqqatli.

Ushbu muammoni hal qilishning oqlangan yechimi diagrammani faqat joriy qatordagi ma'lumotlarga, ya'ni faol katak joylashgan qatorga chizish bo'lishi mumkin:

Buni amalga oshirish juda oson - sizga faqat ikkita formula va 3 qatorda bitta kichik so'l kerak bo'ladi.

1-qadam. Joriy qator raqami

Bizga kerak bo'lgan birinchi narsa bu bizning faol hujayramiz joylashgan varaqdagi qator raqamini hisoblaydigan nomli diapazondir. Yorliqda ochish Formulalar - ism menejeri (Formulalar - ism menejeri), tugmasini bosing yaratish (Yaratmoq) va u erda quyidagi tuzilmani kiriting:

2-qadam. Sarlavhaga havola

Tanlangan mamlakatni diagrammaning sarlavhasi va lejantasida ko'rsatish uchun birinchi ustundan uning (mamlakat) nomi bilan yacheykaga havola olishimiz kerak. Buning uchun biz boshqa mahalliy (ya'ni maydon = Kitob emas, joriy varaq!) quyidagi formula bilan nomlangan diapazon:

Bu yerda INDEX funksiyasi berilgan diapazondan (bizning imzolagan mamlakatlarimiz joylashgan A ustuni) biz avval aniqlagan qator raqamiga ega katakchani tanlaydi.

Qadam 3. Ma'lumotlarga havola

Endi shunga o'xshash tarzda, faol katak hozir joylashgan joriy qatordagi barcha savdo ma'lumotlari bilan diapazonga havolani olamiz. Quyidagi formula bilan boshqa nomli diapazon yarating:

Bu erda nolga teng bo'lgan uchinchi argument INDEX ning bitta qiymatni emas, balki butun qatorni qaytarishiga olib keladi.

Qadam 4. Grafikdagi havolalarni almashtirish

Endi jadval sarlavhasini va ma'lumotlar (diapazon) bilan birinchi qatorni tanlang va ulardan foydalanib diagramma tuzing Qo'shish - Diagrammalar (Qo'shish - diagrammalar). Agar siz diagrammadagi ma'lumotlarga ega qatorni tanlasangiz, u holda funktsiya formulalar qatorida ko'rsatiladi Row (SERIAL) Excel har qanday diagramma yaratishda asl ma'lumotlar va teglarga murojaat qilish uchun avtomatik ravishda foydalanadigan maxsus funktsiyadir:

Keling, ushbu funktsiyadagi birinchi (imzo) va uchinchi (ma'lumotlar) argumentlarini 2 va 3-bosqichlardagi diapazonlarimiz nomlari bilan ehtiyotkorlik bilan almashtiramiz:

Grafik joriy qatordan savdo ma'lumotlarini ko'rsatishni boshlaydi.

5-qadam. Qayta hisoblash makrosi

Yakuniy teginish qoladi. Microsoft Excel formulalarni faqat varaqdagi ma'lumotlar o'zgarganda yoki tugma bosilganda qayta hisoblaydi F9, va biz qayta hisoblash tanlov o'zgarganda, ya'ni faol katak varaq bo'ylab ko'chirilganda sodir bo'lishini xohlaymiz. Buning uchun ishchi daftarimizga oddiy makros qo'shishimiz kerak.

Ma'lumotlar varag'i yorlig'ini o'ng tugmasini bosing va buyruqni tanlang manba (Manba kodi). Ochilgan oynada tanlovni o'zgartirish hodisasi uchun so'l ishlov beruvchi kodini kiriting:

Osonlik bilan tasavvur qilganingizdek, faol hujayraning joylashuvi har doim o'zgarganda varaqni qayta hisoblashni ishga tushiradi.

Qadam 6. Joriy chiziqni ajratib ko'rsatish

Aniqlik uchun siz diagrammada hozirda ko'rsatilgan mamlakatni ajratib ko'rsatish uchun shartli formatlash qoidasini ham qo'shishingiz mumkin. Buning uchun jadvalni tanlang va tanlang Bosh sahifa — Shartli formatlash — Qoida yaratish — Formatlanadigan kataklarni aniqlash uchun formuladan foydalaning (Uy — Shartli formatlash — Yangi qoida — Qaysi hujayralar formatlanishini aniqlash uchun formuladan foydalaning):

Bu erda formula jadvaldagi har bir katak uchun uning satr raqami TekRow o'zgaruvchisida saqlangan raqamga mos kelishini tekshiradi va agar mos keladigan bo'lsa, tanlangan rang bilan to'ldirish boshlanadi.

Bu oddiy va chiroyli, shunday emasmi?

Eslatmalar

• Katta stollarda bu go'zallikning barchasi sekinlashishi mumkin - shartli formatlash resurslarni talab qiladigan narsadir va har bir tanlov uchun qayta hisoblash ham og'ir bo'lishi mumkin.
• Jadvalning tepasida yoki ostida tasodifan yacheyka tanlanganda diagrammada ma'lumotlar yo'qolishining oldini olish uchun formaning ichki o'rnatilgan IF funksiyalaridan foydalanib TekRow nomiga qo'shimcha chek qo'shishingiz mumkin:

  =AGAR(CELL("satr")<4,AGAR(CELL("satr")>4,CELL("satr")))
  

• Diagrammada belgilangan ustunlarni ajratib ko'rsatish
• Excelda interaktiv diagramma qanday yaratiladi
• Koordinata tanlash