ເນື້ອໃນ
ນີ້ tutorial ພຽງເລັກນ້ອຍອະທິບາຍວິທີການເຮັດໃຫ້ຫນ້າທີ່ VPR (VLOOKUP) case-sensitive, ສະແດງໃຫ້ເຫັນຫຼາຍສູດອື່ນໆທີ່ Excel ສາມາດຄົ້ນຫາໃນລັກສະນະທີ່ລະອຽດອ່ອນ, ແລະຊີ້ໃຫ້ເຫັນຈຸດແຂງແລະຈຸດອ່ອນຂອງແຕ່ລະຫນ້າທີ່.
ຂ້ອຍເດົາວ່າຜູ້ໃຊ້ Excel ທຸກຄົນຮູ້ວ່າຫນ້າທີ່ໃດດໍາເນີນການຄົ້ນຫາແນວຕັ້ງ. ນັ້ນແມ່ນ, ມັນເປັນຫນ້າທີ່ VPR. ຢ່າງໃດກໍຕາມ, ມີຄົນຈໍານວນຫນ້ອຍຮູ້ວ່າ VPR ບໍ່ແມ່ນຕົວພິມນ້ອຍໃຫຍ່, ເຊັ່ນ: ຕົວພິມນ້ອຍ ແລະຕົວພິມໃຫຍ່ແມ່ນຄືກັນ.
ນີ້ແມ່ນຕົວຢ່າງສັ້ນໆທີ່ສະແດງໃຫ້ເຫັນເຖິງຄວາມບໍ່ສາມາດ VPR ຮັບຮູ້ການລົງທະບຽນ. ສົມມຸດວ່າຢູ່ໃນຫ້ອງ A1 ປະກອບມີຄ່າ "ໃບບິນ" ແລະຕາລາງ A2 - “ໃບບິນ”, ສູດ:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… ຈະຢຸດການຄົ້ນຫາຂອງມັນໃນ “ໃບບິນ” ເພາະວ່າຄ່ານັ້ນມາທຳອິດໃນລາຍການ, ແລະສະກັດເອົາຄ່າອອກຈາກເຊລ B1.
ຕໍ່ມາໃນບົດຄວາມນີ້, ຂ້ອຍຈະສະແດງວິທີການເຮັດ VPR ກໍລະນີທີ່ລະອຽດອ່ອນ. ນອກຈາກນັ້ນ, ພວກເຮົາຈະຮຽນຮູ້ບາງຫນ້າທີ່ເພີ່ມເຕີມທີ່ສາມາດດໍາເນີນການຄົ້ນຫາທີ່ລະອຽດອ່ອນໃນ Excel.
ພວກເຮົາຈະເລີ່ມຕົ້ນດ້ວຍການງ່າຍດາຍທີ່ສຸດ - VIEW (LOOKUP) ແລະ SUMPRODUCT (SUMPRODUCT), ເຊິ່ງ, ແຕ່ຫນ້າເສຍດາຍ, ມີຂໍ້ຈໍາກັດທີ່ສໍາຄັນຈໍານວນຫນຶ່ງ. ຕໍ່ໄປ, ພວກເຮົາຈະພິຈາລະນາຢ່າງລະອຽດກ່ຽວກັບສູດທີ່ສັບສົນເລັກນ້ອຍ INDEX+MATCH (INDEX+MATCH), ເຊິ່ງເຮັດວຽກໄດ້ຢ່າງບໍ່ມີຂໍ້ບົກພ່ອງໃນທຸກສະຖານະການ ແລະກັບຊຸດຂໍ້ມູນໃດໆ.
ຟັງຊັນ VLOOKUP ແມ່ນຕົວພິມນ້ອຍໃຫຍ່
ດັ່ງທີ່ທ່ານຮູ້ແລ້ວ, ຫນ້າທີ່ປົກກະຕິ VPR ແມ່ນກໍລະນີທີ່ບໍ່ລະອຽດອ່ອນ. ຢ່າງໃດກໍ່ຕາມ, ມີວິທີທີ່ຈະເຮັດໃຫ້ມັນມີຄວາມລະອຽດອ່ອນ. ເພື່ອເຮັດສິ່ງນີ້, ທ່ານຈໍາເປັນຕ້ອງເພີ່ມຖັນເສີມໃສ່ຕາຕະລາງ, ດັ່ງທີ່ສະແດງຢູ່ໃນຕົວຢ່າງຕໍ່ໄປນີ້.
ສົມມຸດວ່າຢູ່ໃນຖັນ B ມີຕົວລະບຸຜະລິດຕະພັນ (ລາຍການ) ແລະທ່ານຕ້ອງການສະກັດລາຄາຂອງຜະລິດຕະພັນແລະຄໍາຄິດຄໍາເຫັນທີ່ສອດຄ້ອງກັນຈາກຄໍລໍາ. C и D. ບັນຫາແມ່ນວ່າຕົວລະບຸມີທັງຕົວພິມນ້ອຍ ແລະຕົວພິມໃຫຍ່. ຕົວຢ່າງ, ຄ່າເຊລ B4 (001Tvci3u) ແລະ B5 (001Tvci3U) ແຕກຕ່າງກັນພຽງແຕ່ໃນກໍລະນີຂອງຕົວອັກສອນສຸດທ້າຍ, u и U ຕາມລໍາດັບ.
ຕາມທີ່ທ່ານສາມາດຈິນຕະນາການ, ສູດການຄົ້ນຫາປົກກະຕິ
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
ຈະກັບຄືນ $ 90, ນັບຕັ້ງແຕ່ມູນຄ່າ 001Tvci3u ແມ່ນຢູ່ໃນໄລຍະການຄົ້ນຫາກ່ອນຫນ້າ 001Tvci3U. ແຕ່ນັ້ນບໍ່ແມ່ນສິ່ງທີ່ພວກເຮົາຕ້ອງການ, ແມ່ນບໍ?
ເພື່ອຄົ້ນຫາດ້ວຍຟັງຊັນ VPR ໃນກໍລະນີທີ່ລະອຽດອ່ອນຂອງ Excel, ທ່ານຈະຕ້ອງເພີ່ມຖັນຜູ້ຊ່ວຍແລະຕື່ມຂໍ້ມູນໃສ່ຈຸລັງຂອງມັນດ້ວຍສູດຕໍ່ໄປນີ້ (ບ່ອນທີ່ B ແມ່ນຖັນຊອກຫາ):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
ສູດນີ້ແຍກຄ່າທີ່ຕ້ອງການເປັນຕົວອັກສອນແຍກຕ່າງຫາກ, ແທນທີ່ແຕ່ລະຕົວອັກສອນດ້ວຍລະຫັດຂອງມັນ (ຕົວຢ່າງ, ແທນທີ່ຈະເປັນ A ຢູ່ທີ່ 65, ແທນທີ່ຈະ a ລະຫັດ 97) ແລະຫຼັງຈາກນັ້ນລວມລະຫັດເຫຼົ່ານີ້ເຂົ້າໄປໃນສາຍຕົວເລກທີ່ເປັນເອກະລັກ.
ຫຼັງຈາກນັ້ນ, ພວກເຮົາໃຊ້ຫນ້າທີ່ງ່າຍດາຍ VPR ສຳລັບການຄົ້ນຫາທີ່ລະອຽດອ່ອນ:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
ການປະຕິບັດຫນ້າທີ່ທີ່ເຫມາະສົມ VPR case-sensitive ແມ່ນຂຶ້ນກັບສອງປັດໃຈ:
- ຖັນຜູ້ຊ່ວຍຕ້ອງເປັນຖັນຊ້າຍສຸດໃນຂອບເຂດທີ່ສາມາດເບິ່ງໄດ້.
- ຄ່າທີ່ທ່ານກໍາລັງຊອກຫາຕ້ອງມີລະຫັດຕົວອັກສອນແທນທີ່ຈະເປັນມູນຄ່າທີ່ແທ້ຈິງ.
ວິທີການໃຊ້ຟັງຊັນ CODE ຢ່າງຖືກຕ້ອງ
ສູດທີ່ໃສ່ເຂົ້າໄປໃນຈຸລັງຂອງຖັນຊ່ວຍສົມມຸດວ່າທຸກຄ່າຄົ້ນຫາຂອງເຈົ້າມີຈໍານວນຕົວອັກສອນດຽວກັນ. ຖ້າບໍ່, ຫຼັງຈາກນັ້ນທ່ານຈໍາເປັນຕ້ອງຮູ້ຕົວເລກທີ່ນ້ອຍທີ່ສຸດແລະໃຫຍ່ທີ່ສຸດແລະເພີ່ມລັກສະນະຫຼາຍ IFERROR (IFERROR) ຈໍານວນຕົວອັກສອນແມ່ນຄວາມແຕກຕ່າງລະຫວ່າງຄ່າຄົ້ນຫາທີ່ສັ້ນທີ່ສຸດແລະຍາວທີ່ສຸດ.
ຕົວຢ່າງ, ຖ້າຄ່າຄົ້ນຫາທີ່ສັ້ນທີ່ສຸດແມ່ນ 3 ຕົວອັກສອນ ແລະຍາວທີ່ສຸດແມ່ນ 5 ຕົວອັກສອນ, ໃຫ້ໃຊ້ສູດນີ້:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
ສໍາລັບຫນ້າທີ່ ປທສ (ກາງ) ທ່ານໃຫ້ຂໍ້ໂຕ້ແຍ້ງຕໍ່ໄປນີ້:
- ການໂຕ້ຖຽງທີ 1 - ຂໍ້ຄວາມ (ຂໍ້ຄວາມ) ແມ່ນການອ້າງອີງຂໍ້ຄວາມ ຫຼືຕາລາງທີ່ມີຕົວອັກສອນທີ່ຈະແຍກອອກ (ໃນກໍລະນີຂອງພວກເຮົາມັນແມ່ນ B2)
- ການໂຕ້ຖຽງທີ 2 - start_num (start_position) ແມ່ນຕຳແໜ່ງທຳອິດຂອງຕົວອັກສອນເຫຼົ່ານັ້ນທີ່ຈະຖືກສະກັດອອກ. ເຈົ້າເຂົ້າ 1 ໃນຫນ້າທໍາອິດ ປທສ, 2 - ໃນຫນ້າທີ່ທີສອງ ປທສ ແລະອື່ນໆ
- ການໂຕ້ຖຽງທີ 3 - ຕົວເລກ (number_of_characters) – ລະບຸຈໍານວນຕົວອັກສອນທີ່ຈະແຍກອອກຈາກຂໍ້ຄວາມ. ເນື່ອງຈາກພວກເຮົາຕ້ອງການພຽງແຕ່ 1 ຕົວອັກສອນຕະຫຼອດເວລາ, ໃນທຸກຫນ້າທີ່ພວກເຮົາຂຽນ 1.
ຂໍ້ກໍານົດ: ຫນ້າທີ່ VPR ບໍ່ແມ່ນການແກ້ໄຂທີ່ດີທີ່ສຸດສໍາລັບການຄົ້ນຫາທີ່ລະອຽດອ່ອນໃນ Excel. ກ່ອນອື່ນ ໝົດ, ການເພີ່ມຖັນເສີມແມ່ນຕ້ອງການ. ອັນທີສອງ, ສູດໄດ້ເຮັດວຽກທີ່ດີພຽງແຕ່ຖ້າຂໍ້ມູນແມ່ນ homogeneous, ຫຼືຕົວເລກທີ່ແນ່ນອນຂອງຕົວອັກສອນໃນຄ່າທີ່ຄົ້ນຫາແມ່ນເປັນທີ່ຮູ້ຈັກ. ຖ້ານີ້ບໍ່ແມ່ນກໍລະນີຂອງເຈົ້າ, ມັນກໍ່ດີກວ່າທີ່ຈະໃຊ້ຫນຶ່ງໃນການແກ້ໄຂທີ່ພວກເຮົາສະແດງຂ້າງລຸ່ມນີ້.
ຟັງຊັນ LOOKUP ສໍາລັບການຊອກຫາທີ່ລະອຽດອ່ອນຕົວພິມນ້ອຍ
ຫນ້າທີ່ VIEW (LOOKUP) ທີ່ກ່ຽວຂ້ອງ VPR, ແນວໃດກໍ່ຕາມ syntax ຂອງມັນອະນຸຍາດໃຫ້ຊອກຫາກໍລະນີທີ່ລະອຽດອ່ອນໂດຍບໍ່ມີການເພີ່ມຖັນຊ່ວຍ. ເພື່ອເຮັດສິ່ງນີ້, ໃຊ້ VIEW ບວກກັບຫນ້າທີ່ ຄວາມຈິງ (ແນ່ນອນ).
ຖ້າພວກເຮົາເອົາຂໍ້ມູນຈາກຕົວຢ່າງທີ່ຜ່ານມາ (ໂດຍບໍ່ມີຖັນເສີມ), ສູດຕໍ່ໄປນີ້ຈະຮັບມືກັບຫນ້າວຽກ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
ຄົ້ນຫາສູດໃນຂອບເຂດ A2:A7 ກົງກັນທີ່ແນ່ນອນກັບຄ່າເຊລ F2 case sensitive ແລະສົ່ງຄືນຄ່າຈາກຖັນ B ຂອງແຖວດຽວກັນ.
ຄື VPRຫນ້າທີ່ VIEW ເຮັດວຽກເທົ່າທຽມກັນກັບຂໍ້ຄວາມແລະຄ່າຕົວເລກ, ດັ່ງທີ່ທ່ານສາມາດເບິ່ງໃນ screenshot ຂ້າງລຸ່ມນີ້:
ສໍາຄັນ! ໃນຄໍາສັ່ງສໍາລັບຫນ້າທີ່ VIEW ເຮັດວຽກຢ່າງຖືກຕ້ອງ, ຄ່າໃນຖັນຊອກຫາຄວນຈະຖືກຈັດຮຽງຕາມລໍາດັບຈາກໃຫຍ່ຫານ້ອຍ, ເຊັ່ນ: ຈາກນ້ອຍສຸດໄປຫາໃຫຍ່ທີ່ສຸດ.
ໃຫ້ຂ້ອຍອະທິບາຍສັ້ນໆວ່າຟັງຊັນເຮັດວຽກແນວໃດ ຄວາມຈິງ ໃນສູດທີ່ສະແດງຂ້າງເທິງ, ນີ້ແມ່ນຈຸດສໍາຄັນ.
ຫນ້າທີ່ ຄວາມຈິງ ປຽບທຽບສອງຄ່າຂໍ້ຄວາມໃນອາກິວເມັນທີ 1 ແລະ 2 ແລະສົ່ງຄືນ TRUE ຖ້າພວກມັນຄືກັນແທ້ ຫຼື FALSE ຖ້າພວກມັນບໍ່ແມ່ນ. ມັນເປັນສິ່ງສໍາຄັນສໍາລັບພວກເຮົາຫນ້າທີ່ ຄວາມຈິງ ກໍລະນີທີ່ລະອຽດອ່ອນ.
ໃຫ້ເບິ່ງວິທີການເຮັດວຽກຂອງສູດຂອງພວກເຮົາ ເບິ່ງ+ແທ້:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- ຫນ້າທີ່ ຄວາມຈິງ ປຽບທຽບຄ່າເຊລ F2 ກັບອົງປະກອບທັງຫມົດໃນຖັນ A (A2:A7). ຕອບ TRUE ຖ້າພົບການກົງກັນທີ່ແນ່ນອນ, ຖ້າບໍ່ດັ່ງນັ້ນ FALSE.
- ນັບຕັ້ງແຕ່ທ່ານໃຫ້ການໂຕ້ຖຽງຫນ້າທີ່ທໍາອິດ VIEW value TRUE, ມັນສະກັດຄ່າທີ່ສອດຄ້ອງກັນຈາກຖັນທີ່ລະບຸ (ໃນກໍລະນີຂອງພວກເຮົາ, ຖັນ B) ພຽງແຕ່ຖ້າພົບກົງກັນທີ່ແນ່ນອນ, ກໍລະນີທີ່ລະອຽດອ່ອນ.
ຂ້າພະເຈົ້າຫວັງວ່າຄໍາອະທິບາຍນີ້ແມ່ນຈະແຈ້ງແລະໃນປັດຈຸບັນທ່ານເຂົ້າໃຈແນວຄວາມຄິດຕົ້ນຕໍ. ຖ້າເປັນດັ່ງນັ້ນ, ຫຼັງຈາກນັ້ນທ່ານຈະບໍ່ມີຄວາມຫຍຸ້ງຍາກກັບຫນ້າທີ່ອື່ນໆທີ່ພວກເຮົາຈະວິເຄາະຕື່ມອີກ, ເພາະວ່າ. ພວກເຂົາທັງຫມົດເຮັດວຽກຢູ່ໃນຫຼັກການດຽວກັນ.
ຂໍ້ກໍານົດ: ຂໍ້ມູນໃນຖັນຊອກຫາຈະຕ້ອງຖືກຈັດຮຽງຈາກນ້ອຍຫາໃຫຍ່.
SUMPRODUCT – ຊອກຫາຄ່າຂໍ້ຄວາມ, ໂຕພິມນ້ອຍໃຫຍ່, ແຕ່ສົ່ງຄືນຕົວເລກເທົ່ານັ້ນ
ດັ່ງທີ່ທ່ານເຂົ້າໃຈແລ້ວຈາກຫົວຂໍ້, SUMPRODUCT (SUMPRODUCT) ເປັນອີກໜຶ່ງຟັງຊັນ Excel ທີ່ຈະຊ່ວຍໃຫ້ທ່ານເຮັດການຄົ້ນຫາທີ່ລະອຽດອ່ອນ, ແຕ່ຈະໃຫ້ຄ່າເປັນຕົວເລກເທົ່ານັ້ນ. ຖ້າທາງເລືອກນີ້ບໍ່ເຫມາະສົມກັບທ່ານ, ຫຼັງຈາກນັ້ນທ່ານສາມາດດໍາເນີນການກັບມັດໄດ້ທັນທີ INDEX+MATCH, ເຊິ່ງເຮັດໃຫ້ການແກ້ໄຂສໍາລັບກໍລະນີໃດກໍ່ຕາມແລະສໍາລັບປະເພດຂໍ້ມູນໃດໆ.
ກ່ອນອື່ນ, ໃຫ້ຂ້ອຍອະທິບາຍສັ້ນໆກ່ຽວກັບ syntax ຂອງຟັງຊັນນີ້, ນີ້ຈະຊ່ວຍໃຫ້ທ່ານເຂົ້າໃຈດີຂື້ນກັບສູດທີ່ລະອຽດອ່ອນຂອງກໍລະນີຕໍ່ໄປນີ້.
ຫນ້າທີ່ SUMPRODUCT ຄູນອົງປະກອບຂອງອາເຣທີ່ໃຫ້ໄວ້ ແລະສົ່ງຜົນລວມຂອງຜົນໄດ້ຮັບ. syntax ເບິ່ງຄືວ່ານີ້:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
ເນື່ອງຈາກພວກເຮົາຕ້ອງການການຄົ້ນຫາທີ່ລະອຽດອ່ອນ, ພວກເຮົາໃຊ້ຟັງຊັນ ຄວາມຈິງ (EXACT) ຈາກຕົວຢ່າງທີ່ຜ່ານມາເປັນຫນຶ່ງໃນຕົວຄູນ:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
ດັ່ງທີ່ທ່ານຈື່, ຄວາມຈິງ ປຽບທຽບຄ່າເຊລ F2 ກັບອົງປະກອບທັງຫມົດໃນຖັນ A. ຕອບ TRUE ຖ້າພົບການກົງກັນທີ່ແນ່ນອນ, ຖ້າບໍ່ດັ່ງນັ້ນ FALSE. ໃນການດໍາເນີນງານທາງຄະນິດສາດ, Excel ໃຊ້ TRUE ເປັນ 1, ແລະ FALSE ສໍາລັບ 0ນອກຈາກນັ້ນ SUMPRODUCT ຄູນຕົວເລກເຫຼົ່ານີ້ແລະຜົນລວມ.
ເລກສູນບໍ່ໄດ້ຖືກນັບເພາະວ່າເມື່ອຄູນພວກມັນໃຫ້ສະເໝີ 0. ຂໍໃຫ້ພິຈາລະນາຢ່າງລະອຽດກ່ຽວກັບສິ່ງທີ່ເກີດຂື້ນໃນເວລາທີ່ກົງກັນທີ່ແນ່ນອນໃນຖັນ A ພົບເຫັນແລະກັບຄືນ 1… ໜ້າທີ່ SUMPRODUCT ຄູນຕົວເລກໃນຖັນ B on 1 ແລະສົ່ງຜົນໄດ້ຮັບ - ຕົວເລກດຽວກັນແທ້! ນີ້ແມ່ນຍ້ອນວ່າຜົນໄດ້ຮັບຂອງຜະລິດຕະພັນອື່ນໆແມ່ນສູນ, ແລະພວກມັນບໍ່ມີຜົນຕໍ່ຜົນລວມ.
ແຕ່ຫນ້າເສຍດາຍ, ຫນ້າທີ່ SUMPRODUCT ບໍ່ສາມາດເຮັດວຽກກັບຄ່າຂໍ້ຄວາມແລະວັນທີຍ້ອນວ່າພວກເຂົາບໍ່ສາມາດຄູນໄດ້. ໃນກໍລະນີນີ້, ທ່ານຈະໄດ້ຮັບຂໍ້ຄວາມສະແດງຂໍ້ຜິດພາດ #VALUE! (#VALUE!) ເຊັ່ນດຽວກັບຢູ່ໃນຕາລາງ F4 ໃນຮູບຂ້າງລຸ່ມນີ້:
ຂໍ້ກໍານົດ: ຕອບພຽງແຕ່ຄ່າຕົວເລກ.
INDEX + MATCH - ການຄົ້ນຫາທີ່ລະອຽດອ່ອນຕົວພິມນ້ອຍສໍາລັບປະເພດຂອງຂໍ້ມູນໃດໆ
ສຸດທ້າຍ, ພວກເຮົາຢູ່ໃກ້ກັບສູດການຄົ້ນຫາແບບບໍ່ຈໍາກັດແລະບໍ່ຈໍາກັດຕົວພິມໃຫຍ່ທີ່ເຮັດວຽກກັບຊຸດຂໍ້ມູນໃດໆ.
ຕົວຢ່າງນີ້ມາສຸດທ້າຍ, ບໍ່ແມ່ນຍ້ອນວ່າສິ່ງທີ່ດີທີ່ສຸດຖືກປະໄວ້ສໍາລັບ dessert, ແຕ່ເນື່ອງຈາກວ່າຄວາມຮູ້ທີ່ໄດ້ຮັບຈາກຕົວຢ່າງທີ່ຜ່ານມາຈະຊ່ວຍໃຫ້ທ່ານເຂົ້າໃຈສູດທີ່ລະອຽດອ່ອນຂອງກໍລະນີທີ່ດີກວ່າແລະໄວຂຶ້ນ. INDEX+MATCH (INDEX+MATCH).
ດັ່ງທີ່ທ່ານອາດຈະຄາດເດົາ, ການປະສົມປະສານຂອງຫນ້າທີ່ ເປີດເຜີຍຫຼາຍຂຶ້ນ и INDEX ໃຊ້ໃນ Excel ເປັນທາງເລືອກທີ່ມີຄວາມຍືດຫຍຸ່ນແລະມີອໍານາດຫຼາຍສໍາລັບ VPR. ບົດຄວາມການນໍາໃຊ້ INDEX ແລະ MATCH ແທນ VLOOKUP ຈະອະທິບາຍຢ່າງສົມບູນວ່າຫນ້າທີ່ເຫຼົ່ານີ້ເຮັດວຽກຮ່ວມກັນແນວໃດ.
ຂ້າພະເຈົ້າພຽງແຕ່ຈະສະຫຼຸບຈຸດສໍາຄັນ:
- ຫນ້າທີ່ ເປີດເຜີຍຫຼາຍຂຶ້ນ (MATCH) ຄົ້ນຫາຄ່າໃນຂອບເຂດທີ່ໃຫ້ໄວ້ ແລະສົ່ງຄືນຕຳແໜ່ງທີ່ກ່ຽວຂ້ອງຂອງມັນ, ນັ້ນແມ່ນ, ແຖວ ແລະ/ຫຼື ຖັນແຖວ;
- ຕໍ່ໄປ, ຫນ້າທີ່ INDEX (INDEX) ຕອບຄ່າຈາກຖັນທີ່ລະບຸ ແລະ/ຫຼືແຖວ.
ສູດ INDEX+MATCH ສາມາດຄົ້ນຫາກໍລະນີທີ່ລະອຽດອ່ອນ, ທ່ານພຽງແຕ່ຕ້ອງການເພີ່ມຫນຶ່ງຫນ້າທີ່ໃສ່ມັນ. ມັນບໍ່ຍາກທີ່ຈະຄາດເດົາວ່າມັນແມ່ນຫຍັງອີກເທື່ອຫນຶ່ງ ຄວາມຈິງ (ແນ່ນອນ):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
ໃນສູດນີ້ ຄວາມຈິງ ເຮັດວຽກໃນລັກສະນະດຽວກັນກັບການສົມທົບກັບຫນ້າທີ່ VIEW, ແລະໃຫ້ຜົນໄດ້ຮັບດຽວກັນ:
ໃຫ້ສັງເກດວ່າສູດ INDEX+MATCH enclosed ໃນ curly braces ເປັນສູດ array ແລະທ່ານຕ້ອງເຮັດສໍາເລັດມັນໂດຍການກົດ Ctrl + Shift + Enter.
ເປັນຫຍັງ INDEX+MATCH ຈຶ່ງເປັນທາງອອກທີ່ດີທີ່ສຸດສໍາລັບການຄົ້ນຫາທີ່ລະອຽດອ່ອນ?
ຂໍ້ໄດ້ປຽບຕົ້ນຕໍຂອງມັດ INDEX и ເປີດເຜີຍຫຼາຍຂຶ້ນ:
- ບໍ່ຈໍາເປັນຕ້ອງເພີ່ມຖັນຊ່ວຍ, ບໍ່ເຫມືອນກັບ VPR.
- ບໍ່ຕ້ອງການຄໍລໍາຄົ້ນຫາເພື່ອຈັດຮຽງ, ບໍ່ເຫມືອນກັບ VIEW.
- ເຮັດວຽກກັບທຸກປະເພດຂອງຂໍ້ມູນ - ຕົວເລກ, ຂໍ້ຄວາມແລະວັນທີ.
ສູດນີ້ເບິ່ງຄືວ່າສົມບູນແບບ, ບໍ່ແມ່ນບໍ? ໃນຄວາມເປັນຈິງ, ມັນບໍ່ແມ່ນ. ແລະວ່າເປັນຫຍັງ.
ສົມມຸດວ່າຕາລາງໃນຖັນຄ່າຕອບແທນທີ່ກ່ຽວຂ້ອງກັບຄ່າຊອກຫານັ້ນຫວ່າງເປົ່າ. ສູດຈະສົ່ງຄືນຜົນໄດ້ຮັບຫຍັງ? ບໍ່? ເຮົາມາເບິ່ງກັນວ່າສູດໃດໃຫ້ຜົນຕອບແທນແທ້ໆ:
ອຸຍ, ສູດຕອບແທນສູນ! ນີ້ອາດຈະບໍ່ເປັນບັນຫາໃຫຍ່ຖ້າທ່ານກໍາລັງເຮັດວຽກກັບຄ່າຂໍ້ຄວາມທີ່ບໍລິສຸດ. ຢ່າງໃດກໍຕາມ, ຖ້າຕາຕະລາງປະກອບມີຕົວເລກ, ລວມທັງສູນ "ຈິງ", ນີ້ຈະກາຍເປັນບັນຫາ.
ໃນຄວາມເປັນຈິງ, ສູດການຊອກຫາອື່ນໆທັງຫມົດ (VLOOKUP, LOOKUP, ແລະ SUMPRODUCT) ທີ່ພວກເຮົາໄດ້ສົນທະນາກ່ອນຫນ້ານີ້ມີພຶດຕິກໍາດຽວກັນ. ແຕ່ທ່ານຕ້ອງການສູດທີ່ສົມບູນແບບ, ແມ່ນບໍ?
ເພື່ອເຮັດໃຫ້ສູດຄຳນວນທີ່ລະອຽດອ່ອນ INDEX+MATCH ທີ່ສົມບູນແບບ, ເຮັດໃຫ້ມັນຢູ່ໃນຫນ້າທີ່ IF (IF) ທີ່ຈະທົດສອບເຊລທີ່ມີຄ່າສົ່ງຄືນ ແລະໃຫ້ຜົນໄດ້ຮັບທີ່ຫວ່າງເປົ່າ ຖ້າມັນຫວ່າງເປົ່າ:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
ໃນສູດນີ້:
- B ແມ່ນຖັນທີ່ມີຄ່າຕອບແທນ
- 1+ ແມ່ນຕົວເລກທີ່ປ່ຽນຕຳແໜ່ງຂອງເຊລທີ່ສົ່ງກັບໂດຍຟັງຊັນ ເປີດເຜີຍຫຼາຍຂຶ້ນ, ກັບທີ່ຢູ່ທີ່ແທ້ຈິງຂອງເຊນ. ຕົວຢ່າງ, ໃນຫນ້າທີ່ຂອງພວກເຮົາ ເປີດເຜີຍຫຼາຍຂຶ້ນ array ຄົ້ນຫາໃຫ້ A2:A7, ນັ້ນແມ່ນ, ຕໍາແຫນ່ງທີ່ກ່ຽວຂ້ອງຂອງເຊນ A2 ຈະ 1, ເນື່ອງຈາກວ່າມັນເປັນອັນທໍາອິດໃນ array. ແຕ່ຕໍາແຫນ່ງຕົວຈິງຂອງເຊນ A2 ໃນຖັນແມ່ນ 2, ດັ່ງນັ້ນພວກເຮົາເພີ່ມ 1ເພື່ອເຮັດໃຫ້ເຖິງຄວາມແຕກຕ່າງແລະມີຫນ້າທີ່ ປະຕິບັດຕາມ (INDIRECT) ດຶງເອົາຄ່າຈາກຕາລາງທີ່ຕ້ອງການ.
ຮູບພາບຂ້າງລຸ່ມນີ້ສະແດງໃຫ້ເຫັນສູດການແກ້ໄຂທີ່ລະອຽດອ່ອນຕົວພິມນ້ອຍ INDEX+MATCH ໃນການປະຕິບັດ. ມັນສົ່ງຄືນຜົນໄດ້ຮັບທີ່ຫວ່າງເປົ່າຖ້າຕາລາງທີ່ສົ່ງຄືນແມ່ນຫວ່າງເປົ່າ.
ຂ້າພະເຈົ້າໄດ້ຂຽນສູດໃຫມ່ເຂົ້າໄປໃນຖັນ B:Dເພື່ອໃຫ້ພໍດີກັບແຖບສູດໃນຮູບໜ້າຈໍ.
ສູດຜົນຕອບແທນ 0ຖ້າຕາລາງທີ່ສົ່ງຄືນມີສູນ.
ຖ້າທ່ານຕ້ອງການການເຊື່ອມຕໍ່ INDEX и ເປີດເຜີຍຫຼາຍຂຶ້ນ ສະແດງຂໍ້ຄວາມບາງຢ່າງເມື່ອຄ່າກັບຄືນຫວ່າງເປົ່າ, ທ່ານສາມາດຂຽນມັນຢູ່ໃນວົງຢືມສຸດທ້າຍ (“”) ຂອງສູດ, ຕົວຢ່າງເຊັ່ນນີ້:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
