ເນື້ອໃນ
ພວກເຮົາມີສອງຕາຕະລາງ (ຕົວຢ່າງ, ຮຸ່ນເກົ່າແລະໃຫມ່ຂອງບັນຊີລາຍຊື່ລາຄາ), ເຊິ່ງພວກເຮົາຈໍາເປັນຕ້ອງປຽບທຽບແລະຊອກຫາຄວາມແຕກຕ່າງຢ່າງໄວວາ:
ມັນຈະແຈ້ງທັນທີວ່າບາງສິ່ງບາງຢ່າງໄດ້ຖືກເພີ່ມເຂົ້າໃນບັນຊີລາຍຊື່ລາຄາໃຫມ່ (ວັນທີ, ຜັກທຽມ ...), ບາງສິ່ງບາງຢ່າງໄດ້ຫາຍໄປ (blackberries, raspberries ...), ລາຄາໄດ້ມີການປ່ຽນແປງສໍາລັບບາງສິນຄ້າ (figs, melons ... ). ທ່ານຈໍາເປັນຕ້ອງຊອກຫາແລະສະແດງການປ່ຽນແປງທັງຫມົດເຫຼົ່ານີ້ຢ່າງໄວວາ.
ສໍາລັບວຽກງານໃດຫນຶ່ງໃນ Excel, ເກືອບສະເຫມີມີຫຼາຍກວ່າຫນຶ່ງການແກ້ໄຂ (ປົກກະຕິ 4-5). ສໍາລັບບັນຫາຂອງພວກເຮົາ, ວິທີການທີ່ແຕກຕ່າງກັນຫຼາຍສາມາດນໍາໃຊ້:
- ຫນ້າທີ່ VPR (VLOOKUP) — ຊອກຫາຊື່ຜະລິດຕະພັນຈາກບັນຊີລາຍການລາຄາໃຫມ່ໃນອັນເກົ່າແລະສະແດງລາຄາເກົ່າຕໍ່ກັບອັນໃຫມ່, ແລະຫຼັງຈາກນັ້ນຈັບຄວາມແຕກຕ່າງ
- ລວມສອງລາຍການເຂົ້າໄປໃນຫນຶ່ງແລະຫຼັງຈາກນັ້ນສ້າງຕາຕະລາງ pivot ໂດຍອີງໃສ່ມັນ, ບ່ອນທີ່ຄວາມແຕກຕ່າງຈະເຫັນໄດ້ຊັດເຈນ
- ໃຊ້ Power Query Add-in ສໍາລັບ Excel
ໃຫ້ຂອງພວກເຂົາທັງຫມົດໃນຄໍາສັ່ງ.
ວິທີການ 1. ການປຽບທຽບຕາຕະລາງກັບຟັງຊັນ VLOOKUP
ຖ້າທ່ານບໍ່ຄຸ້ນເຄີຍກັບຄຸນສົມບັດທີ່ຍອດຢ້ຽມນີ້, ທໍາອິດເບິ່ງທີ່ນີ້ແລະອ່ານຫຼືເບິ່ງວິດີໂອ tutorial ກ່ຽວກັບມັນ - ຊ່ວຍປະຢັດຕົວທ່ານເອງສອງສາມປີຂອງຊີວິດ.
ໂດຍປົກກະຕິ, ຟັງຊັນນີ້ຖືກນໍາໃຊ້ເພື່ອດຶງຂໍ້ມູນຈາກຕາຕະລາງຫນຶ່ງໄປຫາອື່ນໂດຍການຈັບຄູ່ພາລາມິເຕີທົ່ວໄປບາງຢ່າງ. ໃນກໍລະນີນີ້, ພວກເຮົາຈະໃຊ້ມັນເພື່ອຊຸກດັນໃຫ້ລາຄາເກົ່າເຂົ້າໄປໃນລາຄາໃຫມ່:
ຜະລິດຕະພັນເຫຼົ່ານັ້ນ, ຕໍ່ກັບຂໍ້ຜິດພາດ #N/A, ບໍ່ໄດ້ຢູ່ໃນບັນຊີລາຍຊື່ເກົ່າ, ເຊັ່ນວ່າໄດ້ຖືກເພີ່ມເຂົ້າ. ການປ່ຽນແປງລາຄາຍັງເຫັນໄດ້ຊັດເຈນ.
pros ວິທີການນີ້: ງ່າຍດາຍແລະຊັດເຈນ, "ຄລາສສິກຂອງປະເພດ", ຍ້ອນວ່າເຂົາເຈົ້າເວົ້າວ່າ. ເຮັດວຽກຢູ່ໃນສະບັບໃດຂອງ Excel.
cons ຢູ່ທີ່ນັ້ນ. ເພື່ອຊອກຫາຜະລິດຕະພັນທີ່ເພີ່ມເຂົ້າໃນລາຍະການລາຄາໃຫມ່, ທ່ານຈະຕ້ອງເຮັດຂັ້ນຕອນດຽວກັນໃນທິດທາງກົງກັນຂ້າມ, ຄືດຶງລາຄາໃຫມ່ໄປສູ່ລາຄາເກົ່າດ້ວຍການຊ່ວຍເຫຼືອຂອງ VLOOKUP. ຖ້າຂະຫນາດຂອງຕາຕະລາງມີການປ່ຽນແປງໃນມື້ອື່ນ, ຫຼັງຈາກນັ້ນ, ສູດຈະຕ້ອງຖືກປັບ. ດີ, ແລະຢູ່ໃນຕາຕະລາງຂະຫນາດໃຫຍ່ແທ້ໆ (> 100 ພັນແຖວ), ຄວາມສຸກທັງຫມົດນີ້ຈະຊ້າລົງ.
ວິທີທີ່ 2: ການປຽບທຽບຕາຕະລາງໂດຍໃຊ້ pivot
ໃຫ້ພວກເຮົາຄັດລອກຕາຕະລາງຂອງພວກເຮົາລົງລຸ່ມຫນຶ່ງ, ເພີ່ມຄໍລໍາທີ່ມີຊື່ຂອງລາຍະການລາຄາ, ດັ່ງນັ້ນຕໍ່ມາທ່ານສາມາດເຂົ້າໃຈໄດ້ຈາກບັນຊີລາຍຊື່ໃດ:
ໃນປັດຈຸບັນ, ໂດຍອີງໃສ່ຕາຕະລາງທີ່ສ້າງຂຶ້ນ, ພວກເຮົາຈະສ້າງບົດສະຫຼຸບໂດຍຜ່ານ ແຊກ – PivotTable (ໃສ່ - ຕາຕະລາງ Pivot). ໃຫ້ຖິ້ມພາກສະຫນາມ ຜະລິດຕະພັນ ກັບພື້ນທີ່ຂອງສາຍ, ພາກສະຫນາມ ລາຄາ ກັບພື້ນທີ່ຖັນແລະພາກສະຫນາມ Цອັນນີ້ ເຂົ້າໄປໃນຂອບເຂດ:
ດັ່ງທີ່ທ່ານສາມາດເບິ່ງເຫັນໄດ້, ຕາຕະລາງ pivot ຈະສ້າງບັນຊີລາຍຊື່ທົ່ວໄປຂອງຜະລິດຕະພັນທັງຫມົດຈາກລາຍະການລາຄາເກົ່າແລະໃຫມ່ໂດຍອັດຕະໂນມັດ (ບໍ່ມີການຊໍ້າຊ້ອນ!) ແລະຈັດລຽງຜະລິດຕະພັນຕາມລໍາດັບ. ທ່ານສາມາດເບິ່ງເຫັນໄດ້ຊັດເຈນວ່າຜະລິດຕະພັນທີ່ເພີ່ມເຂົ້າມາ (ພວກເຂົາບໍ່ມີລາຄາເກົ່າ), ຜະລິດຕະພັນທີ່ຖືກໂຍກຍ້າຍ (ພວກເຂົາບໍ່ມີລາຄາໃຫມ່) ແລະການປ່ຽນແປງລາຄາ, ຖ້າມີ.
ຈໍານວນທັງຫມົດຂະຫນາດໃຫຍ່ຢູ່ໃນຕາຕະລາງດັ່ງກ່າວບໍ່ມີຄວາມຫມາຍ, ແລະພວກເຂົາສາມາດຖືກປິດການໃຊ້ງານຢູ່ໃນແຖບ ຜູ້ກໍ່ສ້າງ – ຈໍານວນທັງໝົດ – ປິດການໃຊ້ງານສໍາລັບແຖວ ແລະຖັນ (ການອອກແບບ — Grand Totals).
ຖ້າຫາກວ່າລາຄາມີການປ່ຽນແປງ (ແຕ່ບໍ່ແມ່ນປະລິມານຂອງສິນຄ້າ!), ມັນພຽງພໍທີ່ຈະປັບປຸງສະຫຼຸບໂດຍການຄລິກຂວາໃສ່ມັນ - ໂຫຼດຫນ້າຈໍຄືນ.
pros: ວິທີການນີ້ແມ່ນຄໍາສັ່ງຂອງຂະຫນາດໄວກັບຕາຕະລາງຂະຫນາດໃຫຍ່ກ່ວາ VLOOKUP.
cons: ທ່ານຈໍາເປັນຕ້ອງຄັດລອກຂໍ້ມູນດ້ວຍຕົນເອງພາຍໃຕ້ກັນແລະກັນແລະເພີ່ມຄໍລໍາທີ່ມີຊື່ຂອງລາຍະການລາຄາ. ຖ້າຂະຫນາດຂອງຕາຕະລາງມີການປ່ຽນແປງ, ຫຼັງຈາກນັ້ນທ່ານຕ້ອງເຮັດທຸກສິ່ງທຸກຢ່າງອີກເທື່ອຫນຶ່ງ.
ວິທີທີ 3: ການປຽບທຽບຕາຕະລາງກັບ Power Query
Power Query ເປັນ add-in ຟຣີສໍາລັບ Microsoft Excel ທີ່ຊ່ວຍໃຫ້ທ່ານສາມາດໂຫລດຂໍ້ມູນເຂົ້າໄປໃນ Excel ຈາກເກືອບທຸກແຫຼ່ງແລະຫຼັງຈາກນັ້ນປ່ຽນຂໍ້ມູນນີ້ໃນທາງໃດກໍ່ຕາມທີ່ຕ້ອງການ. ໃນ Excel 2016, add-in ນີ້ໄດ້ຖືກສ້າງແລ້ວໃນຄ່າເລີ່ມຕົ້ນໃນແຖບ ຂໍ້ມູນ (ຂໍ້ມູນ), ແລະສໍາລັບ Excel 2010-2013 ທ່ານຈໍາເປັນຕ້ອງໄດ້ດາວໂຫລດມັນແຍກຕ່າງຫາກຈາກເວັບໄຊທ໌ Microsoft ແລະຕິດຕັ້ງມັນ - ເອົາແຖບໃຫມ່ ແບບສອບຖາມພະລັງງານ.
ກ່ອນທີ່ຈະໂຫລດລາຍະການລາຄາຂອງພວກເຮົາເຂົ້າໄປໃນ Power Query, ທໍາອິດພວກເຂົາຕ້ອງໄດ້ຮັບການປ່ຽນເປັນຕາຕະລາງ smart. ເພື່ອເຮັດສິ່ງນີ້, ເລືອກເອົາໄລຍະທີ່ມີຂໍ້ມູນແລະກົດປະສົມປະສານໃນແປ້ນພິມ Ctrl+T ຫຼືເລືອກແຖບເທິງໂບ ຫນ້າທໍາອິດ – ຈັດຮູບແບບເປັນຕາຕະລາງ (ຫນ້າທໍາອິດ — Format as ຕາຕະລາງ). ຊື່ຂອງຕາຕະລາງທີ່ສ້າງຂຶ້ນສາມາດຖືກແກ້ໄຂຢູ່ໃນແຖບ ຜູ້ກໍ່ສ້າງ (ຂ້ອຍຈະອອກຈາກມາດຕະຖານ ຕາລາງ 1 и ຕາລາງ 2, ເຊິ່ງໄດ້ຮັບໂດຍຄ່າເລີ່ມຕົ້ນ).
ໂຫຼດລາຄາເກົ່າໃນ Power Query ໂດຍໃຊ້ປຸ່ມ ຈາກຕາຕະລາງ/ໄລຍະ (ຈາກຕາຕະລາງ/ໄລຍະ) ຈາກແຖບ ຂໍ້ມູນ (ວັນທີ) ຫຼືຈາກແຖບ ແບບສອບຖາມພະລັງງານ (ຂຶ້ນກັບສະບັບຂອງ Excel). ຫຼັງຈາກການໂຫຼດ, ພວກເຮົາຈະກັບຄືນໄປຫາ Excel ຈາກ Power Query ດ້ວຍຄໍາສັ່ງ ປິດ ແລະ ໂຫຼດ – ປິດ ແລະ ໂຫຼດໃນ… (ປິດ & ໂຫຼດ — Close & Load To…):
... ແລະຢູ່ໃນປ່ອງຢ້ຽມທີ່ປາກົດຫຼັງຈາກນັ້ນເລືອກ ພຽງແຕ່ສ້າງການເຊື່ອມຕໍ່ (ການເຊື່ອມຕໍ່ເທົ່ານັ້ນ).
ເຮັດຊ້ໍາອີກຄັ້ງກັບລາຍການລາຄາໃຫມ່.
ຕອນນີ້ໃຫ້ພວກເຮົາສ້າງແບບສອບຖາມທີສາມທີ່ຈະສົມທົບແລະປຽບທຽບຂໍ້ມູນຈາກສອງອັນທີ່ຜ່ານມາ. ເພື່ອເຮັດສິ່ງນີ້, ເລືອກໃນ Excel ໃນແຖບ ຂໍ້ມູນ – ໄດ້ຮັບຂໍ້ມູນ – ສົມທົບການຮ້ອງຂໍ – ລວມ (ຂໍ້ມູນ — ໄດ້ຮັບຂໍ້ມູນ — ລວມການສອບຖາມ — ລວມ) ຫຼືກົດປຸ່ມ ສົມທົບ (ລວມ) ແຖບ ແບບສອບຖາມພະລັງງານ.
ຢູ່ໃນປ່ອງຢ້ຽມທີ່ເຂົ້າຮ່ວມ, ເລືອກຕາຕະລາງຂອງພວກເຮົາໃນລາຍການແບບເລື່ອນລົງ, ເລືອກຄໍລໍາທີ່ມີຊື່ຂອງສິນຄ້າໃນພວກມັນ, ແລະຢູ່ດ້ານລຸ່ມ, ກໍານົດວິທີການເຂົ້າຮ່ວມ - ສໍາເລັດພາຍນອກ (ເຕັມນອກ):
ຫຼັງຈາກທີ່ຄລິກໃສ່ OK ຕາຕະລາງສາມຄໍລໍາຄວນຈະປາກົດ, ບ່ອນທີ່ຢູ່ໃນຖັນທີສາມ, ທ່ານຈໍາເປັນຕ້ອງຂະຫຍາຍເນື້ອໃນຂອງຕາຕະລາງທີ່ຊ້ອນກັນໂດຍໃຊ້ລູກສອນຄູ່ໃນສ່ວນຫົວ:
ດັ່ງນັ້ນ, ພວກເຮົາໄດ້ຮັບການລວມຂໍ້ມູນຈາກທັງສອງຕາຕະລາງ:
ມັນດີກວ່າ, ແນ່ນອນ, ການປ່ຽນຊື່ຖັນໃນສ່ວນຫົວໂດຍການຄລິກສອງຄັ້ງໃສ່ສິ່ງທີ່ເຂົ້າໃຈໄດ້ຫຼາຍ:
ແລະໃນປັດຈຸບັນທີ່ຫນ້າສົນໃຈຫຼາຍທີ່ສຸດ. ໄປທີ່ແຖບ ເພີ່ມຖັນ (ເພີ່ມຖັນ) ແລະໃຫ້ຄລິກໃສ່ປຸ່ມ ຖັນເງື່ອນໄຂ (ຖັນເງື່ອນໄຂ). ແລະຫຼັງຈາກນັ້ນຢູ່ໃນປ່ອງຢ້ຽມທີ່ເປີດ, ໃສ່ເງື່ອນໄຂການທົດສອບຈໍານວນຫນຶ່ງດ້ວຍຄ່າຜົນຜະລິດທີ່ສອດຄ້ອງກັນ:
ມັນຍັງຄົງໃຫ້ຄລິກໃສ່ OK ແລະອັບໂຫລດລາຍງານຜົນໄດ້ຮັບໄປຍັງ Excel ໂດຍໃຊ້ປຸ່ມດຽວກັນ ປິດແລະດາວໂຫລດ (ປິດ & ໂຫຼດ) ແຖບ ຫນ້າທໍາອິດ (ໜ້າ ທຳ ອິດ):
ຄວາມງາມ.
ຍິ່ງໄປກວ່ານັ້ນ, ຖ້າການປ່ຽນແປງໃດໆເກີດຂື້ນໃນລາຍະການລາຄາໃນອະນາຄົດ (ເສັ້ນຖືກເພີ່ມຫຼືລຶບ, ລາຄາປ່ຽນແປງ, ແລະອື່ນໆ), ມັນຈະພຽງພໍພຽງແຕ່ປັບປຸງຄໍາຮ້ອງຂໍຂອງພວກເຮົາດ້ວຍປຸ່ມລັດແປ້ນພິມ. Ctrl+alt+F5 ຫຼືໂດຍປຸ່ມ ໂຫຼດຂໍ້ມູນຄືນໃໝ່ທັງໝົດ (ໂຫຼດຂໍ້ມູນຄືນໃໝ່ທັງໝົດ) ແຖບ ຂໍ້ມູນ (ວັນທີ).
pros: ບາງທີວິທີທີ່ສວຍງາມແລະສະດວກທີ່ສຸດຂອງທັງຫມົດ. ເຮັດວຽກຢ່າງສະຫຼາດກັບຕາຕະລາງຂະຫນາດໃຫຍ່. ບໍ່ຮຽກຮ້ອງໃຫ້ມີການແກ້ໄຂດ້ວຍຕົນເອງໃນເວລາທີ່ປັບຂະຫນາດຕາຕະລາງ.
cons: ຕ້ອງການເພີ່ມ Power Query (ໃນ Excel 2010-2013) ຫຼື Excel 2016 ເພື່ອຕິດຕັ້ງ. ຊື່ຖັນໃນຂໍ້ມູນແຫຼ່ງຕ້ອງບໍ່ຖືກປ່ຽນແປງ, ຖ້າບໍ່ດັ່ງນັ້ນພວກເຮົາຈະໄດ້ຮັບຄວາມຜິດພາດ "ຖັນດັ່ງກ່າວແລະບໍ່ພົບ!" ເມື່ອພະຍາຍາມປັບປຸງການສອບຖາມ.
- ວິທີການເກັບກຳຂໍ້ມູນຈາກໄຟລ໌ Excel ທັງໝົດໃນໂຟນເດີທີ່ໃຫ້ໄວ້ໂດຍໃຊ້ Power Query
- ວິທີການຊອກຫາການແຂ່ງຂັນລະຫວ່າງສອງລາຍການໃນ Excel
- ຮວມສອງລາຍຊື່ໂດຍບໍ່ຊໍ້າກັນ