Cuộc Offline Hải Phòng vừa rồi có thật nhiều thú vị, học hỏi, tìm hiểu và còn có cả những thắc mắc.
Sau đây là 1 thắc mắc "tạm thời" chưa có lời giải mong các thành viên GPE giúp đỡ:
Vấn đề: Làm sao nối các phần tử trong 1 mảng ảo thành 1 chuỗi
(Để dễ hình dung đơn giản hơn mọi người xem File đính kèm)
Tks!
Cuộc Offline Hải Phòng vừa rồi có thật nhiều thú vị, học hỏi, tìm hiểu và còn có cả những thắc mắc.
Sau đây là 1 thắc mắc "tạm thời" chưa có lời giải mong các thành viên GPE giúp đỡ:
Vấn đề: Làm sao nối các phần tử trong 1 mảng ảo thành 1 chuỗi
(Để dễ hình dung đơn giản hơn mọi người xem File đính kèm)
Tks!
Nếu là 1 bài toán cụ thể chỉ nối vài phần tử thì cứ nối bình thường. Riêng với bài nối chuổi tổng quát thì dám cá với bạn chẳng có công thức nào làm được
Nối chuổi trên Range ta có thể "cố" bằng cách dùng cột phụ còn nối chuổi trên mảng thì.. THUA ---> chẳng hạn nối chuổi từ mảng =IF(A1:A10 <> "", C1:C10, "")
Nếu là 1 bài toán cụ thể chỉ nối vài phần tử thì cứ nối bình thường. Riêng với bài nối chuổi tổng quát thì dám cá với bạn chẳng có công thức nào làm được
Nối chuổi trên Range ta có thể "cố" bằng cách dùng cột phụ còn nối chuổi trên mảng thì.. THUA ---> chẳng hạn nối chuổi từ mảng =IF(A1:A10 <> "", C1:C10, "")
Vấn đề này em đã thử nghiệm rất nhiều lần và chưa có kết quả, song qua buổi Offline đã có những ý kiến gợi mở. Vậy liệu có thể biến cái không thể thành có thể hay không? Nếu giải quyết được vấn đề này thì đó là bước ngoặt rât lớn cho GPE ta đó!
Vấn đề này em đã thử nghiệm rất nhiều lần và chưa có kết quả, song qua buổi Offline đã có những ý kiến gợi mở. Vậy liệu có thể biến cái không thể thành có thể hay không? Nếu giải quyết được vấn đề này thì đó là bước ngoặt rât lớn cho GPE ta đó!
Nếu có ai đó gợi ý rằng có cách làm thì tôi cũng muốn xem cách đó là CÁCH GÌ để tôi còn học hỏi
Riêng tôi thì đã nghiên cứu vấn đề này từ rất lâu (kể cả tham khảo các trang web nước ngoải) nhưng vẫn chưa tìm ra được cách gì cả (đương nhiên, VBA không có gì để bàn)
Vấn đề này em đã thử nghiệm rất nhiều lần và chưa có kết quả, song qua buổi Offline đã có những ý kiến gợi mở. Vậy liệu có thể biến cái không thể thành có thể hay không? Nếu giải quyết được vấn đề này thì đó là bước ngoặt rât lớn cho GPE ta đó!
Đã biểu hum đó là không có làm theo cách đó được dhn46 ạ, ngoài VBA ra thì công thức không giải quyết nổi.
Cái ý kiến gợi mở đó đâu có được, hum đó cứ kêu là không có máy để thử chứ thử cũng hem có được. Kêu cược vụ đó với mình mà run tay, hiiiiiiiiiiiiiiiii. Sợ phải quay lại HP mời cafe mình lần nữa.
Đã biểu hum đó là không có làm theo cách đó được dhn46 ạ, ngoài VBA ra thì công thức không giải quyết nổi.
Cái ý kiến gợi mở đó đâu có được, hum đó cứ kêu là không có máy để thử chứ thử cũng hem có được. Kêu cược vụ đó với mình mà run tay, hiiiiiiiiiiiiiiiii. Sợ phải quay lại HP mời cafe mình lần nữa.
Đã nói là lập Topic thì phải làm, lời hứa nói rồi đâu rút lại được, hơn nữa biết đâu đấy sẽ có giải pháp cư dân Gpe có tới cả ngàn người mà cư dân sử dụng Excel lên tới cả triệu, chưa đưa lên "nhỡ' có người biết thì biết chia sẻ cùng ai
Ở Hải Phòng óp- la bằng .....cafe hả bé Còi
Ngộ
Híc
Mấy hôm nay bận quá không theo dõi được. Đúng là hôm trước không có máy, mô tả bằng "mồm", nên chưa hiểu rõ yêu cầu đưa ra. Còn bi giờ, quả là vấn đề này công thức không giải quyết được.
Dẫu sao, biết đặt vấn đề và tìm hướng giải quyết, cho dù không thành công, cũng là việc tốt và đáng phải làm.
Mấy hôm nay bận quá không theo dõi được. Đúng là hôm trước không có máy, mô tả bằng "mồm", nên chưa hiểu rõ yêu cầu đưa ra. Còn bi giờ, quả là vấn đề này công thức không giải quyết được.
Dẫu sao, biết đặt vấn đề và tìm hướng giải quyết, cho dù không thành công, cũng là việc tốt và đáng phải làm.
Suy nghĩ logic cũng biết là không làm được! Lý do đơn giản vì trong bộ hàm Excel (kể cả macro 4), cũng không hề có bất cứ hàm nào hổ trợ nối chuổi từ mảng cả
CONCATENATE và toán tử & chỉ nối chuổi từ chuổi đơn, tuyệt đối không làm việc được với mảng
Vậy nên khỏi tìm hay đặt vấn đề chi cho mất công (vì MS không hổ trợ). Tuy nhiên MS cũng không cấm ta tự tạo ra hàm để phục vụ nhu cầu này. Thế thì cứ viết code VBA để giải quyết thôi
Cuộc Offline Hải Phòng vừa rồi có thật nhiều thú vị, học hỏi, tìm hiểu và còn có cả những thắc mắc.
Sau đây là 1 thắc mắc "tạm thời" chưa có lời giải mong các thành viên GPE giúp đỡ:
Vấn đề: Làm sao nối các phần tử trong 1 mảng ảo thành 1 chuỗi
(Để dễ hình dung đơn giản hơn mọi người xem File đính kèm)
Tks!
Theo như cái file và kết quả mong muốn, thì 1 hàm tự tạo (quá đơn giản) cũng giải quyết được vấn đề mà ta?
PHP:
Function NoiPhanTu(ByVal sArray As Variant) As String
Dim TmpArr As Variant
TmpArr = sArray
If IsArray(TmpArr) Then
Dim i As Long, txt As String
For i = LBound(TmpArr) To UBound(TmpArr)
txt = txt & TmpArr(i, 1)
Next
Else
txt = TmpArr
End If
NoiPhanTu = txt
End Function
Theo như cái file và kết quả mong muốn, thì 1 hàm tự tạo (quá đơn giản) cũng giải quyết được vấn đề mà ta?
PHP:
Function NoiPhanTu(ByVal sArray As Variant) As String
Dim TmpArr As Variant
TmpArr = sArray
If IsArray(TmpArr) Then
Dim i As Long, txt As String
For i = 1 To UBound(TmpArr)
txt = txt & TmpArr(i, 1)
Next
Else
txt = TmpArr
End If
NoiPhanTu = txt
End Function
Chẳng đơn giản thế đâu! Viết code thì phải có tính tổng quát nên tôi nghĩ hàm phải có những chức năng thế này:
- Dữ liệu nguồn do người dùng tùy chọn
- Dấu phân cách do người dùng tùy chọn
- Dữ liệu đôi khi có cell rổng ---> Vậy có tính mấy cell rổng này không ---> Cái này cũng do người dùng tùy chọn
Ngoài ra xin nói thêm: Ta chắc chắn không biết trước dữ liệu nguồn là cái gì... là dữ liệu mảng hay range. Nếu là range cũng không biết trước đó là dữ liệu 1 dòng nhiều cột hay 1 cột nhiều dòng, vậy nên For i = 1 To UBound(TmpArr) là quá chủ quan
Chẳng đơn giản thế đâu! Viết code thì phải có tính tổng quát nên tôi nghĩ hàm phải có những chức năng thế này:
- Dữ liệu nguồn do người dùng tùy chọn
- Dấu phân cách do người dùng tùy chọn
- Dữ liệu đôi khi có cell rổng ---> Vậy có tính mấy cell rổng này không ---> Cái này cũng do người dùng tùy chọn
Ngoài ra xin nói thêm: Ta chắc chắn không biết trước dữ liệu nguồn là cái gì... là dữ liệu mảng hay range. Nếu là range cũng không biết trước đó là dữ liệu 1 dòng nhiều cột hay 1 cột nhiều dòng, vậy nên For i = 1 To UBound(TmpArr)là quá chủ quan
- Họ muốn chọn vùng nào cũng được, miễn là liên tục, không thôi lỗi ráng chịu (công thức nào cũng có quy luật của nó)
- Dấu phân cách (nếu muốn) thì thêm vào hàm, chẳng thành vấn đề
- Dữ liệu có cell rỗng tự nó cũng không thể hiện trên kết quả (kiểm tra sẽ thấy)
- Đã khai báo là Variant thì nguồn nào cũng chạy, tuy nhiên đã kiểm tra bằng isArray thì không chạy ra lỗi được đâu!
Ngoài ra người dùng muốn gì thì cứ đưa yêu cầu vì đó là một hàm tự tạo sẽ phục vụ một mục đích nào đó nên ta không nên "ôm đồm" nhiều việc cho một hàm.
Một vài ý kiến của em là vậy.
Phần tô đỏ đúng như Thầy nói là chủ quan thật, phải như vầy mới đúng: For i = LBound(TmpArr) To UBound(TmpArr)
1> Thử chọn vùng A1:C5 xem sao? (tất cả các cell A1:C5 đều có dữ liệu) 2> Cứ thêm tùy chọn có dấu cách vào mới thấy sự khác biệt 3> Chính vì không có có dấu cách nên bạn không thấy sự khác biệt 4> Biết là vậy nên mảng cũng có nhiều loại: 1 chiều, 2 chiều, Base 1 hoặc Base 0 ---> Phần tô đỏ được rồi nhưng phần tô xanh thì chưa đâu, đó là chưa nói mảng cũng là thể là ParamArray (nhiều mảng con trong 1 mảng lớn) ---> Vậy sao không For Each.. cho khỏe?
Tóm lại: Bạn cứ đưa mọi thứ tùy chọn vào hàm, test xong rồi nói tiếp nhé
------------------------
Ngoài ra người dùng muốn gì thì cứ đưa yêu cầu vì đó là một hàm tự tạo sẽ phục vụ một mục đích nào đó nên ta không nên "ôm đồm" nhiều việc cho một hàm.
Cái này là nói tầm bậy nha!
- Ông A có nhu cầu nối chuổi không có dấu phân cách ---> Ta viết hàm A
- Ông B có nhu cầu nối chuổi có dấu phân cách là dấu phẩy---> Ta viết hàm B
- Ông C có nhu cầu nối chuổi có dấu phân cách là dấu gạch ngang---> Ta viết hàm C
Hoặc
- Ông A có nhu cầu nối chuổi không có dấu phân cách ---> Ta viết hàm A
- Ông B có nhu cầu nối chuổi có dấu phân cách là dấu phẩy ---> ta bào ông B tự sửa hàm A thành cái ổng muốn
- Ông C có nhu cầu nối chuổi có dấu phân cách là dấu gạch ngang ---> ta bào ông C tự sửa hàm A thành cái ổng muốn
Má ơi... rảnh lắm sao?
Riêng cá nhân tôi chẳng bao giờ khoái viết 1 hàm chỉ phục vụ cho 1 nhu cầu duy nhất ---> Code viết xong, nó phải trở thành "đồ nghề" dùng cho công việc khác được
1> Thử chọn vùng A1:C5 xem sao? (tất cả các cell A1:C5 đều có dữ liệu) 2> Cứ thêm tùy chọn có dấu cách vào mới thấy sự khác biệt 3> Chính vì không có có dấu cách nên bạn không thấy sự khác biệt 4> Biết là vậy nên mảng cũng có nhiều loại: 1 chiều, 2 chiều, Base 1 hoặc Base 0 ---> Phần tô đỏ được rồi nhưng phần tô xanh thì chưa đâu, đó là chưa nói mảng cũng là thể là ParamArray (nhiều mảng con trong 1 mảng lớn) ---> Vậy sao không For Each.. cho khỏe?
Tóm lại: Bạn cứ đưa mọi thứ tùy chọn vào hàm, test xong rồi nói tiếp nhé
Kiểu For Each nó xác định từ trên xuống dưới, từ trái qua phải, nên cũng dễ dàng xác định được cách thức nó làm việc, tuy nhiên nếu ai đó yêu cầu, nối cột 1 rồi nối tiếp cột 3 bỏ qua cột 2 thì For Each chưa thể đáp ứng được yêu cầu.
Mà thôi tạm thời sửa lại code này theo chiều hướng For Each cho chắc ăn như Thầy nói:
PHP:
Function NoiPhanTu(ByVal sArray As Variant) As String
Dim TmpArr As Variant
TmpArr = sArray
If IsArray(TmpArr) Then
Dim i As Long, txt As String, tmp
For Each tmp In TmpArr
txt = txt & tmp
Next
Else
txt = TmpArr
End If
NoiPhanTu = txt
End Function
Kiểu For Each nó xác định từ trên xuống dưới, từ trái qua phải, nên cũng dễ dàng xác định được cách thức nó làm việc, tuy nhiên nếu ai đó yêu cầu, nối cột 1 rồi nối tiếp cột 3 bỏ qua cột 2 thì For Each chưa thể đáp ứng được yêu cầu.
Mà thôi tạm thời sửa lại code này theo chiều hướng For Each cho chắc ăn như Thầy nói:
Cái quỷ gì cũng làm được tuốt! Cứ nghiên cứu đi
Hàm JoinText tôi viết đã lâu là để phục vụ cho những nhu cầu đa dạng như vậy đấy
Mã:
Function JoinText(ByVal Sep As String, ByVal IgnoreBlanks As Boolean, ParamArray sArray()) As String
Dim tmpArr, SubArr, Arr(), Item, n As Long, tmp As String
'On Error Resume Next
For Each SubArr In sArray
tmpArr = SubArr
If TypeName(tmpArr) <> "Variant()" Then
tmp = IIf(TypeName(tmpArr) = "Error", "", Trim(CStr(tmpArr)))
If IgnoreBlanks = False Or Len(tmp) Then
n = n + 1
ReDim Preserve Arr(1 To n)
Arr(n) = tmp
End If
Else
For Each Item In tmpArr
tmp = IIf(TypeName(Item) = "Error", "", Trim(CStr(Item)))
If IgnoreBlanks = False Or Len(tmp) Then
n = n + 1
ReDim Preserve Arr(1 To n)
Arr(n) = tmp
End If
Next
End If
Next
If n Then JoinText = Join(Arr, Sep)
End Function
Nó chẳng những nối chuổi thông thường mà còn cho phép nối chuổi theo những điều kiện phức tạp nữa đấy
Nếu là 1 bài toán cụ thể chỉ nối vài phần tử thì cứ nối bình thường. Riêng với bài nối chuổi tổng quát thì dám cá với bạn chẳng có công thức nào làm được
Nối chuổi trên Range ta có thể "cố" bằng cách dùng cột phụ còn nối chuổi trên mảng thì.. THUA ---> chẳng hạn nối chuổi từ mảng =IF(A1:A10 <> "", C1:C10, "")
Cái quỷ gì cũng làm được tuốt! Cứ nghiên cứu đi
Hàm JoinText tôi viết đã lâu là để phục vụ cho những nhu cầu đa dạng như vậy đấy
Mã:
Function JoinText(ByVal Sep As String, ByVal IgnoreBlanks As Boolean, ParamArray sArray()) As String
Dim tmpArr, SubArr, Arr(), Item, n As Long, tmp As String
'On Error Resume Next
For Each SubArr In sArray
tmpArr = SubArr
If TypeName(tmpArr) <> "Variant()" Then
tmp = IIf(TypeName(tmpArr) = "Error", "", Trim(CStr(tmpArr)))
If IgnoreBlanks = False Or Len(tmp) Then
n = n + 1
ReDim Preserve Arr(1 To n)
Arr(n) = tmp
End If
Else
For Each Item In tmpArr
tmp = IIf(TypeName(Item) = "Error", "", Trim(CStr(Item)))
If IgnoreBlanks = False Or Len(tmp) Then
n = n + 1
ReDim Preserve Arr(1 To n)
Arr(n) = tmp
End If
Next
End If
Next
If n Then JoinText = Join(Arr, Sep)
End Function
Nó chẳng những nối chuổi thông thường mà còn cho phép nối chuổi theo những điều kiện phức tạp nữa đấy
Lúc Thầy chưa đưa lên cái hàm JoinText thì em đã làm như vầy:
PHP:
Function NoiPhanTu(ByVal sArray As Variant, _
Optional ByVal Spr As String, _
Optional isBlank As Boolean) As String
Dim tmpArr As Variant
tmpArr = sArray
If IsArray(tmpArr) Then
Dim i As Long, txt(), tmp
For Each tmp In tmpArr
If tmp = "" And isBlank = False Then GoTo Next_tmp
i = i + 1
ReDim Preserve txt(1 To i)
txt(i) = tmp
Next_tmp:
Next
NoiPhanTu = Join(txt, Spr)
Else
NoiPhanTu = tmpArr
End If
End Function
Còn cột nào nối chuổi với cột nào, hàng nào nối chuỗi với hàm nào thì tính sau!
Cấu trúc: =NoiPhanTu(Mảng, [Ký tự kết nối], [Bao gồm rỗng=True/False])
- Nếu ta không cần ký tự kết nối, hay bao gồm rỗng thì công thức: =NoiPhanTu(Mảng) ==> abcde
- Nếu ta có ký tự kết nối như thêm dấu gạch nối (-) , công thức: =NoiPhanTu(Mảng, "-") ==> a-b-c-d-e
- Nếu tính luôn cả ô rỗng thì: =NoiPhanTu(Mảng, "-", True) ==> a-b---c-d---e
Function JoinText(ByVal Sep As String, ByVal IgnoreBlanks As Boolean, ParamArray sArray()) As String
Lẽ ra phải như vầy:
Function JoinText(ByVal sArray As Variant, Optional ByVal Sep As String, Optional ByVal IgnoreBlanks As Boolean) As String
Cái nguồn chính để trên đầu, thì tính linh hoạt nó cao hơn! Nếu chỉ nối chuỗi thôi, tôi không cần 2 chọn lựa sau, nếu muốn phân cách thì tôi lại chọn tiếp, muốn bao hàm luôn rỗng thì chọn tiếp.
Vã lại nội dung của code có phần lòng vòng, có thể rút lại ngắn gọn.
Trả lời với bạn về tại sao không dùng 1 mà dùng LBound(array) là vì ở mảng có nhiều chiều, với mảng là 1 chiều thì Item đầu tiên được tính là 0 và các mảng khác thường là 1. Vì thế nếu hàm tham chiếu vào mảng 1 chiều mà gán cho For i = 1 thì sẽ bị thiếu ở mục đầu tiên. Cho nên dù mảng có là gì thì dùng LBound(array) là hợp lý nhất, nó sẽ tính ngay số thứ tự đầu tiên trong mảng. Bạn đã hiểu ý tôi nói không nhỉ?
Function JoinText(ByVal Sep As String, ByVal IgnoreBlanks As Boolean, ParamArray sArray()) As String
Lẽ ra phải như vầy:
Function JoinText(ByVal sArray As Variant, Optional ByVal Sep As String, Optional ByVal IgnoreBlanks As Boolean) As String
Cái nguồn chính để trên đầu, thì tính linh hoạt nó cao hơn! Nếu chỉ nối chuỗi thôi, tôi không cần 2 chọn lựa sau, nếu muốn phân cách thì tôi lại chọn tiếp, muốn bao hàm luôn rỗng thì chọn tiếp.
Vã lại nội dung của code có phần lòng vòng, có thể rút lại ngắn gọn.
Vậy là Nghĩa chưa hiểu thấu đáo vấn đề rồi
Như đã nói ngay từ đầu: Mảng dữ liệu không phải lúc nào cũng liên tục....
Đặt trường hợp nối chuổi cột 1, cột 3, cột 5 thì cái thằng ParamArray sArray sẽ phát huy tác dụng
Mà đã dùng đến ParamArray thì BẮT BUỘC phải bố trí ở biến cuối cùng, sau nó không được có bất cứ biến nào khác nữa ---> Đó là quy định
--------------------------
Trả lời với bạn về tại sao không dùng 1 mà dùng LBound(array) là vì ở mảng có nhiều chiều, với mảng là 1 chiều thì Item đầu tiên được tính là 0 và các mảng khác thường là 1. Vì thế nếu hàm tham chiếu vào mảng 1 chiều mà gán cho For i = 1 thì sẽ bị thiếu ở mục đầu tiên. Cho nên dù mảng có là gì thì dùng LBound(array) là hợp lý nhất, nó sẽ tính ngay số thứ tự đầu tiên trong mảng. Bạn đã hiểu ý tôi nói không nhỉ?
Cái này lại bậy nữa
Chỉ số bắt đầu của 1 phần tử mảng tùy thuộc vào rất nhiều ý tố ---> Không phải cứ mảng 1 chiều thì Item đầu sẽ đánh số thứ tự = 0 đâu
Trước khi trả lời, tôi đã cải thiện hàm lại ở bài 17:
Trả lời với bạn về tại sao không dùng 1 mà dùng LBound(array) là vì ở mảng có nhiều chiều, với mảng là 1 chiều thì Item đầu tiên được tính là 0 và các mảng khác thường là 1. Vì thế nếu hàm tham chiếu vào mảng 1 chiều mà gán cho For i = 1 thì sẽ bị thiếu ở mục đầu tiên. Cho nên dù mảng có là gì thì dùng LBound(array) là hợp lý nhất, nó sẽ tính ngay số thứ tự đầu tiên trong mảng. Bạn đã hiểu ý tôi nói không nhỉ?
Vậy là Nghĩa chưa hiểu thấu đáo vấn đề rồi
Như đã nói ngay từ đầu: Mảng dữ liệu không phải lúc nào cũng liên tục.... Đặt trường hợp nối chuổi cột 1, cột 3, cột 5 thì cái thằng ParamArray sArray sẽ phát huy tác dụng
Mà đã dùng đến ParamArray thì BẮT BUỘC phải bố trí ở biến cuối cùng, sau nó không được có bất cứ biến nào khác nữa ---> Đó là quy định
--------------------------
Cái này lại bậy nữa
Chỉ số bắt đầu của 1 phần tử mảng tùy thuộc vào rất nhiều ý tố ---> Không phải cứ mảng 1 chiều thì Item đầu sẽ đánh số thứ tự = 0 đâu
Lấy hàm JoinText của tôi làm ví dụ: JoinText(", ", TRUE, A1:A10) là nối các phần tử của A1:A10 JoinText(", ", TRUE, A1:A10, C1:C10) là nối các phần tử của A1:A10 và C1:C10 JoinText(", ", TRUE, A1:A10, C1:C10, E1:E10) là nối các phần tử của A1:A10, C1:C10 và E1:E10
vân vân
1 tham chiếu cho công thức đầu tiên
2 tham chiếu cho công thức thứ hai
3 tham chiếu cho công thức thứ ba
Có nghĩa là khi bạn không biết trước số lượng đối số là bao nhiêu thì phải dùng đến ParamArray cho người dùng tùy chọn
Tham khảo thêm cái này: http://www.giaiphapexcel.com/forum/showthread.php?16499-Khai-báo-Optional-Có-thể-bạn-chưa-biết
Lấy hàm JoinText của tôi làm ví dụ: JoinText(", ", TRUE, A1:A10) là nối các phần tử của A1:A10 JoinText(", ", TRUE, A1:A10, C1:C10) là nối các phần tử của A1:A10 và C1:C10 JoinText(", ", TRUE, A1:A10, C1:C10, E1:E10) là nối các phần tử của A1:A10, C1:C10 và E1:E10
vân vân
1 tham chiếu cho công thức đầu tiên
2 tham chiếu cho công thức thứ hai
3 tham chiếu cho công thức thứ ba
Có nghĩa là khi bạn không biết trước số lượng đối số là bao nhiêu thì phải dùng đến ParamArray cho người dùng tùy chọn
Tham khảo thêm cái này: http://www.giaiphapexcel.com/forum/showthread.php?16499-Khai-báo-Optional-Có-thể-bạn-chưa-biết
Đúng là hàm của Thầy giải quyết được với nhiều cột thật độc đáo! Cho nên bài trước em nhận định thừa và nội dung lòng vòng, thì với cách chọn nhiều vùng của ParamArray mới thấy được cái tổng quát của Hàm. Rất hay!
Sử dụng Công thức để nối chuỗi trong Range 1 chiều
Khi đọc được vấn đề này trên diễn đàn đã làm em thôi thúc tìm hiểu về phương pháp nối chuỗi của excel bằng công thức.
Với vấn đề là "mảng ảo" -> em chịu thua
Đối với Range trên bảng tính, em tìm ra một cách để nối những chuỗi (cùng 1 hàng hoặc 1 cột) bằng công thức (không dùng cột phụ)
Nói chung đặt Name khá là vất vả, nhưng tham chiếu khá linh hoạt (có sử dụng Get.cell của Macro4 để lấy giá trị tham chiếu theo giới thiệu từ lâu của thầy Ndu)
Em gửi theo file đính kèm để các Thầy và các Anh, Chị tham khảo!
Tiếp theo Em gửi tiếp phương pháp ghép chuỗi trong Range (1 chiều, liên tục) bằng công thức phần 2: có sử dụng thêm ký tự ở giữa để ghép nối giữa các chuỗi
Cú pháp vẫn tương tự, nhưng thêm phần chuỗi ký tự
=IF(Row( tham chiếu 1 chiều), [JointR] hoặc [JointC],[Chuỗi ký tự ghép nối])
Các thầy, cô và các Anh Chị tham khảo theo file đính kèm.
Tiếp theo Em gửi tiếp phương pháp ghép chuỗi trong Range (1 chiều, liên tục) bằng công thức phần 2: có sử dụng thêm ký tự ở giữa để ghép nối giữa các chuỗi
Cú pháp vẫn tương tự, nhưng thêm phần chuỗi ký tự
=IF(Row( tham chiếu 1 chiều), [JointR] hoặc [JointC],[Chuỗi ký tự ghép nối])
Các thầy, cô và các Anh Chị tham khảo theo file đính kèm.
File có dùng Macro 4
Mà đã gọi là macro thì dù nó mà macro 4 hay macro 40000 (hay mắc cổ gì gì đó) đi nữa thì đàng nào ta vẫn phải Enable macro để công thức có tác dụng, đúng không?
Đã vậy tôi thà viết VBA cho linh hoạt hơn (nối chuổi được với bất cứ mảng nào + và bất cứ điều kiện nối chuổi nào)
Dám cá với bạn rằng chẳng ai đi chọn lựa 1 giải pháp kém hiệu quả như vậy
Suy nghĩ logic cũng biết là không làm được! Lý do đơn giản vì trong bộ hàm Excel (kể cả macro 4), cũng không hề có bất cứ hàm nào hổ trợ nối chuổi từ mảng cả
CONCATENATE và toán tử & chỉ nối chuổi từ chuổi đơn, tuyệt đối không làm việc được với mảng
Vậy nên khỏi tìm hay đặt vấn đề chi cho mất công (vì MS không hổ trợ). Tuy nhiên MS cũng không cấm ta tự tạo ra hàm để phục vụ nhu cầu này. Thế thì cứ viết code VBA để giải quyết thôi
Với những vấn đề được nêu lên ở trên càng làm em cảm thầy tò mò hơn, vì thế, em muốn tìm hiểu mức giới hạn của các hàm excel và Macro4 đối với chuỗi là như thế nào. Tất nhiên là nếu sử dụng VBA thì rất ổn, còn nếu sử dụng công thức có sẵn kết hợp Macro4 thì là điều có thể đúng không thầy (mặc dù có khá nhiều hạn chế)
Với những vấn đề được nêu lên ở trên càng làm em cảm thầy tò mò hơn, vì thế, em muốn tìm hiểu mức giới hạn của các hàm excel và Macro4 đối với chuỗi là như thế nào. Tất nhiên là nếu sử dụng VBA thì rất ổn, còn nếu sử dụng công thức có sẵn kết hợp Macro4 thì là điều có thể đúng không thầy (mặc dù có khá nhiều hạn chế)
Vâng!
- Thử để biết giới hạn của công thức đến đâu
- Thử để biết khả năng của ta đến đâu
Điều đó là rất tốt
Nhưng thử được không có nghĩa là ta sẽ dùng
Giống như các nước Bắc Âu có 1 tập tục cứ đến ngày nào đó thì rủ nhau cả làng đi ngâm mình dưới dòng nước lạnh. Họ làm vậy để thử khả năng chịu đựng tối đa của con người nhưng điều đó không đồng nghĩa là cả năm họ đều khoái nhảy xuống nước lạnh
Ẹc... Ẹc...
Vâng!
- Thử để biết giới hạn của công thức đến đâu
- Thử để biết khả năng của ta đến đâu
Điều đó là rất tốt
Nhưng thử được không có nghĩa là ta sẽ dùng
Giống như các nước Bắc Âu có 1 tập tục cứ đến ngày nào đó thì rủ nhau cả làng đi ngâm mình dưới dòng nước lạnh. Họ làm vậy để thử khả năng chịu đựng tối đa của con người nhưng điều đó không đồng nghĩa là cả năm họ đều khoái nhảy xuống nước lạnh.
Ẹc... Ẹc...
Theo em nghiên cứu được thì rất tốt, nó mở mang tư duy của mình nhưng để áp dụng nó vào thực tế thì đúng là không phải ai cũng thích nhảy xuống nước lạnh. Sếp em mỗi lần nhờ em đặt công thức gì là luôn miệng cháu dùng công thức gì dễ hiểu thôi để cô dễ bàn giao cho bộ phận khác, không lúc họ nhập dữ liệu bị lỗi chả biết vì sao sai mà sửa và phụ thuộc lắm. VBA sếp em còn chả thích dù nó tự động trừ cái gì nó làm sếp quá mất thời gian sếp mới nhờ làm 1 lần ăn ngay.
Em thấy mấy cái bảng sếp em làm toàn If, and với Vlookup, cột phụ thì lằng nhằng nhưng mà vẫn chạy phe phé đó. Thế là công thức mảng mà em học được bị phá sản, tìm hiểu để mở mang tư duy, thoả mãn tính tò mò và phục vụ cho mình là chính. hic.
Theo em nghiên cứu được thì rất tốt, nó mở mang tư duy của mình nhưng để áp dụng nó vào thực tế thì đúng là không phải ai cũng thích nhảy xuống nước lạnh. Sếp em mỗi lần nhờ em đặt công thức gì là luôn miệng cháu dùng công thức gì dễ hiểu thôi để cô dễ bàn giao cho bộ phận khác, không lúc họ nhập dữ liệu bị lỗi chả biết vì sao sai mà sửa và phụ thuộc lắm. VBA sếp em còn chả thích dù nó tự động trừ cái gì nó làm sếp quá mất thời gian sếp mới nhờ làm 1 lần ăn ngay.
Em thấy mấy cái bảng sếp em làm toàn If, and với Vlookup, cột phụ thì lằng nhằng nhưng mà vẫn chạy phe phé đó. Thế là công thức mảng mà em học được bị phá sản, tìm hiểu để mở mang tư duy, thoả mãn tính tò mò và phục vụ cho mình là chính. hic.
Ngoài việc áp dụng để giải quyết vấn đề cho người khác thì ta cũng phải giải quyết file dữ liệu cho chính ta nữa, đúng không?
Vậy thì Sếp yêu cầu thế nào ta làm thế nấy, còn file nào phục vụ cho chính ta thì.. ta cứ việc làm theo ý ta
Thế thôi!
Mục đích cuối cùng là GIẢI QUYẾT NHANH CHÓNG bằng các công cụ mạnh mà ta đang có
Lấy hàm JoinText của tôi làm ví dụ: JoinText(", ", TRUE, A1:A10) là nối các phần tử của A1:A10 JoinText(", ", TRUE, A1:A10, C1:C10) là nối các phần tử của A1:A10 và C1:C10 JoinText(", ", TRUE, A1:A10, C1:C10, E1:E10) là nối các phần tử của A1:A10, C1:C10 và E1:E10
vân vân
1 tham chiếu cho công thức đầu tiên
2 tham chiếu cho công thức thứ hai
3 tham chiếu cho công thức thứ ba
Có nghĩa là khi bạn không biết trước số lượng đối số là bao nhiêu thì phải dùng đến ParamArray cho người dùng tùy chọn
Tham khảo thêm cái này: http://www.giaiphapexcel.com/forum/showthread.php?16499-Khai-báo-Optional-Có-thể-bạn-chưa-biết
Thầy ndu96081631;448582 cho em hỏi. Em không rành VBA lắm nhưng em thử dùng hàm của thầy hình như khi chọn nối một mảng thì nó nối dữ liệu nằm trên một cột trước rồi mới tiếp các cột khác (ý em là thứ tự đó mà!hi). Nhưng nếu muốn tùy chọn để nối dữ liệu trên cùng hàng trước rồi đến hàng tiếp theo thì được không? Và lúc quét chọn thì chọn cả mảng chứ nếu có nhiều dòng mà chọn từng dòng thì cũng sẽ mất thời gian.
Thầy ndu96081631;448582 cho em hỏi. Em không rành VBA lắm nhưng em thử dùng hàm của thầy hình như khi chọn nối một mảng thì nó nối dữ liệu nằm trên một cột trước rồi mới tiếp các cột khác (ý em là thứ tự đó mà!hi). Nhưng nếu muốn tùy chọn để nối dữ liệu trên cùng hàng trước rồi đến hàng tiếp theo thì được không? Và lúc quét chọn thì chọn cả mảng chứ nếu có nhiều dòng mà chọn từng dòng thì cũng sẽ mất thời gian.
Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String
Dim aTmp, arr(), Item, tmp As String
Dim i As Long, n As Long
'On Error Resume Next
For i = LBound(Arrays) To UBound(Arrays)
aTmp = Arrays(i)
If Not IsArray(aTmp) Then aTmp = Array(aTmp)
For Each Item In aTmp
If TypeName(Item) <> "Error" Then
tmp = CStr(Item)
n = n + 1
ReDim Preserve arr(1 To n)
arr(n) = tmp
End If
Next
Next
If n Then JoinText = Join(arr, Delimiter)
End Function
Vậy là phải viết lại hàm hả thầy? Em thấy hàm jointext trước có cú pháp =jointext(", ",TRUE,A1:A7,B1:C7) có thể dùng chung cho trường hợp này được không ạ?
Em làm được rồi thầy ơi, hàm thầy viết trước tổng quát hơn nên có thể dùng được. Do em không nắm được cú pháp (A1:A7&B1:B7) sẽ tạo ra được mảng một cột mà mỗi ô là dữ liệu nối từ 2 ô trên cùng dòng. Cám ơn thầy, nhờ vậy mà em hiểu thêm được một vấn đề nữa!hi
Em còn thắc mắc mong thầy giải đáp: Em thấy hàm ở #36 (thầy nói là tổng quát hơn) khác hàm trước (ở #15) là #36 trong công thức không có TRUE, còn #15 phải có TRUE hoặc FALSE phải không thầy? Thầy có thể giải thích TRUE/FALSE trong công thức #15 có nghĩa gì? Nếu được thầy có thể nêu cú pháp tổng quát của mỗi hàm không ạ? (Giống như khi đánh tên hàm, excel tự hiện ra gợi ý cú pháp vậy)
Em còn thắc mắc mong thầy giải đáp: Em thấy hàm ở #36 (thầy nói là tổng quát hơn) khác hàm trước (ở #15) là #36 trong công thức không có TRUE, còn #15 phải có TRUE hoặc FALSE phải không thầy? Thầy có thể giải thích TRUE/FALSE trong công thức #15 có nghĩa gì? Nếu được thầy có thể nêu cú pháp tổng quát của mỗi hàm không ạ? (Giống như khi đánh tên hàm, excel tự hiện ra gợi ý cú pháp vậy)
=JoinText(Dấu phân cách, [COLOR=#ff0000]Mảng đầu vào[/COLOR])
Riêng với Mảng đầu vào: Có thể là 1 mảng hoặc nhiều mảng tùy ý. Mảng ở đâu có thể là 1 vùng dữ liệu hoặc công thức mảng gì gì đó cũng được
Hàm ở bài 15 có TRUE, FALSE. Ý nghĩa của nó là cho phép lấy dữ liệu rổng hoặc không. Tuy nhiên sau này cải tiến lại tôi thấy đối số này không cần thiết nên đã bỏ đi
Có nghĩa là hàm ở bài 15 bắt buộc phải có đối số TRUE/FALSE còn hàm mới thì có thể dùng điều kiện ở ngay đối số thứ hai phải không thầy?
Em xin hỏi luôn ở công thức thầy gởi: =JoinText(", ",IF(A1:A100="",1/0,A1:A100&B1:B100)) theo em hiểu thì nếu như A1:A100 khác rỗng thì nối còn bằng rỗng thì thực hiện 1/0. Theo em biết thì 1/0 trả về lỗi DIV/0. Vậy nó có ý nghĩa gì trong công thức này? (Em còn dốt excel nên có gì thấy phiền mong thầy bỏ qua!hi)
Em xin hỏi luôn ở công thức thầy gởi: =JoinText(", ",IF(A1:A100="",1/0,A1:A100&B1:B100)) theo em hiểu thì nếu như A1:A100 khác rỗng thì nối còn bằng rỗng thì thực hiện 1/0. Theo em biết thì 1/0 trả về lỗi DIV/0. Vậy nó có ý nghĩa gì trong công thức này? (Em còn dốt excel nên có gì thấy phiền mong thầy bỏ qua!hi)
1/0 là cố tình tạo ra lỗi đấy mà! Bởi hàm JoinText khi gặp lỗi nó sẽ bỏ qua. Chính vì lẽ đó, cái nào mình muốn bỏ đi không lấy, cứ cho nó thành lỗi (lỗi gì tùy ý) là xong
Em đang tìm hiểu về consolidate để tổng hợp dữ liệu từ nhiều file, khi tổng hợp excel có sẵn các hàm nhưng em muốn kết hợp hàm jointext của thầy và consolidate có được không? Bài toán thế này: Giả sử có nhiều file excel (có thể vài chục, giả sử 20 workbook), em muốn nối các chuỗi trong ô A1 của các file trên lại vào một ô (trong một file mới). Ngoài ra nếu trong thời gian sử dụng phát sinh thêm workbook 21, làm sao để nối vào ô A1 mà không cần chỉnh sửa công thức nhiều?
