Bir necha omborning Excel fayllarini bitta ro'yxatga birlashtirish

Uchta omborda uchta xodim o'z Excel faylini yuritadi. Birinchisida ustun «Soni» deb nomlangan, ikkinchisida «Qoldiq», uchinchisida «Kol-vo». Birida tovar nomi bosh harf bilan, boshqasida hammasi katta harfda yozilgan, birlik esa kimda dona, kimda quti. Buxgalter oy oxirida bu fayllarni qo'lda nusxalab bitta jadvalga yig'adi, xatolar esa shu yerda yig'ilib qoladi. Quyida bir nechta faylni bitta toza ro'yxatga birlashtirishning tartibli yo'li.

Fayllarni yig'ib, ro'yxatga oling

Birlashtirishdan oldin qaysi fayllar borligini aniqlang. Oddiy jadval tuzing: fayl nomi, ombor, kim yuritadi, oxirgi o'zgartirilgan sana, ustunlar ro'yxati. Bir omborning ikki versiyasi topilsa, qaysi biri joriyligini ombor mas'uli tasdiqlasin, hech kim taxmin qilmasin. Har bir fayl bo'yicha uch narsani yozib qo'ying: nechta qator bor, umumiy miqdor qancha, yashirin yoki bo'sh qatorlar bormi. Bu raqamlar birlashtirgandan keyingi tekshiruvga tayanch bo'ladi.

Asl fayllarni alohida «asl» papkaga ko'chiring va ularga tegmang. Ish nusxada boradi, shunda birlashtirishda xato bo'lsa, boshlang'ich holatga qaytish mumkin.

Yagona shablon va manba ustuni

Birlashtirilgan jadvalning ustunlarini oldindan belgilang. Odatda yetarli bo'lgani:

• tovar kodi yoki artikuli;
• tovar nomi;
• o'lchov birligi;
• ombor;
• miqdor;
• narx, agar kerak bo'lsa;
• izoh;
• manba: fayl nomi, varaq va asl qator raqami.

Har bir faylning ustunlarini shu shablonga moslashtiring: «Soni», «Qoldiq» va «Kol-vo» uchalasi ham «miqdor» bo'ladi. Moslik jadvalini alohida saqlang, ertaga to'rtinchi fayl kelganda uni qayta yaratmaysiz.

Manba ustuni muhim. Birlashtirilgan jadvalda ziddiyat chiqsa, aynan shu ustun qatorning asl joyini ko'rsatadi va «bu raqamni kim yozgan?» savoliga javob beradi.

Ustunlarni moslashtirish bilan birga qiymatlarni ham moslashtiring. Birliklar ro'yxatini (dona, quti, kg) belgilang va har bir fayldagi yozilishni shunga keltiring. Omborlar nomini ham yagona yozing: «1-ombor», «Ombor 1» va «Asosiy ombor» uch xil ombor bo'lib qolmasin, aks holda pivot jadvalda bir omborning ostida uchta qator paydo bo'ladi.

Birlashtirish va tekshirish

2–5 ta kichik fayl bo'lsa, qatorlarni yagona shablonga nusxalab qo'yish yetarli. Fayllar ko'p yoki ular har oy yangilansa, Excel'ning Power Query vositasi qulay: «Ma'lumotlar» bo'limidan papkadagi bir xil tuzilishli fayllarni olib, bitta jadvalga birlashtirish mumkin. Sozlash bir marta qilinadi, keyin yangi fayllar yuklanganda jadval yangilanadi.

Birlashtirgandan so'ng yig'indini tekshiring. Har bir manba fayl bo'yicha umumiy miqdorni hisoblab, birlashtirilgan jadvaldagi manba kesimidagi yig'indi bilan solishtiring. Mos kelmasa, qatorlar yo'qolgan yoki ikki marta qo'shilgan. Qatorlar sonini ham solishtiring. Natijani saqlab, fayl nomiga sana qo'ying va keyingi ishni yangi nusxada davom ettiring: shunda har bir bosqichdan orqaga qaytish mumkin.

Ziddiyatlarni hal qilish

Birlashtirilgan jadvalda to'rt xil holat uchraydi:

1. Bir kod, bir ombor, ikki faylda. Bu nusxa bo'lishi mumkin yoki ikki kishi alohida kiritgan bo'lishi mumkin. Raqamlarni avtomatik qo'shmang: ombor mas'uli qaysi biri to'g'ri ekanini hal qilsin. Topish uchun ombor va kodni birlashtirgan kalit bo'yicha COUNTIF ishlating.
2. Bir kod, turli ombor. Bu odatiy holat: ikki qator qoladi, ombor ustuni ularni ajratadi.
3. Bir tovar, turli kod yoki nom. Bu dublikat; uni nomenklaturani tozalashda alohida hal qilinadi.
4. Birliklar farqi. Bir faylda dona, boshqasida quti bo'lsa, birlashtirishdan oldin bitta birlikka keltiring.

Shartli misol: 3 omborning 3 ta faylida jami 2 100 qator bor. Birlashtirgandan keyin ombor va kod bo'yicha 140 qator takrorlangan bo'lib chiqdi, yana 85 ta kod turli nom bilan yozilgan edi. Takrorlar hal qilingach, 1 960 ta noyob qator qoldi va uchala fayl bo'yicha miqdorlar yig'indisi manba kesimida mos tushdi.

Xulosa

Excel fayllarini birlashtirish uchun nusxa, yagona shablon, manba ustuni va yig'indi tekshiruvi yetarli, qolgan qiyinchilik ziddiyatlarni to'g'ri hal qilishda. SkladSoft turli ombor va xodimlarning Excel fayllarini bitta toza ro'yxatga yig'adi: bir xil nomlar, bir xil birliklar, bitta manba; har bir qator qaysi fayldan kelganini ko'rsatadi, asl fayllarga esa tegilmaydi.