ເນື້ອໃນ
ຂ້າພະເຈົ້າໄດ້ຂຽນແລ້ວກ່ຽວກັບວິທີທີ່ທ່ານສາມາດກາວຂໍ້ຄວາມຈາກຫຼາຍໆຈຸລັງເຂົ້າໄປໃນຫນຶ່ງແລະ, ກົງກັນຂ້າມ, ແຍກສາຍຂໍ້ຄວາມຍາວເຂົ້າໄປໃນອົງປະກອບ. ຕອນນີ້ໃຫ້ພິຈາລະນາວຽກງານທີ່ໃກ້ຊິດ, ແຕ່ສະລັບສັບຊ້ອນເລັກນ້ອຍ - ວິທີການກາວຂໍ້ຄວາມຈາກຫຼາຍໆຈຸລັງເມື່ອມີເງື່ອນໄຂທີ່ລະບຸໄວ້.
ໃຫ້ເວົ້າວ່າພວກເຮົາມີຖານຂໍ້ມູນຂອງລູກຄ້າ, ບ່ອນທີ່ຫນຶ່ງຊື່ບໍລິສັດສາມາດກົງກັບອີເມວທີ່ແຕກຕ່າງກັນຫຼາຍຂອງພະນັກງານຂອງຕົນ. ວຽກງານຂອງພວກເຮົາແມ່ນເພື່ອເກັບກໍາທີ່ຢູ່ທັງຫມົດໂດຍຊື່ບໍລິສັດແລະ concatenate ໃຫ້ເຂົາເຈົ້າ (ແຍກດ້ວຍເຄື່ອງໝາຍຈຸດຫຼື semicolons) ເພື່ອເຮັດໃຫ້, ສໍາລັບການຍົກຕົວຢ່າງ, ບັນຊີລາຍຊື່ທາງໄປສະນີສໍາລັບລູກຄ້າ, ie ໄດ້ຮັບຜົນຜະລິດບາງສິ່ງບາງຢ່າງເຊັ່ນ:.
ໃນຄໍາສັບຕ່າງໆອື່ນໆ, ພວກເຮົາຕ້ອງການເຄື່ອງມືທີ່ຈະກາວ (ເຊື່ອມຕໍ່) ຂໍ້ຄວາມຕາມເງື່ອນໄຂ - ການປຽບທຽບຂອງຫນ້າທີ່ SUMMESLI (SUMIF), ແຕ່ສໍາລັບຂໍ້ຄວາມ.
ວິທີການ 0. ສູດ
ບໍ່ elegant ຫຼາຍ, ແຕ່ວິທີທີ່ງ່າຍທີ່ສຸດ. ທ່ານສາມາດຂຽນສູດງ່າຍໆທີ່ຈະກວດເບິ່ງວ່າບໍລິສັດໃນແຖວຕໍ່ໄປແຕກຕ່າງຈາກອັນທີ່ຜ່ານມາ. ຖ້າມັນບໍ່ແຕກຕ່າງກັນ, ຫຼັງຈາກນັ້ນກາວທີ່ຢູ່ຕໍ່ໄປທີ່ແຍກດ້ວຍເຄື່ອງໝາຍຈຸດ. ຖ້າມັນແຕກຕ່າງກັນ, ຫຼັງຈາກນັ້ນພວກເຮົາ "ປັບ" ການສະສົມ, ເລີ່ມຕົ້ນອີກເທື່ອຫນຶ່ງ:
ຂໍ້ເສຍຂອງວິທີການນີ້ແມ່ນເຫັນໄດ້ຊັດເຈນ: ຈາກຈຸລັງທັງຫມົດຂອງຖັນເພີ່ມເຕີມທີ່ໄດ້ຮັບ, ພວກເຮົາຕ້ອງການພຽງແຕ່ສຸດທ້າຍສໍາລັບແຕ່ລະບໍລິສັດ (ສີເຫຼືອງ). ຖ້າບັນຊີລາຍຊື່ໃຫຍ່, ຫຼັງຈາກນັ້ນເພື່ອເລືອກພວກມັນໄວ, ທ່ານຈະຕ້ອງເພີ່ມຖັນອື່ນໂດຍໃຊ້ຟັງຊັນ DLSTR (LEN), ກວດເບິ່ງຄວາມຍາວຂອງສາຍສະສົມ:
ໃນປັດຈຸບັນທ່ານສາມາດກັ່ນຕອງອອກແລະຄັດລອກທີ່ຢູ່ທີ່ຈໍາເປັນ gluing ສໍາລັບການນໍາໃຊ້ຕໍ່ໄປ.
ວິທີການ 1. Macrofunction ຂອງ gluing ໂດຍເງື່ອນໄຂຫນຶ່ງ
ຖ້າບັນຊີລາຍຊື່ຕົ້ນສະບັບບໍ່ໄດ້ຖືກຈັດຮຽງໂດຍບໍລິສັດ, ຫຼັງຈາກນັ້ນສູດງ່າຍໆຂ້າງເທິງນີ້ບໍ່ໄດ້ເຮັດວຽກ, ແຕ່ທ່ານສາມາດໄປຫາໄດ້ງ່າຍດ້ວຍຟັງຊັນທີ່ກໍາຫນົດເອງຂະຫນາດນ້ອຍໃນ VBA. ເປີດຕົວແກ້ໄຂ Visual Basic ໂດຍການກົດແປ້ນພິມລັດ Alt + F11 ຫຼືການນໍາໃຊ້ປຸ່ມ Visual Basic ແຖບ ການພັດທະນາ (ຜູ້ພັດທະນາ). ໃນປ່ອງຢ້ຽມທີ່ເປີດ, ໃສ່ໂມດູນຫວ່າງໃຫມ່ຜ່ານເມນູ ໃສ່ - ໂມດູນ ແລະຄັດລອກຂໍ້ຄວາມຂອງຫນ້າທີ່ຂອງພວກເຮົາຢູ່ທີ່ນັ້ນ:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings ບໍ່ເທົ່າກັນ - ພວກເຮົາອອກດ້ວຍຄວາມຜິດພາດຖ້າ SearchRange.Count <> TextRange.Count ຫຼັງຈາກນັ້ນ, MergeIf = CVErr(xlErrRef) Exit Function End ຖ້າ 'ຜ່ານຈຸລັງທັງຫມົດ, ກວດເບິ່ງເງື່ອນໄຂແລະລວບລວມຂໍ້ຄວາມໃນຕົວແປ OutText ສໍາລັບ i = 1 ເພື່ອ SearchRange. Cells.Count ຖ້າ SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'ສະແດງຜົນທີ່ບໍ່ມີຕົວຂັ້ນສຸດທ້າຍ MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End ຫນ້າທີ່
ຖ້າຫາກວ່າທ່ານໃນປັດຈຸບັນກັບຄືນໄປບ່ອນ Microsoft Excel, ຫຼັງຈາກນັ້ນໃນບັນຊີລາຍການຂອງຫນ້າທີ່ (ປຸ່ມ fx ໃນແຖບສູດຫຼືແຖບ ສູດ – ໃສ່ຟັງຊັນ) ມັນຈະເປັນໄປໄດ້ທີ່ຈະຊອກຫາຫນ້າທີ່ຂອງພວກເຮົາ ລວມຖ້າ ໃນປະເພດ ຜູ້ໃຊ້ ກຳ ນົດ (ຜູ້ໃຊ້ກໍານົດ). ການໂຕ້ຖຽງກັບຟັງຊັນມີດັ່ງນີ້:
ວິທີການ 2. ປະກອບຂໍ້ຄວາມໂດຍເງື່ອນໄຂທີ່ບໍ່ແນ່ນອນ
ຖ້າພວກເຮົາປ່ຽນຕົວອັກສອນທໍາອິດໃນແຖວທີ 13 ຂອງມະຫາພາກຂອງພວກເຮົາ = ກັບຕົວປະຕິບັດການຈັບຄູ່ໂດຍປະມານ ຄື, ຫຼັງຈາກນັ້ນມັນຈະເປັນໄປໄດ້ທີ່ຈະດໍາເນີນການ gluing ໂດຍກົງກັນ inexact ຂອງຂໍ້ມູນເບື້ອງຕົ້ນກັບ criterion ການຄັດເລືອກ. ຕົວຢ່າງ, ຖ້າຊື່ບໍລິສັດສາມາດຂຽນເປັນຕົວແປທີ່ແຕກຕ່າງກັນ, ຫຼັງຈາກນັ້ນພວກເຮົາສາມາດກວດເບິ່ງແລະລວບລວມພວກມັນທັງຫມົດດ້ວຍຫນ້າທີ່ດຽວ:
ຮອງຮັບ wildcards ມາດຕະຖານ:
- asterisk (*) - ຫມາຍເຖິງຕົວເລກຂອງຕົວອັກສອນໃດໆ (ລວມທັງການບໍ່ມີຕົວຕົນ)
- ເຄື່ອງຫມາຍຄໍາຖາມ (?) - ຫຍໍ້ມາຈາກຕົວອັກສອນດຽວ
- ສັນຍາລັກປອນ (#) – ຫຍໍ້ມາຈາກຕົວເລກໃດນຶ່ງ (0-9)
ໂດຍຄ່າເລີ່ມຕົ້ນ, ຕົວປະຕິບັດການ Like ແມ່ນຕົວພິມນ້ອຍ, ເຊັ່ນເຂົ້າໃຈ, ສໍາລັບການຍົກຕົວຢ່າງ, "Orion" ແລະ "orion" ເປັນບໍລິສັດທີ່ແຕກຕ່າງກັນ. ເພື່ອບໍ່ສົນໃຈກໍລະນີ, ທ່ານສາມາດເພີ່ມເສັ້ນຢູ່ໃນຈຸດເລີ່ມຕົ້ນຂອງໂມດູນໃນຕົວແກ້ໄຂ Visual Basic ທາງເລືອກປຽບທຽບຂໍ້ຄວາມ, ເຊິ່ງຈະປ່ຽນ Like ໃຫ້ເປັນຕົວພິມນ້ອຍ.
ດ້ວຍວິທີນີ້, ທ່ານສາມາດປະກອບຫນ້າກາກທີ່ສັບສົນຫຼາຍສໍາລັບການກວດສອບເງື່ອນໄຂ, ຕົວຢ່າງ:
- ?1##??777RUS – ການຄັດເລືອກປ້າຍທະບຽນລົດທັງໝົດຂອງເຂດ 777, ເລີ່ມຕົ້ນດ້ວຍ 1.
- LLC* - ບໍລິສັດທັງໝົດທີ່ມີຊື່ເລີ່ມຕົ້ນດ້ວຍ LLC
- ##7## - ຜະລິດຕະພັນທັງໝົດທີ່ມີລະຫັດດິຈິຕອນຫ້າຕົວເລກ, ເຊິ່ງຕົວເລກທີສາມແມ່ນ 7
- ???? - ຊື່ທັງຫມົດຂອງຫ້າຕົວອັກສອນ, ແລະອື່ນໆ
ວິທີການ 3. ຟັງຊັນ Macro ສໍາລັບ gluing ຂໍ້ຄວາມພາຍໃຕ້ສອງເງື່ອນໄຂ
ໃນການເຮັດວຽກອາດຈະມີບັນຫາໃນເວລາທີ່ທ່ານຕ້ອງການເຊື່ອມໂຍງຂໍ້ຄວາມຫຼາຍກວ່າຫນຶ່ງເງື່ອນໄຂ. ຕົວຢ່າງ, ໃຫ້ຈິນຕະນາການວ່າໃນຕາຕະລາງທີ່ຜ່ານມາຂອງພວກເຮົາ, ຖັນຫນຶ່ງທີ່ມີເມືອງຖືກເພີ່ມ, ແລະການຕິດກາວຄວນປະຕິບັດບໍ່ພຽງແຕ່ສໍາລັບບໍລິສັດທີ່ໃຫ້, ແຕ່ຍັງສໍາລັບເມືອງທີ່ໃຫ້. ໃນກໍລະນີນີ້, ຫນ້າທີ່ຂອງພວກເຮົາຈະຕ້ອງມີຄວາມທັນສະໄຫມເລັກນ້ອຍໂດຍການເພີ່ມການກວດສອບຂອບເຂດອື່ນໃສ່ມັນ:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'ຕົວຂັ້ນຕົວຂັ້ນ (ສາມາດຖືກແທນທີ່ດ້ວຍ space ຫຼື ; etc.) e.) 'ຖ້າ validation and gluing ranges are not equal to each other, exit with an error If SearchRange1.Count <> TextRange.Count ຫຼື SearchRange2.Count <> TextRange.Count ຫຼັງຈາກນັ້ນ MergeIfs = CVERr(xlErrRef) Exit Function End If 'ໄປຜ່ານທຸກເຊລ, ກວດເບິ່ງເງື່ອນໄຂທັງໝົດ ແລະເກັບຂໍ້ຄວາມເຂົ້າໄປໃນຕົວແປ OutText For i = 1 To SearchRange1.Cells.Count ຖ້າ SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End ຖ້າ Next i 'ສະແດງຜົນທີ່ບໍ່ມີຕົວຂັ້ນສຸດທ້າຍ MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
ມັນຈະຖືກນໍາໃຊ້ໃນແບບດຽວກັນຢ່າງແທ້ຈິງ - ພຽງແຕ່ການໂຕ້ຖຽງໃນປັດຈຸບັນຈໍາເປັນຕ້ອງໄດ້ລະບຸເພີ່ມເຕີມ:
ວິທີການ 4. ການຈັດກຸ່ມແລະການຕິດກາວໃນ Power Query
ທ່ານສາມາດແກ້ໄຂບັນຫາໂດຍບໍ່ມີການດໍາເນີນໂຄງການໃນ VBA, ຖ້າຫາກວ່າທ່ານນໍາໃຊ້ Power Query add-in ຟຣີ. ສໍາລັບ Excel 2010-2013 ມັນສາມາດດາວໂຫລດໄດ້ທີ່ນີ້, ແລະໃນ Excel 2016 ມັນຖືກສ້າງຢູ່ໃນຄ່າເລີ່ມຕົ້ນແລ້ວ. ລໍາດັບຂອງການປະຕິບັດຈະເປັນດັ່ງຕໍ່ໄປນີ້:
Power Query ບໍ່ຮູ້ວິທີການເຮັດວຽກກັບຕາຕະລາງປົກກະຕິ, ດັ່ງນັ້ນຂັ້ນຕອນທໍາອິດແມ່ນການປ່ຽນຕາຕະລາງຂອງພວກເຮົາໃຫ້ເປັນ "smart" ຫນຶ່ງ. ເພື່ອເຮັດສິ່ງນີ້, ເລືອກມັນແລະກົດປະສົມປະສານ Ctrl+T ຫຼືເລືອກຈາກແຖບ ຫນ້າທໍາອິດ – ຈັດຮູບແບບເປັນຕາຕະລາງ (ຫນ້າທໍາອິດ — Format as ຕາຕະລາງ). ໃນແຖບທີ່ປາກົດຫຼັງຈາກນັ້ນ ຜູ້ກໍ່ສ້າງ (ອອກແບບ) ທ່ານສາມາດຕັ້ງຊື່ຕາຕະລາງ (ຂ້ອຍອອກຈາກມາດຕະຖານ ຕາລາງ 1):
ຕອນນີ້ໃຫ້ເຮົາໂຫຼດຕາຕະລາງຂອງພວກເຮົາໃສ່ Power Query add-in. ເພື່ອເຮັດສິ່ງນີ້, ຢູ່ໃນແຖບ ຂໍ້ມູນ (ຖ້າທ່ານມີ Excel 2016) ຫຼືຢູ່ໃນແຖບ Power Query (ຖ້າທ່ານມີ Excel 2010-2013) ຄລິກ ຈາກຕາຕະລາງ (ຂໍ້ມູນ - ຈາກຕາຕະລາງ):
ໃນປ່ອງຢ້ຽມຕົວແກ້ໄຂແບບສອບຖາມທີ່ເປີດ, ເລືອກຄໍລໍາໂດຍການຄລິກໃສ່ສ່ວນຫົວ ບໍລິສັດ ແລະກົດປຸ່ມຂ້າງເທິງ ກຸ່ມ (ກຸ່ມຕາມ). ໃສ່ຊື່ຂອງຖັນໃໝ່ ແລະປະເພດຂອງການດຳເນີນການໃນການຈັດກຸ່ມ – ສາຍທັງໝົດ (ແຖວທັງໝົດ):
ກົດ OK ແລະພວກເຮົາໄດ້ຮັບ mini-table ຂອງຄ່າກຸ່ມສໍາລັບແຕ່ລະບໍລິສັດ. ເນື້ອໃນຂອງຕາຕະລາງແມ່ນເຫັນໄດ້ຊັດເຈນຖ້າຫາກວ່າທ່ານຄລິກຊ້າຍໃສ່ພື້ນຖານສີຂາວຂອງຈຸລັງ (ບໍ່ແມ່ນຢູ່ໃນຂໍ້ຄວາມ!) ໃນຖັນຜົນໄດ້ຮັບ:
ຕອນນີ້ໃຫ້ເພີ່ມຫນຶ່ງຖັນຕື່ມອີກ, ບ່ອນທີ່, ການນໍາໃຊ້ຟັງຊັນ, ພວກເຮົາກາວເນື້ອໃນຂອງຖັນທີ່ຢູ່ໃນແຕ່ລະຕາຕະລາງ mini, ແຍກດ້ວຍເຄື່ອງໝາຍຈຸດ. ເພື່ອເຮັດສິ່ງນີ້, ຢູ່ໃນແຖບ ເພີ່ມຖັນ ພວກເຮົາກົດ ຖັນແບບກຳນົດເອງ (ເພີ່ມຖັນ — ຖັນແບບກຳນົດເອງ) ແລະຢູ່ໃນປ່ອງຢ້ຽມທີ່ປາກົດ, ໃສ່ຊື່ຂອງຖັນໃຫມ່ແລະສູດການເຊື່ອມໃນພາສາ M ທີ່ສ້າງຂຶ້ນໃນ Power Query:
ໃຫ້ສັງເກດວ່າຟັງຊັນ M ທັງຫມົດແມ່ນຕົວພິມນ້ອຍ (ບໍ່ຄືກັບ Excel). ຫຼັງຈາກທີ່ຄລິກໃສ່ OK ພວກເຮົາໄດ້ຮັບຄໍລໍາໃຫມ່ທີ່ມີທີ່ຢູ່ກາວ:
ມັນຍັງຄົງທີ່ຈະເອົາຄໍລໍາທີ່ບໍ່ຈໍາເປັນທີ່ມີຢູ່ແລ້ວອອກ ທີ່ຢູ່ຕາຕະລາງ (ຄລິກຂວາໃສ່ຫົວຂໍ້) ລຶບຖັນ) ແລະອັບໂຫລດຜົນໄດ້ຮັບໃສ່ແຜ່ນໂດຍຄລິກໃສ່ແຖບ ຫນ້າທໍາອິດ - ປິດແລະດາວໂຫລດ (ຫນ້າທໍາອິດ - ປິດແລະໂຫຼດ):
nuance ທີ່ສໍາຄັນ: ແຕກຕ່າງຈາກວິທີການທີ່ຜ່ານມາ (ຟັງຊັນ), ຕາຕະລາງຈາກ Power Query ບໍ່ໄດ້ຖືກປັບປຸງໂດຍອັດຕະໂນມັດ. ຖ້າໃນອະນາຄົດຈະມີການປ່ຽນແປງໃດໆໃນຂໍ້ມູນແຫຼ່ງ, ຫຼັງຈາກນັ້ນທ່ານຈະຕ້ອງຄລິກຂວາໃສ່ບ່ອນໃດກໍ່ຕາມໃນຕາຕະລາງຜົນໄດ້ຮັບແລະເລືອກຄໍາສັ່ງ. ອັບເດດ ແລະບັນທຶກ (ໂຫຼດຂໍ້ມູນຄືນໃໝ່).
- ວິທີການແບ່ງສາຍຂໍ້ຄວາມຍາວອອກເປັນພາກສ່ວນ
- ຫຼາຍວິທີທີ່ຈະກາວຂໍ້ຄວາມຈາກຈຸລັງທີ່ແຕກຕ່າງກັນເຂົ້າໄປໃນຫນຶ່ງ
- ການນໍາໃຊ້ຕົວປະຕິບັດການ Like ເພື່ອທົດສອບຂໍ້ຄວາມຕໍ່ກັບຫນ້າກາກ