ສົມມຸດວ່າທ່ານມີບັນຊີລາຍຊື່ທີ່, ດ້ວຍລະດັບທີ່ແຕກຕ່າງກັນຂອງ "ກົງໄປກົງມາ," ຂໍ້ມູນເບື້ອງຕົ້ນແມ່ນຂຽນ - ຕົວຢ່າງ, ທີ່ຢູ່ຫຼືຊື່ບໍລິສັດ:
 |  |
ມັນເຫັນໄດ້ຊັດເຈນວ່າເມືອງຫຼືບໍລິສັດດຽວກັນແມ່ນມີຢູ່ໃນຕົວແປ motley, ເຊິ່ງແນ່ນອນຈະສ້າງບັນຫາຫຼາຍເມື່ອເຮັດວຽກກັບຕາຕະລາງເຫຼົ່ານີ້ໃນອະນາຄົດ. ແລະຖ້າທ່ານຄິດເລັກນ້ອຍ, ທ່ານສາມາດຊອກຫາຕົວຢ່າງຫຼາຍຢ່າງຂອງວຽກງານທີ່ຄ້າຍຄືກັນຈາກຂົງເຂດອື່ນໆ.
ຕອນນີ້ຈິນຕະນາການວ່າຂໍ້ມູນຫລອກລວງດັ່ງກ່າວມາຫາທ່ານເປັນປະຈໍາ, ຫມາຍຄວາມວ່ານີ້ບໍ່ແມ່ນເລື່ອງ "ແກ້ໄຂດ້ວຍຕົນເອງ, ລືມມັນ" ຄັ້ງດຽວ, ແຕ່ເປັນບັນຫາປະຈໍາແລະເປັນຈໍານວນຫລາຍຂອງຈຸລັງ.
ຈະເຮັດແນວໃດ? ຢ່າປ່ຽນຂໍ້ຄວາມທີ່ບິດເບືອນດ້ວຍຕົນເອງ 100500 ເທື່ອດ້ວຍຕົວໜັງສືທີ່ຖືກຕ້ອງຜ່ານກ່ອງ “ຊອກຫາ ແລະ ແທນທີ່” ຫຼືໂດຍການຄລິກ Ctrl+H?
ສິ່ງທໍາອິດທີ່ຢູ່ໃນໃຈໃນສະຖານະການດັ່ງກ່າວແມ່ນເພື່ອເຮັດໃຫ້ການທົດແທນມະຫາຊົນຕາມປື້ມອ້າງອີງທີ່ລວບລວມລ່ວງຫນ້າຂອງທາງເລືອກທີ່ບໍ່ຖືກຕ້ອງແລະຖືກຕ້ອງ - ແບບນີ້:
ແຕ່ຫນ້າເສຍດາຍ, ດ້ວຍຄວາມເດັ່ນຊັດຂອງວຽກງານດັ່ງກ່າວ, Microsoft Excel ບໍ່ມີວິທີການກໍ່ສ້າງໃນການແກ້ໄຂງ່າຍໆ. ເພື່ອເລີ່ມຕົ້ນດ້ວຍ, ໃຫ້ພວກເຮົາຄິດອອກວິທີການເຮັດສິ່ງນີ້ດ້ວຍສູດ, ໂດຍບໍ່ມີການກ່ຽວຂ້ອງກັບ "ປືນໃຫຍ່" ໃນຮູບແບບຂອງ macros ໃນ VBA ຫຼື Power Query.
ກໍລະນີ 1. ການທົດແທນເຕັມຈໍານວນ
ໃຫ້ເລີ່ມຕົ້ນດ້ວຍກໍລະນີທີ່ຂ້ອນຂ້າງງ່າຍດາຍ - ສະຖານະການທີ່ທ່ານຈໍາເປັນຕ້ອງປ່ຽນແທນຂໍ້ຄວາມທີ່ເກົ່າແກ່ດ້ວຍຂໍ້ຄວາມໃຫມ່. ຢ່າງເຕັມສ່ວນ.
ໃຫ້ເວົ້າວ່າພວກເຮົາມີສອງຕາຕະລາງ:
ໃນຄັ້ງທໍາອິດ - ຊື່ variegated ຕົ້ນສະບັບຂອງບໍລິສັດ. ໃນຄັ້ງທີສອງ – ປື້ມບັນທຶກເອກະສານຂອງເອກະສານ. ຖ້າພວກເຮົາຊອກຫາຢູ່ໃນຊື່ຂອງບໍລິສັດໃນຕາຕະລາງທໍາອິດຄໍາໃດກໍ່ຕາມຈາກຄໍລໍາ ຊອກຫາ, ຫຼັງຈາກນັ້ນ, ທ່ານຈໍາເປັນຕ້ອງໄດ້ປ່ຽນຊື່ທີ່ຫຍໍ້ມາຈາກທີ່ຖືກຕ້ອງ - ຈາກຖັນ ທົດແທນ ຕາຕະລາງການຊອກຫາຄັ້ງທີສອງ.
ເພື່ອຄວາມສະດວກ:
- ຕາຕະລາງທັງສອງຖືກປ່ຽນເປັນແບບເຄື່ອນໄຫວ (“ສະຫຼາດ”) ໂດຍໃຊ້ປຸ່ມລັດແປ້ນພິມ Ctrl+T ຫຼືທີມງານ ໃສ່ - ຕາຕະລາງ (ໃສ່ - ຕາຕະລາງ).
- ໃນແຖບທີ່ປາກົດ ຜູ້ກໍ່ສ້າງ (ອອກແບບ) ຕາຕະລາງທໍາອິດທີ່ມີຊື່ ຂໍ້ມູນ, ແລະຕາຕະລາງອ້າງອີງທີສອງ - ການທົດແທນ.
ເພື່ອອະທິບາຍເຫດຜົນຂອງສູດ, ໃຫ້ໄປຈາກໄກເລັກນ້ອຍ.
ເອົາບໍລິສັດທໍາອິດຈາກເຊນ A2 ເປັນຕົວຢ່າງແລະລືມຊົ່ວຄາວກ່ຽວກັບບໍລິສັດສ່ວນທີ່ເຫຼືອ, ໃຫ້ພະຍາຍາມກໍານົດທາງເລືອກໃດຈາກຖັນ. ຊອກຫາ ພົບກັນຢູ່ທີ່ນັ້ນ. ເພື່ອເຮັດສິ່ງນີ້, ເລືອກຫ້ອງຫວ່າງໃດໆໃນສ່ວນທີ່ບໍ່ເສຍຄ່າຂອງແຜ່ນແລະໃສ່ຫນ້າທີ່ນັ້ນ ຊອກຫາ (ຊອກຫາ):
ຟັງຊັນນີ້ກຳນົດວ່າມີສະຕຣິງຍ່ອຍທີ່ໃຫ້ມາຫຼືບໍ່ (ອາກິວເມັນທຳອິດແມ່ນຄ່າທັງໝົດຈາກຖັນ. ຊອກຫາ) ເຂົ້າໄປໃນຂໍ້ຄວາມແຫຼ່ງ (ບໍລິສັດທໍາອິດຈາກຕາຕະລາງຂໍ້ມູນ) ແລະຄວນຈະອອກຕົວເລກຕາມລໍາດັບຂອງຕົວອັກສອນທີ່ພົບຂໍ້ຄວາມ, ຫຼືຄວາມຜິດພາດຖ້າບໍ່ພົບ substring.
trick ນີ້ແມ່ນຍ້ອນວ່າພວກເຮົາບໍ່ໄດ້ລະບຸຫນຶ່ງ, ແຕ່ຫຼາຍຄ່າເປັນ argument ທໍາອິດ, ຟັງຊັນນີ້ຍັງຈະກັບຄືນມາເປັນຜົນໄດ້ຮັບບໍ່ແມ່ນຫນຶ່ງຄ່າ, ແຕ່ array ຂອງ 3 ອົງປະກອບ. ຖ້າທ່ານບໍ່ມີ Office 365 ລຸ້ນລ້າສຸດທີ່ຮອງຮັບ dynamic arrays, ຫຼັງຈາກນັ້ນຫຼັງຈາກໃສ່ສູດນີ້ແລະຄລິກໃສ່ ກະລຸນາໃສ່ ທ່ານຈະເຫັນ array ນີ້ຢູ່ໃນແຜ່ນ:
ຖ້າຫາກທ່ານມີສະບັບທີ່ຜ່ານມາຂອງ Excel, ຫຼັງຈາກນັ້ນຄລິກໃສ່ ກະລຸນາໃສ່ ພວກເຮົາຈະເຫັນພຽງແຕ່ຄ່າທໍາອິດຈາກ array ຜົນໄດ້ຮັບ, ie error #VALUE! (#VALUE!).
ທ່ານບໍ່ຄວນຢ້ານ 🙂 ໃນຄວາມເປັນຈິງ, ສູດຂອງພວກເຮົາເຮັດວຽກແລະທ່ານຍັງສາມາດເຫັນ array ທັງຫມົດຂອງຜົນໄດ້ຮັບຖ້າທ່ານເລືອກຫນ້າທີ່ໃສ່ໃນແຖບສູດແລະກົດປຸ່ມ. F9(ຢ່າລືມກົດ Escກັບໄປທີ່ສູດ):
array ຜົນໄດ້ຮັບຂອງຜົນໄດ້ຮັບຫມາຍຄວາມວ່າໃນຊື່ບໍລິສັດທີ່ໂຄ້ງລົງຕົ້ນສະບັບ (GK Morozko OAO) ຂອງຄ່າທັງຫມົດໃນຖັນ ຊອກຫາ ພົບພຽງແຕ່ຄັ້ງທີສອງ (ໂມໂຣສໂກ), ແລະເລີ່ມຕົ້ນຈາກຕົວອັກສອນທີ 4 ຕິດຕໍ່ກັນ.
ຕອນນີ້ໃຫ້ເພີ່ມຫນ້າທີ່ໃສ່ສູດຂອງພວກເຮົາ VIEW(ເບິ່ງ):
ຟັງຊັນນີ້ມີສາມຂໍ້ໂຕ້ແຍ້ງ:
- ມູນຄ່າທີ່ຕ້ອງການ - ທ່ານສາມາດນໍາໃຊ້ຈໍານວນຂະຫນາດໃຫຍ່ພຽງພໍ (ສິ່ງທີ່ສໍາຄັນແມ່ນວ່າມັນເກີນຄວາມຍາວຂອງຂໍ້ຄວາມໃດຫນຶ່ງໃນແຫຼ່ງຂໍ້ມູນ)
- Viewed_vector – ຊ່ວງ ຫຼື array ທີ່ເຮົາກຳລັງຊອກຫາຄ່າທີ່ຕ້ອງການ. ນີ້ແມ່ນຫນ້າທີ່ແນະນໍາກ່ອນຫນ້ານີ້ ຊອກຫາ, ເຊິ່ງສົ່ງຄືນອາເຣ {#VALUE!:4:#VALUE!}
- vector_ຜົນການຄົ້ນຫາ – ຊ່ວງທີ່ພວກເຮົາຕ້ອງການສົ່ງຄືນຄ່າຖ້າຄ່າທີ່ຕ້ອງການຖືກພົບເຫັນຢູ່ໃນຕາລາງທີ່ສອດຄ້ອງກັນ. ນີ້ແມ່ນຊື່ທີ່ຖືກຕ້ອງຈາກຖັນ ທົດແທນ ຕາຕະລາງອ້າງອີງຂອງພວກເຮົາ.
ລັກສະນະຕົ້ນຕໍແລະບໍ່ຊັດເຈນຢູ່ທີ່ນີ້ແມ່ນວ່າຫນ້າທີ່ VIEW ຖ້າບໍ່ມີການຈັບຄູ່ທີ່ແນ່ນອນ, ໃຫ້ຊອກຫາຄ່ານ້ອຍທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ (ກ່ອນໜ້ານີ້).. ດັ່ງນັ້ນ, ໂດຍການລະບຸຕົວເລກທີ່ຮຸນແຮງໃດໆ (ຕົວຢ່າງ, 9999) ເປັນຄ່າທີ່ຕ້ອງການ, ພວກເຮົາຈະບັງຄັບ. VIEW ຊອກຫາຕາລາງທີ່ມີຕົວເລກນ້ອຍທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ (4) ໃນອາເຣ {#VALUE!:4:#VALUE!} ແລະສົ່ງຄ່າທີ່ກົງກັນຈາກ vector ຜົນໄດ້ຮັບ, ເຊັ່ນ: ຊື່ບໍລິສັດທີ່ຖືກຕ້ອງຈາກຖັນ. ທົດແທນ.
nuance ທີສອງແມ່ນວ່າ, ດ້ານວິຊາການ, ສູດຂອງພວກເຮົາແມ່ນສູດ array, ເນື່ອງຈາກວ່າການທໍາງານ ຊອກຫາ ຕອບເປັນຜົນໄດ້ຮັບບໍ່ແມ່ນຫນຶ່ງ, ແຕ່ເປັນ array ຂອງສາມຄ່າ. ແຕ່ນັບຕັ້ງແຕ່ຫນ້າທີ່ VIEW ສະຫນັບສະຫນູນ arrays ອອກຈາກກ່ອງ, ຫຼັງຈາກນັ້ນພວກເຮົາບໍ່ຈໍາເປັນຕ້ອງໃສ່ສູດນີ້ເປັນສູດ array ຄລາສສິກ - ໂດຍໃຊ້ແປ້ນພິມລັດ. Ctrl+ການປ່ຽນແປງ+ກະລຸນາໃສ່. ງ່າຍດາຍຫນຶ່ງຈະພຽງພໍ ກະລຸນາໃສ່.
ຫມົດເທົ່ານີ້. ຫວັງວ່າເຈົ້າຈະໄດ້ຮັບເຫດຜົນ.
ມັນຍັງຄົງເປັນການໂອນສູດສໍາເລັດຮູບໄປຫາຫ້ອງ B2 ທໍາອິດຂອງຖັນ Fixed – ແລະວຽກງານຂອງພວກເຮົາແມ່ນໄດ້ຮັບການແກ້ໄຂ!
ແນ່ນອນ, ດ້ວຍຕາຕະລາງທໍາມະດາ (ບໍ່ສະຫຼາດ), ສູດນີ້ຍັງເຮັດວຽກໄດ້ດີ (ພຽງແຕ່ຢ່າລືມກ່ຽວກັບກຸນແຈ F4 ແລະແກ້ໄຂການເຊື່ອມຕໍ່ທີ່ກ່ຽວຂ້ອງ):
ກໍລະນີ 2. ການທົດແທນບາງສ່ວນເປັນຈໍານວນຫຼວງຫຼາຍ
ກໍລະນີນີ້ແມ່ນ trickier ເລັກນ້ອຍ. ອີກເທື່ອຫນຶ່ງພວກເຮົາມີສອງຕາຕະລາງ "smart":
ຕາຕະລາງທໍາອິດທີ່ມີທີ່ຢູ່ຂຽນຜິດທີ່ຕ້ອງໄດ້ຮັບການແກ້ໄຂ (ຂ້ອຍເອີ້ນວ່າມັນ ຂໍ້ມູນ 2). ຕາຕະລາງທີສອງແມ່ນປື້ມອ້າງອີງ, ອີງຕາມການທີ່ທ່ານຈໍາເປັນຕ້ອງເຮັດການທົດແທນບາງສ່ວນຂອງ substring ພາຍໃນທີ່ຢູ່ (ຂ້າພະເຈົ້າເອີ້ນວ່າຕາຕະລາງນີ້. ການທົດແທນ 2).
ຄວາມແຕກຕ່າງພື້ນຖານນີ້ແມ່ນວ່າທ່ານຕ້ອງການທົດແທນພຽງແຕ່ສ່ວນຫນຶ່ງຂອງຂໍ້ມູນຕົ້ນສະບັບ - ຕົວຢ່າງ, ທີ່ຢູ່ທໍາອິດມີຂໍ້ມູນທີ່ບໍ່ຖືກຕ້ອງ. “ເຊນ. Petersburg” ທາງຂວາ “ເຊນ. Petersburg”, ອອກຈາກສ່ວນທີ່ເຫຼືອຂອງທີ່ຢູ່ (ລະຫັດໄປສະນີ, ຖະຫນົນ, ເຮືອນ) ຕາມທີ່ເປັນຢູ່.
ສູດສໍາເລັດຮູບຈະມີລັກສະນະນີ້ (ເພື່ອຄວາມງ່າຍຂອງການຮັບຮູ້, ຂ້າພະເຈົ້າໄດ້ແບ່ງອອກເປັນຈໍານວນເສັ້ນທີ່ໃຊ້ alt+ກະລຸນາໃສ່):
ການເຮັດວຽກຕົ້ນຕໍຢູ່ທີ່ນີ້ແມ່ນເຮັດໂດຍຟັງຊັນຂໍ້ຄວາມ Excel ມາດຕະຖານ ແທນ (ປ່ຽນແທນ), ເຊິ່ງມີ 3 ຂໍ້ໂຕ້ແຍ້ງ:
- ຂໍ້ຄວາມຕົ້ນສະບັບ - ທີ່ຢູ່ຫຍໍ້ທໍາອິດຈາກຖັນທີ່ຢູ່
- ສິ່ງທີ່ພວກເຮົາກໍາລັງຊອກຫາ - ໃນທີ່ນີ້ພວກເຮົາໃຊ້ trick ກັບຟັງຊັນ VIEW (ເບິ່ງ)ຈາກວິທີທີ່ຜ່ານມາເພື່ອດຶງຄ່າຈາກຖັນ ຊອກຫາ, ເຊິ່ງລວມເປັນຊິ້ນສ່ວນຢູ່ໃນທີ່ຢູ່ໂຄ້ງ.
- ສິ່ງທີ່ຈະທົດແທນ - ໃນທາງດຽວກັນພວກເຮົາຊອກຫາຄ່າທີ່ຖືກຕ້ອງທີ່ສອດຄ້ອງກັບມັນຈາກຖັນ ທົດແທນ.
ໃສ່ສູດນີ້ດ້ວຍ Ctrl+ການປ່ຽນແປງ+ກະລຸນາໃສ່ ບໍ່ຈໍາເປັນຢູ່ທີ່ນີ້, ເຖິງແມ່ນວ່າມັນເປັນ, ໃນຄວາມເປັນຈິງ, ສູດ array.
ແລະມັນເຫັນໄດ້ຊັດເຈນ (ເບິ່ງຂໍ້ຜິດພາດ #N / A ໃນຮູບທີ່ຜ່ານມາ) ວ່າສູດດັ່ງກ່າວ, ສໍາລັບຄວາມສະຫງ່າງາມຂອງມັນ, ມີຂໍ້ບົກຜ່ອງສອງຢ່າງ:
- ຫນ້າທີ່ SUBSTITUTE ແມ່ນຕົວພິມນ້ອຍໃຫຍ່, ດັ່ງນັ້ນ "Spb" ໃນເສັ້ນ penultimate ບໍ່ພົບຢູ່ໃນຕາຕະລາງທົດແທນ. ເພື່ອແກ້ໄຂບັນຫານີ້, ທ່ານສາມາດນໍາໃຊ້ຫນ້າທີ່ ZAMENIT (ປ່ຽນແທນ), ຫຼືເບື້ອງຕົ້ນເອົາຕາຕະລາງທັງສອງໄປຫາທະບຽນດຽວກັນ.
- ຖ້າຂໍ້ຄວາມແມ່ນຖືກຕ້ອງໃນເບື້ອງຕົ້ນຫຼືຢູ່ໃນມັນ ບໍ່ມີຊິ້ນສ່ວນທີ່ຈະທົດແທນ (ແຖວສຸດທ້າຍ), ຫຼັງຈາກນັ້ນສູດຂອງພວກເຮົາຖິ້ມຄວາມຜິດພາດ. ປັດຈຸບັນນີ້ສາມາດເປັນກາງໂດຍການຂັດຂວາງແລະປ່ຽນຄວາມຜິດພາດໂດຍໃຊ້ຟັງຊັນ IFERROR (IFERROR):
- ຖ້າຂໍ້ຄວາມຕົ້ນສະບັບປະກອບດ້ວຍ ຊິ້ນສ່ວນຫຼາຍຈາກໄດເລກະທໍລີໃນເວລາດຽວກັນ, ຫຼັງຈາກນັ້ນສູດຂອງພວກເຮົາທົດແທນພຽງແຕ່ອັນສຸດທ້າຍ (ໃນແຖວທີ 8, Ligovsky «ຖະຫນົນ« ປ່ຽນເປັນ "pr-t", ແຕ່ "S-Pb" on “ເຊນ. Petersburg” ບໍ່ມີຕໍ່ໄປອີກແລ້ວ, ເພາະວ່າ “S-Pb” ແມ່ນສູງກວ່າຢູ່ໃນໄດເລກະທໍລີ). ບັນຫານີ້ສາມາດແກ້ໄຂໄດ້ໂດຍການແລ່ນສູດຂອງພວກເຮົາເອງ, ແຕ່ວ່າຢູ່ຕາມຖັນ Fixed:
ບໍ່ສົມບູນແບບແລະຫຍຸ້ງຍາກໃນສະຖານທີ່, ແຕ່ດີກ່ວາການທົດແທນຄູ່ມືດຽວກັນ, ແມ່ນບໍ? 🙂
PS
ໃນບົດຄວາມຕໍ່ໄປ, ພວກເຮົາຈະຄິດວິທີການປະຕິບັດການທົດແທນຈໍານວນຫຼາຍດັ່ງກ່າວໂດຍໃຊ້ macros ແລະ Power Query.
- ຟັງຊັນ SUBSTITUTE ເຮັດວຽກແນວໃດເພື່ອແທນທີ່ຂໍ້ຄວາມ
- ຊອກຫາຂໍ້ຄວາມກົງກັນໂດຍໃຊ້ຟັງຊັນ EXACT
- ການຊອກຫາທີ່ລະອຽດອ່ອນຕົວອັກສອນແລະການທົດແທນ (ຕົວພິມນ້ອຍໃຫຍ່ VLOOKUP)