ການກັ່ນຕອງຖັນແນວນອນໃນ Excel

ຖ້າທ່ານບໍ່ແມ່ນຜູ້ໃຊ້ຈົວ, ທ່ານຈະຕ້ອງໄດ້ສັງເກດເຫັນແລ້ວວ່າ 99% ຂອງທຸກສິ່ງທຸກຢ່າງໃນ Excel ຖືກອອກແບບມາເພື່ອເຮັດວຽກກັບຕາຕະລາງຕັ້ງ, ບ່ອນທີ່ພາລາມິເຕີຫຼືຄຸນລັກສະນະ (ພາກສະຫນາມ) ໄປຜ່ານຖັນ, ແລະຂໍ້ມູນກ່ຽວກັບວັດຖຸຫຼືເຫດການຕັ້ງຢູ່. ໃນ​ສາຍ​. ຕາຕະລາງ pivot, ຈໍານວນຍ່ອຍ, ສູດການຄັດລອກດ້ວຍການຄລິກສອງຄັ້ງ - ທຸກຢ່າງແມ່ນຖືກປັບແຕ່ງສະເພາະສໍາລັບຮູບແບບຂໍ້ມູນນີ້.

ຢ່າງໃດກໍຕາມ, ບໍ່ມີກົດລະບຽບທີ່ບໍ່ມີຂໍ້ຍົກເວັ້ນແລະມີຄວາມຖີ່ປົກກະຕິ, ຂ້ອຍຖືກຖາມວ່າຂ້ອຍຈະເຮັດແນວໃດຖ້າຕາຕະລາງທີ່ມີທິດທາງ semantic ຕາມລວງນອນ, ຫຼືຕາຕະລາງທີ່ແຖວແລະຖັນມີນ້ໍາຫນັກດຽວກັນໃນຄວາມຫມາຍ, ເຂົ້າມາໃນການເຮັດວຽກ:

ແລະຖ້າ Excel ຍັງຮູ້ວິທີການຈັດຮຽງຕາມແນວນອນ (ດ້ວຍຄໍາສັ່ງ ຂໍ້​ມູນ – ຄັດ​ເລືອກ – ທາງ​ເລືອກ – ຖັນ​ຄັດ​ເລືອກ​), ຫຼັງຈາກນັ້ນ, ສະຖານະການທີ່ມີການກັ່ນຕອງແມ່ນຮ້າຍແຮງກວ່າເກົ່າ - ມີພຽງແຕ່ບໍ່ມີເຄື່ອງມືໃນຕົວສໍາລັບການກັ່ນຕອງຖັນ, ບໍ່ແມ່ນແຖວໃນ Excel. ສະນັ້ນ, ຖ້າເຈົ້າປະເຊີນໜ້າກັບວຽກງານດັ່ງກ່າວ, ເຈົ້າຈະຕ້ອງມາແກ້ໄຂຄວາມຊັບຊ້ອນໃນລະດັບຕ່າງໆ.

ວິທີການ 1. ຟັງຊັນ FILTER ໃຫມ່

ຖ້າທ່ານຢູ່ໃນ Excel 2021 ຮຸ່ນໃຫມ່ຫຼືການສະຫມັກ Excel 365, ທ່ານສາມາດໃຊ້ປະໂຫຍດຈາກຄຸນສົມບັດທີ່ແນະນໍາໃຫມ່. FILTER (ການກັ່ນຕອງ), ເຊິ່ງສາມາດກັ່ນຕອງຂໍ້ມູນແຫຼ່ງບໍ່ພຽງແຕ່ໂດຍແຖວ, ແຕ່ຍັງໂດຍຖັນ. ເພື່ອເຮັດວຽກ, ຟັງຊັນນີ້ຕ້ອງການແຖວແຖວແນວນອນ array-dimensional, ເຊິ່ງແຕ່ລະຄ່າ (TRUE ຫຼື FALSE) ຈະກຳນົດວ່າພວກເຮົາສະແດງ ຫຼື ກົງກັນຂ້າມ, ເຊື່ອງຖັນຖັດໄປໃນຕາຕະລາງ.

ໃຫ້ພວກເຮົາເພີ່ມແຖວຕໍ່ໄປນີ້ຂ້າງເທິງຕາຕະລາງຂອງພວກເຮົາແລະຂຽນສະຖານະພາບຂອງແຕ່ລະຄໍລໍາໃນມັນ:

• ໃຫ້ເວົ້າວ່າພວກເຮົາສະເຫມີຕ້ອງການສະແດງຄໍລໍາທໍາອິດແລະສຸດທ້າຍ (ສ່ວນຫົວແລະຈໍານວນທັງຫມົດ), ດັ່ງນັ້ນສໍາລັບພວກເຂົາຢູ່ໃນຈຸລັງທໍາອິດແລະສຸດທ້າຍຂອງ array ພວກເຮົາກໍານົດຄ່າ = TRUE.
• ສໍາລັບຖັນທີ່ຍັງເຫຼືອ, ເນື້ອໃນຂອງເຊລທີ່ສອດຄ້ອງກັນຈະເປັນສູດທີ່ກວດສອບເງື່ອນໄຂທີ່ພວກເຮົາຕ້ອງການໂດຍໃຊ້ຟັງຊັນ. И (ແລະ) or OR (OR). ສໍາລັບຕົວຢ່າງ, ຈໍານວນທັງຫມົດແມ່ນຢູ່ໃນລະຫວ່າງ 300 ຫາ 500.

ຫຼັງຈາກນັ້ນ, ມັນຍັງມີພຽງແຕ່ໃຊ້ຟັງຊັນ FILTER ເພື່ອເລືອກຖັນຂ້າງເທິງທີ່ອາເຣຕົວຊ່ວຍຂອງພວກເຮົາມີຄ່າ TRUE:

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

ວິທີການ 2. ຕາຕະລາງ Pivot ແທນທີ່ຈະເປັນຕາຕະລາງປົກກະຕິ

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

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

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

ພວກ​ນີ້​ແມ່ນ:

1. ໃຫ້ພວກເຮົາປ່ຽນຕາຕະລາງເປັນ "smart" ຄໍາສັ່ງແບບເຄື່ອນໄຫວ ຫນ້າທໍາອິດ – ຈັດຮູບແບບເປັນຕາຕະລາງ (ຫນ້າທໍາອິດ — Format as ຕາ​ຕະ​ລາງ​).
2. ກຳລັງໂຫລດເຂົ້າໄປໃນ Power Query ດ້ວຍຄຳສັ່ງ ຂໍ້ມູນ – ຈາກຕາຕະລາງ/ໄລຍະ (ຂໍ້ມູນ – ຈາກຕາຕະລາງ/ໄລຍະ).
3. ພວກເຮົາກັ່ນຕອງເສັ້ນດ້ວຍຈໍານວນທັງຫມົດ (ບົດສະຫຼຸບຈະມີຈໍານວນທັງຫມົດຂອງຕົນເອງ).
4. ຄລິກຂວາໃສ່ຫົວຂໍ້ຖັນທຳອິດ ແລະເລືອກ ຍົກເລີກການຫຍໍ້ໜ້າຖັນອື່ນ (ຍົກເລີກການຫັນປ່ຽນຖັນອື່ນໆ). ຖັນທັງ ໝົດ ທີ່ບໍ່ໄດ້ເລືອກຈະຖືກປ່ຽນເປັນສອງ - ຊື່ຂອງພະນັກງານແລະມູນຄ່າຂອງຕົວຊີ້ວັດຂອງລາວ.
5. ການກັ່ນຕອງຖັນກັບຈໍານວນທັງຫມົດທີ່ເຂົ້າໄປໃນຖັນ ຄຸນລັກສະນະ.
6. ພວກເຮົາສ້າງຕາຕະລາງ pivot ອີງຕາມຕາຕະລາງແປ (ປົກກະຕິ) ທີ່ມີຄໍາສັ່ງ ຫນ້າທໍາອິດ - ປິດແລະໂຫຼດ - ປິດແລະໂຫຼດໃນ ... (ໜ້າຫຼັກ — ປິດ & ໂຫຼດ — ປິດ & ໂຫຼດໃສ່…).

ຕອນນີ້ທ່ານສາມາດໃຊ້ຄວາມສາມາດໃນການກັ່ນຕອງຖັນທີ່ມີຢູ່ໃນຕາຕະລາງ pivot - ເຄື່ອງໝາຍຖືກຕາມປົກກະຕິຢູ່ທາງໜ້າຂອງຊື່ ແລະລາຍການຕ່າງໆ. ການກັ່ນຕອງລາຍເຊັນ (ຕົວກັ່ນຕອງປ້າຍກຳກັບ) or ການກັ່ນຕອງຕາມມູນຄ່າ (ຕົວກອງມູນຄ່າ):

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

ວິທີການ 3. Macro ໃນ VBA

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

ສົມມຸດວ່າພວກເຮົາຕ້ອງການການກັ່ນຕອງຖັນທີ່ຊື່ຂອງຜູ້ຈັດການໃນຫົວຕາຕະລາງເຮັດໃຫ້ຫນ້າກາກທີ່ລະບຸໄວ້ໃນຫ້ອງສີເຫຼືອງ A4, ຕົວຢ່າງ, ເລີ່ມຕົ້ນດ້ວຍຕົວອັກສອນ "A" (ຫມາຍຄວາມວ່າ, ເອົາ "Anna" ແລະ "Arthur. ” ດັ່ງ​ນັ້ນ​)​. 

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

ຫຼັງຈາກນັ້ນ, ໃຫ້ເພີ່ມ macro ງ່າຍດາຍ. ຄລິກຂວາໃສ່ແຖບແຜ່ນແລະເລືອກຄໍາສັ່ງ ແຫຼ່ງຂໍ້ມູນ (ລະຫັດແຫຼ່ງ). ຄັດລອກແລະວາງລະຫັດ VBA ຕໍ່ໄປນີ້ໃສ່ປ່ອງຢ້ຽມທີ່ເປີດ:

Private Sub Worksheet_Change(ByVal Target As Range) ຖ້າ Target.Address = "$A$4" ຫຼັງຈາກນັ້ນສໍາລັບແຕ່ລະຕາລາງໃນ Range("D2:O2") ຖ້າ cell = True ຫຼັງຈາກນັ້ນ cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

ເຫດຜົນຂອງມັນແມ່ນດັ່ງນີ້:

• ໂດຍທົ່ວໄປ, ນີ້ແມ່ນຕົວຈັດການເຫດການ Worksheet_Change, ie macro ນີ້ຈະດໍາເນີນການໂດຍອັດຕະໂນມັດໃນການປ່ຽນແປງໃດໆກັບຕາລາງໃດໆໃນແຜ່ນປະຈຸບັນ.
• ການອ້າງອີງເຖິງຕາລາງທີ່ປ່ຽນແປງຈະຢູ່ໃນຕົວແປສະເໝີ ເປົ້າຫມາຍ.
• ກ່ອນອື່ນ, ພວກເຮົາກວດເບິ່ງວ່າຜູ້ໃຊ້ໄດ້ປ່ຽນເຊລທີ່ມີເງື່ອນໄຂ (A4) ຢ່າງແທ້ຈິງ - ນີ້ແມ່ນເຮັດໂດຍຜູ້ປະກອບການ. if.
• ຫຼັງຈາກນັ້ນ, ວົງຈອນເລີ່ມຕົ້ນ ສໍາລັບແຕ່ລະ… ເພື່ອ iterate ເທິງຕາລາງສີຂີ້ເຖົ່າ (D2:O2) ດ້ວຍຄ່າຕົວຊີ້ວັດ TRUE / FALSE ສໍາລັບແຕ່ລະຖັນ.
• ຖ້າຄ່າຂອງຕາລາງສີຂີ້ເຖົ່າຕໍ່ໄປແມ່ນ TRUE (ຄວາມຈິງ), ຫຼັງຈາກນັ້ນຖັນບໍ່ໄດ້ຖືກເຊື່ອງໄວ້, ຖ້າບໍ່ດັ່ງນັ້ນພວກເຮົາຈະເຊື່ອງມັນ (ຄຸນສົມບັດ. ເຊື່ອງໄວ້).

•  ຟັງຊັນອາເຣແບບໄດນາມິກຈາກ Office 365: FILTER, SORT ແລະ UNIC
• ຕາຕະລາງ Pivot ທີ່ມີສ່ວນຫົວຫຼາຍແຖວໂດຍໃຊ້ Power Query
• ມະຫາພາກແມ່ນຫຍັງ, ວິທີການສ້າງແລະນໍາໃຊ້ພວກມັນ