ຊອກຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດ

ເນື້ອໃນ

ໃນການປະຕິບັດ, ເລື້ອຍໆມີກໍລະນີທີ່ທ່ານແລະຂ້ອຍຕ້ອງຊອກຫາຄ່າທີ່ໃກ້ຄຽງທີ່ສຸດໃນຊຸດ (ຕາຕະລາງ) ທີ່ກ່ຽວຂ້ອງກັບຕົວເລກທີ່ລະບຸ. ມັນອາດຈະເປັນ, ສໍາລັບການຍົກຕົວຢ່າງ:

ການຄິດໄລ່ສ່ວນຫຼຸດຂຶ້ນກັບປະລິມານ.
ການຄິດໄລ່ຈໍານວນເງິນໂບນັດຂຶ້ນກັບການປະຕິບັດແຜນການ.
ການຄິດໄລ່ອັດຕາການຂົນສົ່ງຂຶ້ນກັບໄລຍະທາງ.
ການຄັດເລືອກຕູ້ຄອນເທນເນີທີ່ເຫມາະສົມສໍາລັບສິນຄ້າ, ແລະອື່ນໆ.

ຍິ່ງໄປກວ່ານັ້ນ, ຮອບອາດຈະຕ້ອງການທັງຂຶ້ນແລະລົງ, ຂຶ້ນກັບສະຖານະການ.

ມີຫຼາຍວິທີການ – ຈະແຈ້ງແລະບໍ່ເປັນທີ່ຈະແຈ້ງ – ເພື່ອແກ້ໄຂບັນຫາດັ່ງກ່າວ. ໃຫ້ເບິ່ງພວກເຂົາຕາມລໍາດັບ.

ເພື່ອເລີ່ມຕົ້ນ, ໃຫ້ຈິນຕະນາການຜູ້ສະຫນອງທີ່ໃຫ້ສ່ວນຫຼຸດກ່ຽວກັບການຂາຍຍົກ, ແລະອັດຕາສ່ວນຂອງສ່ວນຫຼຸດແມ່ນຂຶ້ນກັບປະລິມານຂອງສິນຄ້າທີ່ຊື້. ຕົວຢ່າງ, ເມື່ອຊື້ຫຼາຍກວ່າ 5 ຊິ້ນ, ສ່ວນຫຼຸດ 2% ແມ່ນໃຫ້, ແລະເມື່ອຊື້ຈາກ 20 ຊິ້ນ - ແລ້ວ 6%, ແລະອື່ນໆ.

ວິທີການຄິດໄລ່ອັດຕາສ່ວນສ່ວນຫຼຸດຢ່າງໄວວາແລະສວຍງາມເມື່ອເຂົ້າໄປໃນປະລິມານຂອງສິນຄ້າທີ່ຊື້?

ວິທີທີ 1: Nested IFs

ວິທີການຈາກຊຸດ "ມີຫຍັງໃຫ້ຄິດ - ເຈົ້າຕ້ອງເຕັ້ນໄປຫາ!". ການນໍາໃຊ້ຫນ້າທີ່ຕິດຕັ້ງ IF (ຖ້າແມ່ນ) ເພື່ອກວດສອບຕາມລຳດັບວ່າຄ່າຕາລາງຕົກຢູ່ໃນແຕ່ລະໄລຍະ ແລະສະແດງສ່ວນຫຼຸດສຳລັບຊ່ວງທີ່ສອດຄ້ອງກັນ. ແຕ່ສູດໃນກໍລະນີນີ້ສາມາດກາຍເປັນທີ່ສັບສົນຫຼາຍ:

ຊອກຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດ

ຂ້າພະເຈົ້າຄິດວ່າມັນຈະແຈ້ງວ່າການແກ້ບັນຫາດັ່ງກ່າວເປັນ " doll monster " ຫຼືພະຍາຍາມເພີ່ມສອງສາມເງື່ອນໄຂໃຫມ່ກັບມັນຫຼັງຈາກເວລາໃດຫນຶ່ງແມ່ນມ່ວນ.

ນອກຈາກນັ້ນ, Microsoft Excel ມີຂອບເຂດຈໍາກັດສໍາລັບຟັງຊັນ IF - 7 ເທື່ອໃນຮຸ່ນເກົ່າແລະ 64 ເທື່ອໃນຮຸ່ນໃຫມ່. ຈະເປັນແນວໃດຖ້າຫາກວ່າທ່ານຕ້ອງການເພີ່ມເຕີມ?

ວິທີການ 2. VLOOKUP ດ້ວຍການເບິ່ງໄລຍະຫ່າງ

ວິທີການນີ້ແມ່ນມີຄວາມຫນາແຫນ້ນຫຼາຍ. ເພື່ອຄິດໄລ່ອັດຕາສ່ວນສ່ວນຫຼຸດ, ໃຫ້ໃຊ້ຟັງຊັນ legendary VPR (VLOOKUP) ໃນຮູບແບບການຊອກຫາໂດຍປະມານ:

ຊອກຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດ

ບ່ອນທີ່

B4 - ມູນຄ່າຂອງປະລິມານຂອງສິນຄ້າໃນທຸລະກໍາທໍາອິດທີ່ພວກເຮົາກໍາລັງຊອກຫາສ່ວນຫຼຸດ
$G$4:$H$8 - ການເຊື່ອມຕໍ່ກັບຕາຕະລາງສ່ວນຫຼຸດ - ໂດຍບໍ່ມີ "header" ແລະມີທີ່ຢູ່ທີ່ຖືກແກ້ໄຂດ້ວຍເຄື່ອງຫມາຍ $.
2 — ຕົວເລກຕາມລຳດັບຂອງຖັນໃນຕາຕະລາງສ່ວນຫຼຸດທີ່ພວກເຮົາຕ້ອງການເອົາມູນຄ່າສ່ວນຫຼຸດ
TRUE - ນີ້ແມ່ນບ່ອນທີ່ "ຫມາ" ຖືກຝັງ. ຖ້າເປັນການໂຕ້ຖຽງຫນ້າທີ່ສຸດທ້າຍ VPR ລະບຸ ນອນ (ຜິດ) ຫຼື 0, ຫຼັງຈາກນັ້ນຟັງຊັນຈະຊອກຫາ ການແຂ່ງຂັນທີ່ເຄັ່ງຄັດ ໃນຖັນປະລິມານ (ແລະໃນກໍລະນີຂອງພວກເຮົາມັນຈະໃຫ້ຄວາມຜິດພາດ #N/A, ເນື່ອງຈາກວ່າບໍ່ມີຄ່າ 49 ໃນຕາຕະລາງສ່ວນຫຼຸດ). ແຕ່ຖ້າແທນ ນອນ ຂຽນ TRUE (ຄວາມຈິງ) ຫຼື 1, ຫຼັງຈາກນັ້ນ, ຟັງຊັນຈະບໍ່ຊອກຫາທີ່ແນ່ນອນ, ແຕ່ ນ້ອຍທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ ມູນຄ່າແລະຈະໃຫ້ພວກເຮົາສ່ວນຫຼຸດທີ່ພວກເຮົາຕ້ອງການ.

ຂໍ້ເສຍຂອງວິທີການນີ້ແມ່ນຄວາມຕ້ອງການທີ່ຈະຈັດລຽງຕາຕະລາງສ່ວນຫຼຸດໃນລໍາດັບຕັ້ງຊັນຂຶ້ນໂດຍຖັນທໍາອິດ. ຖ້າບໍ່ມີການຮຽງລໍາດັບດັ່ງກ່າວ (ຫຼືມັນຖືກເຮັດໃນລໍາດັບປີ້ນກັບກັນ), ສູດຂອງພວກເຮົາຈະບໍ່ເຮັດວຽກ:

ຊອກຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດ

ຕາມນັ້ນແລ້ວ, ວິທີນີ້ພຽງແຕ່ສາມາດໃຊ້ໄດ້ເພື່ອຊອກຫາຄ່ານ້ອຍທີ່ໃກ້ທີ່ສຸດ. ຖ້າທ່ານຕ້ອງການຊອກຫາທີ່ໃຫຍ່ທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ, ຫຼັງຈາກນັ້ນທ່ານຕ້ອງໃຊ້ວິທີການທີ່ແຕກຕ່າງກັນ.

ວິທີການ 3. ຊອກຫາທີ່ໃຫຍ່ທີ່ສຸດທີ່ໃກ້ທີ່ສຸດໂດຍໃຊ້ຟັງຊັນ INDEX ແລະ MATCH

ຕອນນີ້ໃຫ້ເບິ່ງບັນຫາຂອງພວກເຮົາຈາກອີກດ້ານຫນຶ່ງ. ສົມມຸດວ່າພວກເຮົາຂາຍຫຼາຍແບບຂອງປັ໊ມອຸດສາຫະກໍາທີ່ມີຄວາມສາມາດຕ່າງໆ. ຕາຕະລາງການຂາຍຢູ່ເບື້ອງຊ້າຍສະແດງໃຫ້ເຫັນພະລັງງານທີ່ລູກຄ້າຕ້ອງການ. ພວກເຮົາຈໍາເປັນຕ້ອງເລືອກປັ໊ມຂອງພະລັງງານສູງສຸດທີ່ໃກ້ທີ່ສຸດຫຼືເທົ່າທຽມກັນ, ແຕ່ບໍ່ຫນ້ອຍກວ່າສິ່ງທີ່ໂຄງການຕ້ອງການ.

ຟັງຊັນ VLOOKUP ຈະບໍ່ຊ່ວຍຢູ່ທີ່ນີ້, ດັ່ງນັ້ນທ່ານຈະຕ້ອງໃຊ້ການປຽບທຽບຂອງມັນ - ຟັງຊັນ INDEX ຫຼາຍ. (INDEX) ແລະເປີດເຜີຍຫຼາຍຂຶ້ນ (ການແຂ່ງຂັນ):

ຊອກຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດ

ທີ່ນີ້, ຟັງຊັນ MATCH ທີ່ມີການໂຕ້ຖຽງສຸດທ້າຍ -1 ເຮັດວຽກໃນຮູບແບບຂອງການຊອກຫາຄ່າທີ່ໃຫຍ່ທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ, ແລະຟັງຊັນ INDEX ຫຼັງຈາກນັ້ນສະກັດຊື່ແບບຈໍາລອງທີ່ພວກເຮົາຕ້ອງການຈາກຖັນທີ່ຢູ່ໃກ້ຄຽງ.

ວິທີການ 4. ຟັງຊັນໃຫມ່ VIEW (XLOOKUP)

ຖ້າທ່ານມີເວີຊັນຂອງ Office 365 ທີ່ມີການຕິດຕັ້ງການປັບປຸງທັງຫມົດ, ຫຼັງຈາກນັ້ນແທນທີ່ຈະເປັນ VLOOKUP (VLOOKUP) ທ່ານສາມາດນໍາໃຊ້ analogue ຂອງມັນ - ຟັງຊັນ VIEW (XLOOKUP), ເຊິ່ງຂ້ອຍໄດ້ວິເຄາະຢ່າງລະອຽດແລ້ວ:

ຊອກຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດ

ທີ່ນີ້:

B4 - ມູນຄ່າເບື້ອງຕົ້ນຂອງປະລິມານຂອງຜະລິດຕະພັນທີ່ພວກເຮົາກໍາລັງຊອກຫາສ່ວນຫຼຸດ
$G$4:$G$8 - ຊ່ວງທີ່ພວກເຮົາກໍາລັງຊອກຫາການແຂ່ງຂັນ
$H$4:$H$8 - ຊ່ວງຂອງຜົນໄດ້ຮັບທີ່ທ່ານຕ້ອງການສົ່ງຄືນສ່ວນຫຼຸດ
ການໂຕ້ຖຽງທີ່ສີ່ (-1) ລວມມີການຄົ້ນຫາຕົວເລກທີ່ໃກ້ທີ່ສຸດທີ່ພວກເຮົາຕ້ອງການແທນທີ່ຈະກົງກັນທີ່ແນ່ນອນ.

ຂໍ້ດີຂອງວິທີການນີ້ແມ່ນບໍ່ຈໍາເປັນຕ້ອງຈັດຮຽງຕາຕະລາງສ່ວນຫຼຸດແລະຄວາມສາມາດໃນການຄົ້ນຫາ, ຖ້າຈໍາເປັນ, ບໍ່ພຽງແຕ່ຢູ່ໃກ້ທີ່ສຸດ, ແຕ່ຍັງມີມູນຄ່າທີ່ໃຫຍ່ທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ. ການໂຕ້ຖຽງສຸດທ້າຍໃນກໍລະນີນີ້ຈະເປັນ 1.

ແຕ່ຫນ້າເສຍດາຍ, ບໍ່ແມ່ນທຸກຄົນທີ່ມີຄຸນສົມບັດນີ້ - ພຽງແຕ່ເຈົ້າຂອງ Office 365 ທີ່ມີຄວາມສຸກເທົ່ານັ້ນ.

ວິທີການ 5. Power Query

ຖ້າທ່ານຍັງບໍ່ຄຸ້ນເຄີຍກັບ Power Query add-in ທີ່ມີປະສິດທິພາບແລະບໍ່ເສຍຄ່າສໍາລັບ Excel, ຫຼັງຈາກນັ້ນທ່ານຢູ່ທີ່ນີ້. ຖ້າທ່ານຄຸ້ນເຄີຍແລ້ວ, ລອງໃຊ້ມັນເພື່ອແກ້ໄຂບັນຫາຂອງພວກເຮົາ.

ມາເຮັດວຽກກະກຽມກ່ອນ:

ມາປ່ຽນຕາຕະລາງແຫຼ່ງຂອງພວກເຮົາເປັນແບບເຄື່ອນໄຫວ (ສະຫຼາດ) ໂດຍໃຊ້ທາງລັດແປ້ນພິມ Ctrl+T ຫຼືທີມງານ ຫນ້າທໍາອິດ – ຈັດຮູບແບບເປັນຕາຕະລາງ (ຫນ້າທໍາອິດ — Format as ຕາຕະລາງ).
ເພື່ອຄວາມຊັດເຈນ, ໃຫ້ພວກເຮົາຕັ້ງຊື່. Sales и ລາຄາພິເສດ ແຖບ ຜູ້ກໍ່ສ້າງ (ອອກແບບ).
ໂຫຼດແຕ່ລະຕາຕະລາງເປັນ Power Query ໂດຍໃຊ້ປຸ່ມ ຈາກຕາຕະລາງ/ໄລຍະ ແຖບ ຂໍ້ມູນ (ຂໍ້ມູນ - ຈາກຕາຕະລາງ/ໄລຍະ). ໃນລຸ້ນຫຼ້າສຸດຂອງ Excel, ປຸ່ມນີ້ໄດ້ຖືກປ່ຽນຊື່ເປັນ ມີໃບ (ຈາກແຜ່ນງານ).
ຖ້າຕາຕະລາງມີຊື່ຖັນທີ່ແຕກຕ່າງກັນກັບປະລິມານ, ເຊັ່ນດຽວກັບຕົວຢ່າງຂອງພວກເຮົາ ("ປະລິມານຂອງສິນຄ້າ" ແລະ "ປະລິມານຈາກ ... "), ຫຼັງຈາກນັ້ນພວກເຂົາຕ້ອງຖືກປ່ຽນຊື່ໃນ Power Query ແລະຕັ້ງຊື່ດຽວກັນ.
ຫຼັງຈາກນັ້ນ, ທ່ານສາມາດກັບຄືນໄປຫາ Excel ໂດຍການເລືອກຄໍາສັ່ງຢູ່ໃນປ່ອງຢ້ຽມ Power Query editor ຫນ້າທໍາອິດ - ປິດແລະໂຫຼດ - ປິດແລະໂຫຼດໃນ ... (ຫນ້າທໍາອິດ — Close&Load — Close&Load to…) ແລະຫຼັງຈາກນັ້ນທາງເລືອກ ພຽງແຕ່ສ້າງການເຊື່ອມຕໍ່ (ພຽງແຕ່ສ້າງການເຊື່ອມຕໍ່).
ຫຼັງຈາກນັ້ນ, ທີ່ຫນ້າສົນໃຈຫຼາຍທີ່ສຸດເລີ່ມຕົ້ນ. ຖ້າທ່ານມີປະສົບການໃນ Power Query, ຂ້າພະເຈົ້າສົມມຸດວ່າແນວຄວາມຄິດຕໍ່ໄປຄວນຈະຢູ່ໃນທິດທາງຂອງການລວມຕາຕະລາງທັງສອງນີ້ດ້ວຍການສອບຖາມເຂົ້າຮ່ວມ (merge) a la VLOOKUP, ເຊັ່ນດຽວກັນກັບກໍລະນີໃນວິທີການທີ່ຜ່ານມາ. ໃນຄວາມເປັນຈິງ, ພວກເຮົາຈະຕ້ອງປະສົມປະສານໃນຮູບແບບການເພີ່ມ, ເຊິ່ງບໍ່ຊັດເຈນຢູ່ glance ທໍາອິດ. ເລືອກໃນແຖບ Excel ຂໍ້ມູນ – ໄດ້ຮັບຂໍ້ມູນ – ສົມທົບການຮ້ອງຂໍ – ເພີ່ມ (ຂໍ້ມູນ — ໄດ້ຮັບຂໍ້ມູນ — ລວມການສອບຖາມ — ຕື່ມໃສ່) ແລະຫຼັງຈາກນັ້ນຕາຕະລາງຂອງພວກເຮົາ Sales и ລາຄາພິເສດ ຢູ່ໃນປ່ອງຢ້ຽມທີ່ປາກົດ:
ຫຼັງຈາກທີ່ຄລິກໃສ່ OK ຕາ ຕະ ລາງ ຂອງ ພວກ ເຮົາ ຈະ ໄດ້ ຮັບ ການ glued ເປັນ ດຽວ ທັງ ຫມົດ - ພາຍ ໃຕ້ ກັນ ແລະ ກັນ. ກະລຸນາສັງເກດວ່າຄໍລໍາທີ່ມີຈໍານວນສິນຄ້າໃນຕາຕະລາງເຫຼົ່ານີ້ຫຼຸດລົງພາຍໃຕ້ກັນແລະກັນ, ເພາະວ່າ. ພວກເຂົາເຈົ້າມີຊື່ດຽວກັນ:
ຖ້າລໍາດັບຕົ້ນສະບັບຂອງແຖວໃນຕາຕະລາງການຂາຍແມ່ນສໍາຄັນສໍາລັບທ່ານ, ດັ່ງນັ້ນຫຼັງຈາກການຫັນປ່ຽນຕໍ່ມາທັງຫມົດ, ຫຼັງຈາກນັ້ນທ່ານສາມາດຟື້ນຟູມັນ, ເພີ່ມຖັນຕົວເລກໃສ່ຕາຕະລາງຂອງພວກເຮົາໂດຍໃຊ້ຄໍາສັ່ງ. ການເພີ່ມຖັນ – ຖັນດັດຊະນີ (ເພີ່ມຖັນ — ຖັນດັດຊະນີ). ຖ້າລໍາດັບຂອງເສັ້ນບໍ່ສໍາຄັນກັບທ່ານ, ຫຼັງຈາກນັ້ນທ່ານສາມາດຂ້າມຂັ້ນຕອນນີ້.
ໃນປັດຈຸບັນ, ການນໍາໃຊ້ບັນຊີລາຍຊື່ເລື່ອນລົງໃນສ່ວນຫົວຂອງຕາຕະລາງ, ຈັດຮຽງຕາມຖັນ ເລືອກຈຳນວນ ຕັ້ງຊັນຂຶ້ນ:
ແລະເຄັດລັບຕົ້ນຕໍ: ຄລິກຂວາໃສ່ຫົວຖັນ ສ່ວນລົດ ເລືອກທີມ ຕື່ມ - ລົງ (ຕື່ມ - ລົງ). ຕາລາງຫວ່າງເປົ່າກັບ null ຕື່ມຂໍ້ມູນໃສ່ອັດຕະໂນມັດດ້ວຍຄ່າສ່ວນຫຼຸດທີ່ຜ່ານມາ:
ມັນຍັງຄົງເປັນການຟື້ນຟູລໍາດັບຕົ້ນສະບັບຂອງແຖວໂດຍການຈັດລຽງຕາມຖັນ ດັດຊະນີ (ທ່ານສາມາດລຶບມັນໄດ້ຢ່າງປອດໄພໃນພາຍຫຼັງ) ແລະກໍາຈັດສາຍທີ່ບໍ່ຈໍາເປັນດ້ວຍຕົວກອງ null ໂດຍຖັນ ລະຫັດທຸລະກໍາ: