ເນື້ອໃນ
ການຄົ້ນຫາຄໍາສໍາຄັນໃນຂໍ້ຄວາມແຫຼ່ງແມ່ນຫນຶ່ງໃນວຽກງານທົ່ວໄປທີ່ສຸດໃນເວລາທີ່ເຮັດວຽກກັບຂໍ້ມູນ. ໃຫ້ເບິ່ງການແກ້ໄຂຂອງມັນໃນຫຼາຍວິທີໂດຍໃຊ້ຕົວຢ່າງຕໍ່ໄປນີ້:
ສົມມຸດວ່າທ່ານແລະຂ້ອຍມີລາຍຊື່ຄໍາສໍາຄັນ - ຊື່ຂອງຍີ່ຫໍ້ລົດ - ແລະຕາຕະລາງຂະຫນາດໃຫຍ່ຂອງອາໄຫຼ່ທຸກປະເພດ, ບ່ອນທີ່ຄໍາອະທິບາຍບາງຄັ້ງສາມາດບັນຈຸຫນຶ່ງຫຼືຫຼາຍຍີ່ຫໍ້ດັ່ງກ່າວໃນເວລາດຽວກັນ, ຖ້າອາໄຫຼ່ເຫມາະກັບຫຼາຍກ່ວາຫນຶ່ງ. ຍີ່ຫໍ້ລົດ. ວຽກງານຂອງພວກເຮົາແມ່ນເພື່ອຊອກຫາແລະສະແດງຄໍາທີ່ກວດພົບທັງຫມົດໃນຈຸລັງໃກ້ຄຽງໂດຍຜ່ານຕົວອັກສອນຕົວແຍກ (ຕົວຢ່າງ, ເຄື່ອງໝາຍຈຸດ).
ວິທີການ 1. Power Query
ແນ່ນອນ, ທໍາອິດພວກເຮົາປ່ຽນຕາຕະລາງຂອງພວກເຮົາໃຫ້ເປັນແບບເຄື່ອນໄຫວ ("ສະຫຼາດ") ໂດຍໃຊ້ແປ້ນພິມລັດ Ctrl+T ຫຼືຄໍາສັ່ງ ຫນ້າທໍາອິດ – ຈັດຮູບແບບເປັນຕາຕະລາງ (ຫນ້າທໍາອິດ — Format as ຕາຕະລາງ), ໃຫ້ຊື່ພວກເຂົາ (ຕົວຢ່າງ ແສດи ອາໄຫຼ່) ແລະໂຫຼດຫນຶ່ງຫນຶ່ງເຂົ້າໄປໃນຕົວແກ້ໄຂ Power Query ໂດຍການເລືອກໃນແຖບ ຂໍ້ມູນ – ຈາກຕາຕະລາງ/ໄລຍະ (ຂໍ້ມູນ — ຈາກຕາຕະລາງ/ໄລຍະ). ຖ້າທ່ານມີ Excel ຮຸ່ນເກົ່າ 2010-2013, ບ່ອນທີ່ Power Query ຖືກຕິດຕັ້ງເປັນ add-in ແຍກຕ່າງຫາກ, ຫຼັງຈາກນັ້ນປຸ່ມທີ່ຕ້ອງການຈະຢູ່ໃນແຖບ. ແບບສອບຖາມພະລັງງານ. ຖ້າທ່ານມີລຸ້ນໃຫມ່ຂອງ Excel 365, ຫຼັງຈາກນັ້ນກົດປຸ່ມ ຈາກຕາຕະລາງ/ໄລຍະ ເອີ້ນຢູ່ທີ່ນັ້ນດຽວນີ້ ມີໃບ (ຈາກ Sheet).
ຫຼັງຈາກໂຫຼດແຕ່ລະຕາຕະລາງໃນ Power Query, ພວກເຮົາກັບຄືນໄປຫາ Excel ດ້ວຍຄໍາສັ່ງ ຫນ້າທໍາອິດ — ປິດ ແລະໂຫຼດ — ປິດ ແລະໂຫຼດໄປ… — ສ້າງການເຊື່ອມຕໍ່ເທົ່ານັ້ນ (ໜ້າຫຼັກ — ປິດ & ໂຫຼດ — ປິດ & ໂຫຼດໃສ່… — ພຽງແຕ່ສ້າງການເຊື່ອມຕໍ່).
ຕອນນີ້ໃຫ້ພວກເຮົາສ້າງຄໍາຮ້ອງຂໍຊ້ໍາກັນ ອາໄຫຼ່ໂດຍການຄລິກຂວາໃສ່ມັນ ແລະເລືອກ ການຮ້ອງຂໍຊ້ໍາກັນ (ການສອບຖາມຊໍ້າກັນ), ຫຼັງຈາກນັ້ນປ່ຽນຊື່ຄໍາຮ້ອງຂໍສໍາເນົາຜົນໄດ້ຮັບເປັນ ຜົນໄດ້ຮັບ ແລະພວກເຮົາຈະສືບຕໍ່ເຮັດວຽກກັບລາວ.
ເຫດຜົນຂອງການປະຕິບັດແມ່ນດັ່ງຕໍ່ໄປນີ້:
- ຢູ່ໃນແຖບຂັ້ນສູງ ການເພີ່ມຖັນ ເລືອກທີມ ຖັນແບບກຳນົດເອງ (ເພີ່ມຖັນ — ຖັນແບບກຳນົດເອງ) ແລະໃສ່ສູດ = ຍີ່ຫໍ້. ຫຼັງຈາກທີ່ຄລິກໃສ່ OK ພວກເຮົາຈະໄດ້ຮັບຄໍລໍາໃຫມ່, ບ່ອນທີ່ໃນແຕ່ລະຫ້ອງຈະມີຕາຕະລາງທີ່ຊ້ອນກັນທີ່ມີບັນຊີລາຍຊື່ຂອງຄໍາສໍາຄັນຂອງພວກເຮົາ - ຍີ່ຫໍ້ຜູ້ຜະລິດລົດໃຫຍ່:
- ໃຊ້ປຸ່ມທີ່ມີລູກສອນສອງເທົ່າຢູ່ໃນສ່ວນຫົວຂອງຖັນທີ່ເພີ່ມໃສ່ເພື່ອຂະຫຍາຍຕາຕະລາງທີ່ຕິດກັນທັງໝົດ. ໃນເວລາດຽວກັນ, ສາຍທີ່ມີຄໍາອະທິບາຍຂອງອາໄຫຼ່ຈະຄູນດ້ວຍຈໍານວນຍີ່ຫໍ້ຫຼາຍ, ແລະພວກເຮົາຈະໄດ້ຮັບຄູ່ທີ່ເປັນໄປໄດ້ທັງຫມົດ - ການປະສົມຂອງ "ຍີ່ຫໍ້ອາໄຫຼ່":
- ຢູ່ໃນແຖບຂັ້ນສູງ ການເພີ່ມຖັນ ເລືອກທີມ ຖັນເງື່ອນໄຂ (ຖັນເງື່ອນໄຂ) ແລະກໍານົດເງື່ອນໄຂສໍາລັບການກວດສອບການປະກົດຕົວຂອງຄໍາສໍາຄັນ (ຍີ່ຫໍ້) ໃນຂໍ້ຄວາມແຫຼ່ງ (ຄໍາອະທິບາຍສ່ວນ):
- ເພື່ອເຮັດໃຫ້ກໍລະນີຄົ້ນຫາບໍ່ມີຄວາມຮູ້ສຶກ, ເພີ່ມການໂຕ້ຖຽງທີສາມດ້ວຍຕົນເອງໃນແຖບສູດ Compare.OrdinalIgnoreCase ກັບຟັງຊັນການກວດສອບການປະກົດຕົວ ຂໍ້ຄວາມ.ປະກອບດ້ວຍ (ຖ້າແຖບສູດບໍ່ສັງເກດເຫັນ, ຫຼັງຈາກນັ້ນມັນສາມາດຖືກເປີດໃຊ້ໃນແຖບ ການທົບທວນຄືນ):
- ພວກເຮົາກັ່ນຕອງຕາຕະລາງຜົນໄດ້ຮັບ, ປ່ອຍໃຫ້ພຽງແຕ່ຫນຶ່ງໃນຄໍລໍາສຸດທ້າຍ, ເຊັ່ນວ່າກົງກັນແລະເອົາຖັນທີ່ບໍ່ຈໍາເປັນອອກ. ການປະກົດຕົວ.
- ຈັດກຸ່ມຄຳອະທິບາຍທີ່ຄືກັນດ້ວຍຄຳສັ່ງ ກຸ່ມໂດຍ ແຖບ ການຫັນເປັນ (ການປ່ຽນແປງ — ກຸ່ມໂດຍ). ເປັນການດໍາເນີນງານການລວບລວມ, ເລືອກ ສາຍທັງໝົດ (ແຖວທັງໝົດ). ຢູ່ທີ່ຜົນຜະລິດ, ພວກເຮົາໄດ້ຮັບຖັນທີ່ມີຕາຕະລາງ, ເຊິ່ງປະກອບດ້ວຍລາຍລະອຽດທັງຫມົດສໍາລັບແຕ່ລະອາໄຫຼ່, ລວມທັງຍີ່ຫໍ້ຂອງຜູ້ຜະລິດລົດໃຫຍ່ທີ່ພວກເຮົາຕ້ອງການ:
- ເພື່ອແຍກຄະແນນແຕ່ລະພາກສ່ວນ, ໃຫ້ເພີ່ມຖັນທີ່ຄຳນວນແລ້ວໃສ່ໃນແຖບ ການເພີ່ມຖັນ – ຖັນແບບກຳນົດເອງ (ເພີ່ມຖັນ — ຖັນແບບກຳນົດເອງ) ແລະໃຊ້ສູດທີ່ປະກອບດ້ວຍຕາຕະລາງ (ພວກມັນຢູ່ໃນຖັນຂອງພວກເຮົາ ລາຍລະອຽດ) ແລະຊື່ຂອງຖັນສະກັດ:
- ພວກເຮົາຄລິກໃສ່ປຸ່ມທີ່ມີລູກສອນສອງເທົ່າຢູ່ໃນຫົວຂອງຖັນຜົນໄດ້ຮັບແລະເລືອກຄໍາສັ່ງ ສະກັດຄ່າ (ຄ່າສະກັດ)ເພື່ອສົ່ງອອກສະແຕມທີ່ມີຕົວອັກສອນຕົວກໍານົດທີ່ທ່ານຕ້ອງການ:
- ການເອົາຖັນທີ່ບໍ່ຈໍາເປັນອອກ ລາຍລະອຽດ.
- ເພື່ອເພີ່ມຕາຕະລາງຜົນໄດ້ຮັບຂອງຊິ້ນສ່ວນທີ່ຫາຍໄປຈາກມັນ, ບ່ອນທີ່ບໍ່ພົບຍີ່ຫໍ້ໃດໆໃນຄໍາອະທິບາຍ, ພວກເຮົາປະຕິບັດຂັ້ນຕອນການສົມທົບການສອບຖາມ. ຜົນ ດ້ວຍການຮ້ອງຂໍຕົ້ນສະບັບ ອາໄຫຼ່ ປຸ່ມ ສົມທົບ ແຖບ ຫນ້າທໍາອິດ (ຫນ້າທໍາອິດ - ຮວມການສອບຖາມ). ປະເພດການເຊື່ອມຕໍ່ - ດ້ານນອກ Join Right (ເຂົ້າຮ່ວມດ້ານນອກຂວາ):
- ທັງຫມົດທີ່ຍັງເຫຼືອແມ່ນເພື່ອເອົາຄໍລໍາເພີ່ມເຕີມແລະປ່ຽນຊື່ - ຍ້າຍສ່ວນທີ່ເຫຼືອ - ແລະວຽກງານຂອງພວກເຮົາຖືກແກ້ໄຂ:
ວິທີການ 2. ສູດ
ຖ້າທ່ານມີ Excel 2016 ຮຸ່ນຫຼືຫຼັງຈາກນັ້ນ, ບັນຫາຂອງພວກເຮົາສາມາດແກ້ໄຂໄດ້ໃນລັກສະນະທີ່ຫນາແຫນ້ນແລະສະຫງ່າງາມໂດຍໃຊ້ຟັງຊັນໃຫມ່. ສົມທົບ (TEXTJOIN):
ເຫດຜົນທາງຫລັງຂອງສູດນີ້ແມ່ນງ່າຍດາຍ:
- ຫນ້າທີ່ ຄົ້ນຫາ (ຊອກຫາ) ຄົ້ນຫາການປະກົດຕົວຂອງແຕ່ລະຍີ່ຫໍ້ໃນທາງກັບກັນໃນຄໍາອະທິບາຍປະຈຸບັນຂອງພາກສ່ວນແລະສົ່ງຄືນທັງເລກລໍາດັບຂອງສັນຍາລັກ, ເລີ່ມຕົ້ນຈາກຍີ່ຫໍ້ທີ່ພົບເຫັນ, ຫຼືຄວາມຜິດພາດ #VALUE! ຖ້າຍີ່ຫໍ້ບໍ່ຢູ່ໃນຄໍາອະທິບາຍ.
- ຫຼັງຈາກນັ້ນ, ການນໍາໃຊ້ຫນ້າທີ່ IF (ຖ້າແມ່ນ) и EOSHIBKA (ISERROR) ພວກເຮົາປ່ຽນແທນຄວາມຜິດພາດດ້ວຍສາຍຂໍ້ຄວາມຫວ່າງເປົ່າ “”, ແລະຕົວເລກຕາມລໍາດັບຂອງຕົວອັກສອນທີ່ມີຊື່ຍີ່ຫໍ້ເອງ.
- array ຜົນໄດ້ຮັບຂອງເຊລທີ່ຫວ່າງເປົ່າແລະຍີ່ຫໍ້ທີ່ພົບເຫັນແມ່ນປະກອບເຂົ້າໄປໃນສະຕຣິງດຽວໂດຍຜ່ານຕົວອັກສອນຕົວແຍກທີ່ໃຫ້ໂດຍໃຊ້ຟັງຊັນ. ສົມທົບ (TEXTJOIN).
ການປຽບທຽບປະສິດທິພາບແລະການສອບຖາມພະລັງງານ Buffering ສໍາລັບຄວາມໄວ
ສໍາລັບການທົດສອບການປະຕິບັດ, ໃຫ້ພວກເຮົາເອົາຕາຕະລາງຂອງ 100 ຄໍາອະທິບາຍອາໄຫຼ່ເປັນຂໍ້ມູນເບື້ອງຕົ້ນ. ກ່ຽວກັບມັນ, ພວກເຮົາໄດ້ຮັບຜົນໄດ້ຮັບດັ່ງຕໍ່ໄປນີ້:
- ເວລາຄິດໄລ່ຄືນຕາມສູດ (ວິທີ 2) – 9 ວິນາທີ. ເມື່ອທ່ານຄັດລອກສູດທໍາອິດໃສ່ຄໍລໍາທັງຫມົດແລະ 2 ວິນາທີ. ຊ້ຳແລ້ວຊ້ຳອີກ (ອາດຈະສົ່ງຜົນກະທົບຕໍ່ບັນທຶກ).
- ເວລາອັບເດດຂອງການສອບຖາມ Power Query (ວິທີ 1) ແມ່ນຮ້າຍແຮງຂຶ້ນຫຼາຍ – 110 ວິນາທີ.
ແນ່ນອນ, ຫຼາຍໆຢ່າງແມ່ນຂຶ້ນກັບຮາດແວຂອງ PC ໂດຍສະເພາະແລະສະບັບທີ່ຕິດຕັ້ງຂອງ Office ແລະການປັບປຸງ, ແຕ່ພາບລວມ, ຂ້າພະເຈົ້າຄິດວ່າ, ແມ່ນຈະແຈ້ງ.
ເພື່ອເລັ່ງການສອບຖາມ Power Query, ໃຫ້ພວກເຮົາ buffer ຕາຕະລາງຊອກຫາ ແສດ, ເນື່ອງຈາກວ່າມັນບໍ່ມີການປ່ຽນແປງໃນຂະບວນການຂອງການປະຕິບັດການສອບຖາມແລະມັນບໍ່ຈໍາເປັນຕ້ອງ recalculate ມັນຢ່າງຕໍ່ເນື່ອງ (ເປັນ Power Query de facto ເຮັດ). ເພື່ອເຮັດສິ່ງນີ້, ພວກເຮົາໃຊ້ຫນ້າທີ່ Table.Buffer ຈາກ Power Query ພາສາ M.
ເພື່ອເຮັດສິ່ງນີ້, ເປີດຄໍາຖາມ ຜົນໄດ້ຮັບ ແລະຢູ່ໃນແຖບ ການທົບທວນຄືນ ກົດປຸ່ມ ບັນນາທິການຂັ້ນສູງ (ເບິ່ງ - ບັນນາທິການຂັ້ນສູງ). ໃນປ່ອງຢ້ຽມທີ່ເປີດ, ເພີ່ມເສັ້ນທີ່ມີຕົວແປໃຫມ່ Marky 2, ເຊິ່ງຈະເປັນສະບັບທີ່ buffered ຂອງໄດເລກະທໍລີ automaker ຂອງພວກເຮົາ, ແລະນໍາໃຊ້ຕົວແປໃຫມ່ນີ້ຕໍ່ມາໃນຄໍາສັ່ງສອບຖາມຕໍ່ໄປນີ້:
ຫຼັງຈາກການປັບປຸງດັ່ງກ່າວ, ຄວາມໄວການອັບເດດຂອງຄໍາຮ້ອງຂໍຂອງພວກເຮົາເພີ່ມຂຶ້ນເກືອບ 7 ເທົ່າ - ເຖິງ 15 ວິນາທີ. ຂ້ອນຂ້າງແຕກຕ່າງກັນ🙂
- ການຊອກຫາຂໍ້ຄວາມທີ່ບໍ່ເຂົ້າໃຈໃນ Power Query
- ການປ່ຽນຂໍ້ຄວາມເປັນຊຸດດ້ວຍສູດຄຳນວນ
- ການປ່ຽນຂໍ້ຄວາມເປັນຊຸດໃນ Power Query ດ້ວຍຟັງຊັນ List.Accumulate