ການນໍາໃຊ້ຟັງຊັນ VLOOKUP ເພື່ອທົດແທນຄ່າ

ໃຜຂີ້ຄ້ານ ຫຼື ບໍ່ມີເວລາອ່ານ - ເບິ່ງວິດີໂອ. ລາຍລະອຽດແລະ nuances ແມ່ນຢູ່ໃນຂໍ້ຄວາມຂ້າງລຸ່ມນີ້.

ການສ້າງບັນຫາ

ດັ່ງນັ້ນ, ພວກເຮົາມີສອງຕາຕະລາງ - ຕາຕະລາງສັ່ງ и ລາຍ​ການ​ລາ​ຄາ:

ວຽກງານແມ່ນເພື່ອທົດແທນລາຄາຈາກລາຍະການລາຄາເຂົ້າໄປໃນຕາຕະລາງຄໍາສັ່ງອັດຕະໂນມັດ, ສຸມໃສ່ຊື່ຂອງຜະລິດຕະພັນດັ່ງນັ້ນຕໍ່ມາທ່ານສາມາດຄິດໄລ່ຄ່າໃຊ້ຈ່າຍ.

ການແກ້ໄຂ

ໃນຊຸດຟັງຊັນ Excel, ພາຍໃຕ້ຫມວດຫມູ່ ເອກະສານອ້າງອີງ ແລະ arrays (ຊອກຫາ ແລະອ້າງອີງ) ມີຫນ້າທີ່ VPR (VLOOKUP).ຟັງຊັນນີ້ຊອກຫາຄ່າທີ່ໃຫ້ (ໃນຕົວຢ່າງຂອງພວກເຮົາ, ນີ້ແມ່ນຄໍາວ່າ "ຫມາກໂປມ") ໃນຖັນຊ້າຍສຸດຂອງຕາຕະລາງທີ່ກໍານົດໄວ້ (ລາຍການລາຄາ) ຍ້າຍຈາກເທິງລົງລຸ່ມແລະ, ເມື່ອພົບມັນ, ສະແດງເນື້ອໃນຂອງເຊນທີ່ຢູ່ໃກ້ຄຽງ. (23 ຮູເບີນ).

ເພື່ອຄວາມງ່າຍໃນການນໍາໃຊ້ຟັງຊັນຕໍ່ໄປ, ເຮັດສິ່ງຫນຶ່ງໃນເວລາດຽວ - ໃຫ້ຊ່ວງຂອງຈຸລັງໃນລາຍະການຊື່ຂອງເຈົ້າເອງ. ເພື່ອເຮັດສິ່ງນີ້, ເລືອກເອົາຈຸລັງທັງຫມົດຂອງລາຍການລາຄາຍົກເວັ້ນ "ສ່ວນຫົວ" (G3: H19), ເລືອກຈາກເມນູ. ໃສ່ – ຊື່ – ມອບໝາຍ (ໃສ່ - ຊື່ - ກໍານົດ) ຫຼືກົດ CTRL + F3 ແລະໃສ່ຊື່ໃດນຶ່ງ (ບໍ່ມີຍະຫວ່າງ) ຄື ລາຄາ… ໃນປັດຈຸບັນ, ໃນອະນາຄົດ, ທ່ານສາມາດນໍາໃຊ້ຊື່ນີ້ເພື່ອເຊື່ອມຕໍ່ກັບລາຍການລາຄາ.

ໃນປັດຈຸບັນພວກເຮົານໍາໃຊ້ຫນ້າທີ່ VPR… ເລືອກຕາລາງທີ່ມັນຈະຖືກເຂົ້າໄປ (D3) ແລະເປີດແຖບ ສູດ – ການແຊກຟັງຊັນ (ສູດຄຳນວນ — ໃສ່ຟັງຊັນ)… ຢູ່ໃນປະເພດ ເອກະສານອ້າງອີງ ແລະ arrays (ຊອກຫາ ແລະອ້າງອີງ) ຊອກຫາຫນ້າທີ່ VPR (VLOOKUP) ແລະກົດ OK… ປ່ອງ​ຢ້ຽມ​ສໍາ​ລັບ​ການ​ປ້ອນ​ຂໍ້​ກໍາ​ນົດ​ສໍາ​ລັບ​ການ​ທໍາ​ງານ​ຈະ​ປາ​ກົດ​ຂຶ້ນ​:

ການນໍາໃຊ້ຟັງຊັນ VLOOKUP ເພື່ອທົດແທນຄ່າ

ພວກເຮົາຕື່ມຂໍ້ມູນໃສ່ພວກມັນ:

  • ມູນຄ່າທີ່ຕ້ອງການ (ຄ່າຊອກຫາ) - ຊື່ຂອງຜະລິດຕະພັນທີ່ຟັງຊັນຄວນຊອກຫາຢູ່ໃນຖັນຊ້າຍສຸດຂອງລາຍການລາຄາ. ໃນກໍລະນີຂອງພວກເຮົາ, ຄໍາວ່າ "ຫມາກໂປມ" ຈາກເຊນ B3.
  • ຕາຕະລາງ (ຕາຕະລາງ Array) – ຕາຕະລາງທີ່ຄ່າທີ່ຕ້ອງການ uXNUMXbuXNUMXbare, ນັ້ນແມ່ນ, ບັນຊີລາຍຊື່ລາຄາຂອງພວກເຮົາ. ສໍາລັບການອ້າງອິງ, ພວກເຮົາໃຊ້ຊື່ຂອງພວກເຮົາເອງ "ລາຄາ" ທີ່ໃຫ້ກ່ອນຫນ້ານີ້. ຖ້າທ່ານບໍ່ໄດ້ບອກຊື່, ທ່ານພຽງແຕ່ສາມາດເລືອກຕາຕະລາງ, ແຕ່ ຢ່າລືມກົດປຸ່ມ F4ເພື່ອປັກໝຸດການເຊື່ອມຕໍ່ດ້ວຍເຄື່ອງໝາຍເງິນໂດລາ, ເພາະວ່າຖ້າບໍ່ດັ່ງນັ້ນ, ມັນຈະເລື່ອນລົງເມື່ອຄັດລອກສູດຂອງພວກເຮົາລົງໄປຫາສ່ວນທີ່ເຫຼືອຂອງຈຸລັງໃນຖັນ D3:D30.
  • Column_number (ເລກດັດຊະນີຖັນ) – ເລກລໍາດັບ (ບໍ່ແມ່ນຕົວອັກສອນ!) ຂອງຖັນໃນລາຍການລາຄາທີ່ພວກເຮົາຈະເອົາຄ່າລາຄາ. ຖັນທໍາອິດຂອງລາຍການລາຄາທີ່ມີຊື່ແມ່ນເລກ 1, ດັ່ງນັ້ນພວກເຮົາຕ້ອງການລາຄາຈາກຖັນເລກ 2.
  • interval_lookup (ການຊອກຫາໄລຍະ) - ພຽງແຕ່ສອງຄ່າສາມາດໃສ່ໃນຊ່ອງນີ້: FALSE ຫຼື TRUE:
      • ຖ້າຄ່າຖືກໃສ່ 0 or ນອນ (ຜິດ), ຫຼັງຈາກນັ້ນໃນຄວາມເປັນຈິງນີ້ຫມາຍຄວາມວ່າພຽງແຕ່ການຊອກຫາແມ່ນອະນຸຍາດ ກົງກັນແນ່ນອນie ຖ້າຟັງຊັນບໍ່ພົບລາຍການທີ່ບໍ່ແມ່ນມາດຕະຖານທີ່ລະບຸໄວ້ໃນຕາຕະລາງຄໍາສັ່ງໃນລາຍະການລາຄາ (ຖ້າ "ຫມາກພ້າວ" ຖືກໃສ່, ສໍາລັບການຍົກຕົວຢ່າງ), ມັນຈະສ້າງຂໍ້ຜິດພາດ #N / ​​A (ບໍ່ມີຂໍ້ມູນ).
      • ຖ້າຄ່າຖືກໃສ່ 1 or TRUE (ຄວາມຈິງ), ຫຼັງຈາກນັ້ນ, ນີ້ຫມາຍຄວາມວ່າທ່ານອະນຸຍາດໃຫ້ຄົ້ນຫາບໍ່ແມ່ນແນ່ນອນ, ແຕ່ ການ​ແຂ່ງ​ຂັນ​ໂດຍ​ປະ​ມານ​, ie ໃນກໍລະນີຂອງ "ຫມາກພ້າວ", ຫນ້າທີ່ຈະພະຍາຍາມຊອກຫາຜະລິດຕະພັນທີ່ມີຊື່ທີ່ໃກ້ຄຽງກັບ "ຫມາກພ້າວ" ແລະສົ່ງຄືນລາຄາສໍາລັບຊື່ນີ້. ໃນກໍລະນີຫຼາຍທີ່ສຸດ, ການທົດແທນໂດຍປະມານດັ່ງກ່າວສາມາດຫຼອກລວງຜູ້ໃຊ້ໂດຍການທົດແທນມູນຄ່າຂອງຜະລິດຕະພັນທີ່ບໍ່ຖືກຕ້ອງທີ່ຕົວຈິງແລ້ວ! ດັ່ງນັ້ນສໍາລັບບັນຫາທຸລະກິດທີ່ແທ້ຈິງສ່ວນໃຫຍ່, ການຄົ້ນຫາໂດຍປະມານແມ່ນດີທີ່ສຸດທີ່ຈະບໍ່ອະນຸຍາດໃຫ້. ຂໍ້ຍົກເວັ້ນແມ່ນໃນເວລາທີ່ພວກເຮົາກໍາລັງຊອກຫາຕົວເລກແລະບໍ່ແມ່ນຂໍ້ຄວາມ - ຕົວຢ່າງ, ເມື່ອຄິດໄລ່ລາຄາຜ່ອນຜັນ.

ທຸກຢ່າງ! ມັນຍັງຄົງກົດ OK ແລະຄັດລອກຟັງຊັນທີ່ປ້ອນໃສ່ໃນຖັນທັງໝົດ.

# N / A ຄວາມຜິດພາດແລະການສະກັດກັ້ນຂອງພວກເຂົາ

ຫນ້າທີ່ VPR (VLOOKUP) ຕອບຂໍ້ຜິດພາດ #N/A (#N/A) ຖ້າເປັນ:

  • ການ​ຊອກ​ຫາ​ທີ່​ແນ່​ນອນ​ເປີດ​ໃຊ້​ງານ (ການ​ໂຕ້​ຖຽງ ມຸມເບິ່ງໄລຍະຫ່າງ = 0) ແລະຊື່ທີ່ຕ້ອງການບໍ່ໄດ້ຢູ່ໃນ ຕາຕະລາງ.
  • ການຄົ້ນຫາແບບຫຍາບລວມ (ມຸມເບິ່ງໄລຍະຫ່າງ = 1), ແຕ່ວ່າ ຕາຕະລາງ, ໃນ​ທີ່​ການ​ຄົ້ນ​ຫາ​ກໍາ​ລັງ​ເກີດ​ຂຶ້ນ​ແມ່ນ​ບໍ່​ໄດ້​ຈັດ​ຮຽງ​ຕາມ​ລໍາ​ດັບ​ຕັ້ງ​ຂຶ້ນ​ຂອງ​ຊື່​.
  • ຮູບແບບຂອງເຊລທີ່ຄ່າທີ່ຕ້ອງການຂອງຊື່ມາຈາກ (ຕົວຢ່າງ: B3 ໃນກໍລະນີຂອງພວກເຮົາ) ແລະຮູບແບບຂອງຈຸລັງຂອງຖັນທໍາອິດ (F3: F19) ຂອງຕາຕະລາງແມ່ນແຕກຕ່າງກັນ (ຕົວຢ່າງ, ຕົວເລກແລະຂໍ້ຄວາມ. ). ກໍລະນີນີ້ແມ່ນປົກກະຕິໂດຍສະເພາະເມື່ອໃຊ້ລະຫັດຕົວເລກ (ເລກບັນຊີ, ຕົວລະບຸ, ວັນທີ, ແລະອື່ນໆ) ແທນທີ່ຈະເປັນຊື່ຂໍ້ຄວາມ. ໃນກໍລະນີນີ້, ທ່ານສາມາດນໍາໃຊ້ຫນ້າທີ່ Ч и ຂໍ້​ຄວາມ ເພື່ອປ່ຽນຮູບແບບຂໍ້ມູນ. ມັນຈະມີລັກສະນະຄ້າຍຄືນີ້:

    =VLOOKUP(TEXT(B3),ລາຄາ,0)

    ທ່ານສາມາດອ່ານເພີ່ມເຕີມກ່ຽວກັບເລື່ອງນີ້ໄດ້ທີ່ນີ້.

  • ຟັງຊັນບໍ່ສາມາດຊອກຫາຄ່າທີ່ຕ້ອງການໄດ້ເນື່ອງຈາກລະຫັດມີຍະຫວ່າງ ຫຼືຕົວອັກສອນທີ່ບໍ່ສາມາດພິມໄດ້ທີ່ເບິ່ງເຫັນໄດ້ (ຕົວແບ່ງແຖວ, ແລະອື່ນໆ). ໃນກໍລະນີນີ້, ທ່ານສາມາດນໍາໃຊ້ຫນ້າທີ່ຂໍ້ຄວາມ TRIM (TRIM) и PRINT(ສະອາດ) ເພື່ອເອົາພວກມັນອອກ:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),ລາຄາ,0)

    =VLOOKUP(TRIM(CLEAN(B3));ລາຄາ;0)

ເພື່ອສະກັດກັ້ນຂໍ້ຄວາມສະແດງຂໍ້ຜິດພາດ # N / A (#N/A) ໃນກໍລະນີທີ່ຟັງຊັນບໍ່ສາມາດຊອກຫາກົງກັນທີ່ແນ່ນອນ, ທ່ານສາມາດນໍາໃຊ້ຟັງຊັນ IFERROR (IFERROR)… ດັ່ງ​ນັ້ນ, ສໍາ​ລັບ​ຕົວ​ຢ່າງ, ການ​ກໍ່​ສ້າງ​ນີ້ intercepts ຄວາມ​ຜິດ​ພາດ​ທີ່​ສ້າງ​ໂດຍ VLOOKUP ແລະ​ທົດ​ແທນ​ໃຫ້​ພວກ​ເຂົາ​ມີ​ສູນ:

= IFERROR (VLOOKUP (B3, ລາຄາ, 2, 0), 0)

= IFERROR (VLOOKUP (B3; ລາຄາ; 2; 0); 0)

PS

ຖ້າທ່ານຕ້ອງການສະກັດບໍ່ແມ່ນຄ່າຫນຶ່ງ, ແຕ່ຊຸດທັງຫມົດໃນເວລາດຽວກັນ (ຖ້າມີຫຼາຍອັນທີ່ແຕກຕ່າງກັນ), ຫຼັງຈາກນັ້ນທ່ານຈະຕ້ອງ shamanize ດ້ວຍສູດອາເລ. ຫຼືໃຊ້ຄຸນສົມບັດ XLOOKUP ໃໝ່ຈາກ Office 365.

 

  • ສະບັບປັບປຸງຂອງຟັງຊັນ VLOOKUP (VLOOKUP 2).
  • ການຄິດໄລ່ໄວຂອງຂັ້ນຕອນ (ໄລຍະ) ສ່ວນຫຼຸດໂດຍໃຊ້ຟັງຊັນ VLOOKUP.
  • ວິທີການສ້າງ "VLOOKUP ຊ້າຍ" ໂດຍໃຊ້ຟັງຊັນ INDEX ແລະ MATCH
  • ວິທີການໃຊ້ຟັງຊັນ VLOOKUP ເພື່ອຕື່ມຂໍ້ມູນໃສ່ແບບຟອມທີ່ມີຂໍ້ມູນຈາກບັນຊີລາຍຊື່
  • ວິທີການດຶງອອກບໍ່ແມ່ນຄັ້ງທໍາອິດ, ແຕ່ຄ່າທັງຫມົດຈາກຕາຕະລາງໃນເວລາດຽວ
  • ຟັງຊັນ VLOOKUP2 ແລະ VLOOKUP3 ຈາກ PLEX add-on

 

ອອກຈາກ Reply ເປັນ