Hisobot va ma'lumotlarni qayta ishlash bilan shug'ullanadiganlar uchun Excel hayotiy xakerlari

2024 Muallif: Malcolm Clapton | [email protected]. Oxirgi o'zgartirilgan: 2024-01-13 03:21

Ushbu postda Mann, Ivanov va Ferber nashriyoti bosh direktorining yordamchisi Renat Shagabutdinov Excelning ba'zi ajoyib hayotiy xakerlari bilan o'rtoqlashadi. Ushbu maslahatlar turli hisobotlar, ma'lumotlarni qayta ishlash va taqdimotlar yaratish bilan shug'ullanadigan har bir kishi uchun foydali bo'ladi.

Ushbu maqola Excelda ishingizni soddalashtirish uchun oddiy usullarni o'z ichiga oladi. Ular, ayniqsa, boshqaruv hisoboti bilan shug'ullanadigan, 1C va boshqa hisobotlardan yuklab olish asosida turli xil tahliliy hisobotlarni tayyorlaydigan, boshqaruv uchun taqdimotlar va diagrammalarni tuzadiganlar uchun foydalidir. Men o'zimni mutlaqo yangilik deb da'vo qilmayman - bu yoki boshqa shaklda, bu usullar forumlarda muhokama qilingan yoki maqolalarda aytib o'tilgan.

Agar kerakli qiymatlar jadvalning birinchi ustunida bo'lmasa, VLOOKUP va HLOOKUP ga oddiy alternativalar: LOOKUP, INDEX + SEARCH

VLOOKUP va HLOOKUP funksiyalari faqat kerakli qiymatlar ma’lumotlarni olishni rejalashtirgan jadvalning birinchi ustunida yoki qatorida bo‘lsa ishlaydi.

Aks holda, ikkita variant mavjud:

1. LOOKUP funksiyasidan foydalaning.

   U quyidagi sintaksisga ega: LOOKUP (qidiruv_qiymati; qidiruv_vektori; natija_vektori). Ammo uning to'g'ri ishlashi uchun view_vector diapazonining qiymatlari o'sish tartibida tartiblangan bo'lishi kerak:

   

2. MATCH va INDEX funksiyalarining kombinatsiyasidan foydalaning.

   MATCH funksiyasi massivdagi elementning tartib raqamini qaytaradi (uning yordami bilan siz qidirilayotgan element jadvalning qaysi qatorida joylashganligini topishingiz mumkin), INDEX funksiyasi esa berilgan raqamga ega massiv elementini qaytaradi (biz buni bilib olamiz). MATCH funksiyasidan foydalanish).

   

   Funktsiya sintaksisi:

   • SEARCH (search_value; search_array; match_type) - bizning holatimiz uchun bizga mos keladigan "aniq moslik" turi kerak, u 0 raqamiga mos keladi.

   • INDEX (massiv; satr_raqami; [ustun_raqami]). Bunday holda, siz ustun raqamini ko'rsatishingiz shart emas, chunki massiv bir qatordan iborat.

Ro'yxatdagi bo'sh kataklarni qanday tezda to'ldirish kerak

Vazifa ustundagi katakchalarni yuqoridagi qiymatlar bilan to'ldirishdir (mavzu nafaqat mavzu bo'yicha kitoblar blokining birinchi qatorida emas, balki jadvalning har bir qatorida bo'lishi uchun):

"Mavzu" ustunini tanlang, "Uy" guruhidagi lentani bosing, "Topish va tanlash" tugmasi → "Yacheykalar guruhini tanlash" → "Bo'sh hujayralar" va formulani kiritishni boshlang (ya'ni, tenglikni qo'ying. belgisi) va yuqoridagi katakka murojaat qiling, shunchaki klaviaturangizdagi yuqoriga oʻqni bosing. Shundan so'ng Ctrl + Enter tugmalarini bosing. Shundan so'ng, siz olingan ma'lumotlarni qiymatlar sifatida saqlashingiz mumkin, chunki formulalar endi kerak emas:

Formuladagi xatolarni qanday topish mumkin

Formulaning alohida qismini hisoblash

Murakkab formulani tushunish uchun (bunda boshqa funktsiyalar funktsiya argumentlari sifatida ishlatiladi, ya'ni ba'zi funktsiyalar boshqalarga joylashtirilgan) yoki undagi xatolar manbasini topish uchun siz ko'pincha uning bir qismini hisoblashingiz kerak. Ikkita oson yo'l bor:

1. Formulaning bir qismini to'g'ridan-to'g'ri formulalar qatorida hisoblash uchun ushbu qismni tanlang va F9 tugmasini bosing:

   

   Ushbu misolda SEARCH funksiyasi bilan bog'liq muammo bor edi - unda argumentlar almashtirildi. Shuni esda tutish kerakki, agar siz funktsiya qismini hisoblashni bekor qilmasangiz va Enter tugmasini bosmasangiz, hisoblangan qism raqam bo'lib qoladi.

2. Tasmadagi Formulalar guruhidagi Formulani hisoblash tugmasini bosing:

   

   Ko'rsatilgan oynada siz formulani bosqichma-bosqich hisoblashingiz va qaysi bosqichda va qaysi funktsiyada xatolik yuzaga kelishini aniqlashingiz mumkin (agar mavjud bo'lsa):

   

Formula nimaga bog'liq yoki nimaga tegishli ekanligini qanday aniqlash mumkin

Formula qaysi hujayralarga bog'liqligini aniqlash uchun lentadagi Formulalar guruhida Ta'sir qiluvchi hujayralar tugmasini bosing:

Hisoblash natijasi nimaga bog'liqligini ko'rsatadigan o'qlar paydo bo'ladi.

Rasmda qizil rang bilan ta'kidlangan belgi ko'rsatilsa, formula boshqa varaqlardagi yoki boshqa kitoblardagi hujayralarga bog'liq:

Unga bosish orqali biz ta'sir qiluvchi hujayralar yoki diapazonlar qayerda joylashganligini aniq ko'rishimiz mumkin:

"Hujayralarga ta'sir qilish" tugmasi yonida "Tobe bo'lgan hujayralar" tugmasi mavjud bo'lib, u xuddi shunday ishlaydi: u formulali faol hujayradan unga bog'liq bo'lgan katakchalarga o'qlarni ko'rsatadi.

Xuddi shu blokda joylashgan "O'qlarni o'chirish" tugmasi bir vaqtning o'zida ta'sir qiluvchi hujayralarga o'qlarni, qaram hujayralarga o'qlarni yoki ikkala turdagi o'qlarni bir vaqtning o'zida olib tashlashga imkon beradi:

Bir nechta varaqlardan hujayra qiymatlarining yig'indisini (sonini, o'rtachasini) qanday topish mumkin

Aytaylik, sizda bir xil turdagi bir nechta varaqlar mavjud bo'lib, siz qo'shish, hisoblash yoki boshqa usulda qayta ishlashni xohlaysiz:

Buning uchun natijani ko'rmoqchi bo'lgan katakka standart formulani kiriting, masalan, SUM (SUM) va qayta ishlashingiz kerak bo'lgan varaqlar ro'yxatidan birinchi va oxirgi varaqlar nomini belgilang. argument ikki nuqta bilan ajratilgan:

Siz "Data1", "Data2", "Data3" varaqlaridan B3 manzilli hujayralar yig'indisini olasiz:

Bu adreslash joylashgan varaqlar uchun ishlaydi izchil … Sintaksis quyidagicha: = FUNCTION (birinchi_roʻyxat: oxirgi_roʻyxat! Diapazon maʼlumotnomasi).

Shablon iboralarini avtomatik ravishda qanday yaratish kerak

Excelda matn bilan ishlashning asosiy tamoyillari va bir nechta oddiy funksiyalardan foydalanib, siz hisobotlar uchun shablonli iboralarni tayyorlashingiz mumkin. Matn bilan ishlashning bir necha tamoyillari:

• Biz matnni & belgisi yordamida birlashtiramiz (uni CONCATENATE funktsiyasi bilan almashtirishingiz mumkin, lekin bu unchalik ma'noga ega emas).
• Matn har doim qo'shtirnoq ichida yoziladi, matnli kataklarga havolalar doimo bo'lmaydi.
• "Qo'shtirnoq" xizmati belgisini olish uchun 32-argument bilan CHAR funksiyasidan foydalaning.

Formulalar yordamida shablonli iborani yaratishga misol:

Natija:

Bunda CHAR funksiyasidan tashqari (tirnoqlarni ko'rsatish uchun) IF funksiyasi qo'llaniladi, bu esa ijobiy savdo tendentsiyasi mavjudligiga qarab matnni o'zgartirish imkonini beradi va TEXT funksiyasidan foydalaniladi. istalgan formatdagi raqam. Uning sintaksisi quyida tavsiflanadi:

TEXT (qiymat; format)

Format qo'shtirnoq ichida ko'rsatilgan, xuddi siz Hujayralarni formatlash oynasida maxsus formatni kiritganingizdek.

Yana murakkab matnlar ham avtomatlashtirilishi mumkin. Mening amaliyotimda “INDICATOR rejaga nisbatan XX ga pasaygan / XX ga ko'tarilgan, asosan FACTOR1 ning XX ga o'sishi / kamayishi, FACTOR2 ning o'sishi / pasayishi hisobiga” formatida boshqaruv hisobotiga uzoq, ammo muntazam izohlarni avtomatlashtirish mavjud edi. YY …” oʻzgaruvchan omillar roʻyxati bilan. Agar siz bunday sharhlarni tez-tez yozsangiz va ularni yozish jarayonini algoritmlash mumkin bo'lsa, hech bo'lmaganda ishning bir qismini tejashga yordam beradigan formula yoki so'l yaratish uchun bir marta jumboq bo'lishga arziydi.

Birlashtirishdan keyin har bir katakda ma'lumotlarni qanday saqlash kerak

Hujayralarni birlashtirganda faqat bitta qiymat saqlanib qoladi. Excel hujayralarni birlashtirishga urinayotganda bu haqda ogohlantiradi:

Shunga ko'ra, agar sizda har bir katakka qarab formula mavjud bo'lsa, u ularni birlashtirgandan so'ng ishlashni to'xtatadi (misolning 3-4 qatorlarida # N / A xato):

Hujayralarni birlashtirish va ularning har biridagi ma'lumotlarni saqlab qolish uchun (ehtimol, siz ushbu mavhum misoldagi kabi formulaga ega bo'lishingiz mumkin; ehtimol siz hujayralarni birlashtirmoqchisiz, lekin kelajakdagi barcha ma'lumotlarni saqlab qo'ying yoki ularni ataylab yashiring), varaqdagi istalgan kataklarni birlashtiring., ularni tanlang va keyin formatlashni birlashtirishingiz kerak bo'lgan hujayralarga o'tkazish uchun Format Painter buyrug'idan foydalaning:

Bir nechta ma'lumot manbalaridan pivotni qanday qurish mumkin

Agar siz bir vaqtning o'zida bir nechta ma'lumotlar manbalaridan pivot yaratishingiz kerak bo'lsa, bunday imkoniyatga ega bo'lgan lenta yoki tezkor kirish paneliga "Pivot jadvali va diagramma ustasi" ni qo'shishingiz kerak bo'ladi.

Buni quyidagi tarzda amalga oshirishingiz mumkin: “Fayl” → “Tanlovlar” → “Tez kirish asboblar paneli” → “Barcha buyruqlar” → “Pivot jadvali va diagramma ustasi” → “Qo‘shish”:

Shundan so'ng, lentada mos keladigan belgi paydo bo'lib, xuddi shu sehrgarni chaqiradigan tugmani bosing:

Uni bosganingizda dialog oynasi paydo bo'ladi:

Unda siz "Bir nechta konsolidatsiya diapazonlarida" bandini tanlashingiz va "Keyingi" tugmasini bosishingiz kerak. Keyingi bosqichda siz "Bir sahifa maydoni yaratish" yoki "Sahifa maydonlarini yaratish" ni tanlashingiz mumkin. Agar siz har bir ma'lumot manbasiga mustaqil ravishda nom berishni istasangiz, ikkinchi elementni tanlang:

Keyingi oynada, pivot quriladigan barcha diapazonlarni qo'shing va ularga nom bering:

Shundan so'ng, oxirgi dialog oynasida pivot jadval hisoboti qaerga joylashtirilishini belgilang - mavjud yoki yangi varaqda:

Pivot jadval hisoboti tayyor. "1-sahifa" filtrida, agar kerak bo'lsa, ma'lumotlar manbalaridan faqat bittasini tanlashingiz mumkin:

B matnida A matnining takrorlanish sonini qanday hisoblash mumkin ("MTS SuperMTS tarifi" - MTS qisqartmasi ikki marta takrorlanishi)

Ushbu misolda A ustuni bir nechta matn qatorlarini o'z ichiga oladi va bizning vazifamiz ularning har birida E1 katagida joylashgan qidiruv matnini necha marta o'z ichiga olganligini aniqlashdir:

Ushbu muammoni hal qilish uchun siz quyidagi funktsiyalardan iborat murakkab formuladan foydalanishingiz mumkin:

1. DLSTR (LEN) - matn uzunligini hisoblaydi, yagona argument matndir. Misol: DLSTR ("mashina") = 6.
2. SUBSTITUTE – matn qatoridagi ma’lum matnni boshqasiga almashtiradi. Sintaksis: SUBSTITUTE (matn; eski_matn; yangi_matn). Misol: SUBSTITUTE (“avtomobil”; “avtomatik”; “”) = “mobil”.
3. UPPER - satrdagi barcha belgilarni bosh harf bilan almashtiradi. Yagona dalil matndir. Misol: UPPER (“mashina”) = “CAR”. Bizga bu funksiya katta harflarni hisobga olmaydigan qidiruvlarni amalga oshirish uchun kerak. Axir, UPPER ("mashina") = UPPER ("Mashina")

Muayyan matn satrining boshqasida paydo bo'lishini topish uchun siz uning asl nusxadagi barcha hodisalarini o'chirib tashlashingiz va natijada paydo bo'lgan satr uzunligini asl bilan solishtirishingiz kerak:

DLSTR (“Tarif MTS Super MTS”) - DLSTR (“Super tarif”) = 6

Va keyin bu farqni biz izlayotgan satr uzunligiga bo'ling:

6 / DLSTR (“MTS”) = 2

"MTS" liniyasi asl nusxaga ikki marta kiritilgan.

Ushbu algoritmni formulalar tilida yozish qoladi (keling, biz izlayotgan hodisalarni "matn" bilan va "izlangan" - bizni sodir bo'lish soni qiziqtiradigan matnni belgilaymiz):

= (DLSTR (matn) -LSTR (O'ZINARISH (YUQORI (matn); UPPER (qidirish), ""))) / DLSTR (qidirish)

Bizning misolimizda formula quyidagicha ko'rinadi:

= (DLSTR (A2) -LSTR (O'RNISH (YUQORI (A2), YUQORI ($ E $ 1), ""))) / DLSTR ($ E $ 1)