Giải trí với Excel (1 người xem)

[anhtuan1066]

Bạn rollover79 đưa giải pháp hoàn toàn chính xác
Còn giải pháp của bạn chu.cuoi92 nghe thì có vẽ rất đơn giản và rất thông minh ( nên làm sao bạn là con bò được cơ chứ) ... nhưng trên thực tế ta khó mà thực hiện được, nguyên nhân là:
- Trong 1 sheet, không chỉ có dử liệu thô mà còn chứa các công thức, các Define name và thậm chí chứa các tham chiếu trong công thức kiên kết từ các sheet khác
- Khi bạn chèn thêm sheet, copy dử liệu từ sheet bị lổi sang sheet mới thì buộc phải sửa lại toàn bộ công thức, define name và chua chát nhất là phải sửa tất cả tham chiếu trong các công thức ở tất cả các sheet...
===> Cuối cùng cái giải pháp tưởng chừng đơn giản ấy lại trở nên rắc rối và hoàn toàn không khả thi
----------------------
Còn cách tôi tái hiện lổi là dùng code sau:

Sub Test()
  ActiveSheet.Shapes("Drop Down 1").Delete
End Sub

Không cần dùng For, có bao nhiêu Validation cũng bị xóa sạch
-----------------
Chức năng Open and Repair quả thật lợi hại, có điều tôi vẫn không hình dung được MS Excel đã sửa lổi như thế nào

 

[rollover79]

Bạn rollover79 đưa giải pháp hoàn toàn chính xác
Còn giải pháp của bạn chu.cuoi92 nghe thì có vẽ rất đơn giản và rất thông minh ( nên làm sao bạn là con bò được cơ chứ) ... nhưng trên thực tế ta khó mà thực hiện được, nguyên nhân là:
- Trong 1 sheet, không chỉ có dử liệu thô mà còn chứa các công thức, các Define name và thậm chí chứa các tham chiếu trong công thức kiên kết từ các sheet khác
- Khi bạn chèn thêm sheet, copy dử liệu từ sheet bị lổi sang sheet mới thì buộc phải sửa lại toàn bộ công thức, define name và chua chát nhất là phải sửa tất cả tham chiếu trong các công thức ở tất cả các sheet...
===> Cuối cùng cái giải pháp tưởng chừng đơn giản ấy lại trở nên rắc rối và hoàn toàn không khả thi
----------------------
Còn cách tôi tái hiện lổi là dùng code sau:

Sub Test()
  ActiveSheet.Shapes("Drop Down 1").Delete
End Sub

Không cần dùng For, có bao nhiêu Validation cũng bị xóa sạch
-----------------
Chức năng Open and Repair quả thật lợi hại, có điều tôi vẫn không hình dung được MS Excel đã sửa lổi như thế nào

Đoạn code này của bác về bản chất vẫn vậy, nhưng code này không phải lúc nào cũng chạy đúng được, nó chỉ chạy đúng khi ta tạo Validation đầu tiên, còn nếu ta vẽ vài cái hình, hoặc tạo vài cái comment trước thì code này không còn đúng nữa. Cái cần lưu ý ở đây là khi tạo Comment hoặc Validation thì excel sẽ tạo ra các Shape ẩn có tên bắt đầu là Comment ... và Drop Down .... Nên khi ai đó muốn sử dụng code để xoá sạch các đối tượng Shapes do người dùng tạo ra thì nhớ chừa mấy cái này ra, hoặc cứ xóa sạch đi cũng được nhưng phải biết cách khắc phục bằng Repair. Còn cơ chế Repair theo tôi là excel quản lý cái Drop Down đó dựa vào cái Validation của 1 ô nào đó, ở đây Validation vẫn còn thì việc khôi phục lại cũng không khó. Vấn đề là nó tổ chức như nào, và theo tôi lẽ ra khi ta tạo lại validation thì excel phải tự khôi phục lại thì ngon hơn ...

 

[anhtuan1066]

Kiểm tra sự giao nhau giữa 2 vùng dử liệu

- Giả sư cho trước vùng H10:K20 (tạm gọi là vùng 1)
- Dùng chuột quét chọn 1 vùng tùy ý (tạm gọi là vùng 2)
Để kiểm tra sự giao nhau giữa vùng 1 và vùng 2, nếu dùng VBA thì ta sẽ viết như sau:

Function IntRng(Range As Range) As Boolean
  Application.Volatile
  IntRng = Not Intersect(Range, Selection) Is Nothing
End Function

Để kiểm tra, tôi gõ vào 1 cell nào đó (không thuộc vùng H10:K20) công thức =IntRng(H10:K20)
- Quét chọn 1 vùng bất kỳ và bấm F9, nếu:

a) Vùng mà ta quét chọn có giao điểm với H10:K20 thì công thức trả về giá trị = TRUE
b) Vùng mà ta quét chọn không có giao điểm với H10:K20 thì công thức trả về giá trị = FALSE
​

-------------------------
Xin hỏi: Có thể làm được yêu cầu như trên mà không dùng VBA hay không? (chỉ dùng công thức)
-------------------------
Bài này cũng khá hay và.. hóc... Xin mời các cao thủ ra tay! (Quan trọng là xác định được địa chỉ của Selection)

 

[anhtuan1066]

Lổi #VALUE! với hàm SUM

Có 1 file dử liệu thế này:
- Từ A2 đến A20 là các giá trị Number
- Từ B2 đến B20 có cell chứa Number, có cell chứa Text
- Tại C2 người ta nhập công thức: =SUM(A2-B2)
- Mong muốn của người dùng là: Nếu cell nào trong cột B chứa Text thì xem như nó = 0
--------------------------
Mới các bạn xem file đính kèm và sửa lại công thức sao cho đạt được kết quả như ý muốn, triệt tiêu được lổi #VALUE! mà không phải thêm bất cứ hàm nào (Chỉ có SUM mà thôi)...
--------------------------
Có người đã sửa công thức này như sau: =SUM(A2)-SUM(B2) và kết quả OK... Xin hỏi còn cách nào ngắn hơn không?
(bài này khá dể, các bạn thử xem)

 

[thanh_tks]

Có 1 file dử liệu thế này:
- Từ A2 đến A20 là các giá trị Number
- Từ B2 đến B20 có cell chứa Number, có cell chứa Text
- Tại C2 người ta nhập công thức: =SUM(A2-B2)
- Mong muốn của người dùng là: Nếu cell nào trong cột B chứa Text thì xem như nó = 0
--------------------------
Mới các bạn xem file đính kèm và sửa lại công thức sao cho đạt được kết quả như ý muốn, triệt tiêu được lổi #VALUE! mà không phải thêm bất cứ hàm nào (Chỉ có SUM mà thôi)...
--------------------------
Có người đã sửa công thức này như sau: =SUM(A2)-SUM(B2) và kết quả OK... Xin hỏi còn cách nào ngắn hơn không?
(bài này khá dể, các bạn thử xem)

Như thế này phải không Anh :=A2-SUM(B2)

 

[anhtuan1066]

Như thế này phải không Anh :=A2-SUM(B2)

Chính xác!
Còn cách khác nữa đấy
Hi... hi... Bài này dể quá nên giải nhanh nhỉ?

 

[thanh_tks]

Chính xác!
Còn cách khác nữa đấy
Hi... hi... Bài này dể quá nên giải nhanh nhỉ?

còn cách nào nữa vậy? anh cho em học hỏi đi!

 

[anhtuan1066]

còn cách nào nữa vậy? anh cho em học hỏi đi!

Công thức này chắc OK:
=-SUM(-A2,B2)
--------------------
sao chẳng thấy 2 bắt tay với bài #63 thế nhỉ?
Gợi ý: Dùng thêm hàm Macro 4

 

[anhtuan1066]

Tìm 1 chuổi trong 1 Drawing Object

Giả sử tôi có 1 file, trong đó tôi vẽ 1 Rectangle (bằng công cụ Drawing)... Bên trong Rectangle này tôi gõ 1 chuổi gì đó, chẳng hạn là Cộng hòa xã hội chủ nghĩa Việt Nam
Câu hỏi: Làm sao biết được 1 chuổi có nằm trong Rectangle hay không?
(Ví dụ tôi muốn tìm chuổi Việt Nam)

 

[Po_Pikachu]

Giả sử tôi có 1 file, trong đó tôi vẽ 1 Rectangle (bằng công cụ Drawing)... Bên trong Rectangle này tôi gõ 1 chuổi gì đó, chẳng hạn là Cộng hòa xã hội chủ nghĩa Việt Nam
Câu hỏi: Làm sao biết được 1 chuổi có nằm trong Rectangle hay không?
(Ví dụ tôi muốn tìm chuổi Việt Nam)

Muốn tìm Text gì thì đầu tiên là phài lấy đoạn text đó ra trước đã.
Lấy Text ra thì được rồi! Nhưng 1 điều kỳ lạ là nếu Rectangle cuối cùng mà không có text thì nó bị lỗi nhỉ?

Sub Chay()
Dim str As String
On Error GoTo Loi
For Each Rc In ActiveSheet.Shapes
Rc.Select
[B]str = Selection.Text[/B]
MsgBox Rc.Name & Chr(10) & "CO Text" & Chr(10) & str
str = ""
Loi:
    MsgBox Rc.Name & Chr(10) & "Khong co Text"
Next
End Sub

Thân.

 

[anhtuan1066]

Muốn tìm Text gì thì đầu tiên là phài lấy đoạn text đó ra trước đã.
Lấy Text ra thì được rồi! Nhưng 1 điều kỳ lạ là nếu Rectangle cuối cùng mà không có text thì nó bị lỗi nhỉ?

Sub Chay()
Dim str As String
On Error GoTo Loi
For Each Rc In ActiveSheet.Shapes
Rc.Select
[B]str = Selection.Text[/B]
MsgBox Rc.Name & Chr(10) & "CO Text" & Chr(10) & str
str = ""
Loi:
    MsgBox Rc.Name & Chr(10) & "Khong co Text"
Next
End Sub

Thân.

Topic này là Giải trí với các công thức Excel mà bạn!
Tức là bạn có thể dùng bất cứ cách gì để giải quyết, miễn không phải là VBA

 

[Po_Pikachu]

Ẹc..
Công thức mà đụng đến Shapes luôn hả?
Đừng nói là Macro4 nha bác.
Thân.

 

[anhtuan1066]

Ẹc..
Công thức mà đụng đến Shapes luôn hả?
Đừng nói là Macro4 nha bác.
Thân.

Mọi cách, tức bao gồm:
- Công thức
- Công cụ có sẳn của Excel
- Macro 4 (vì nó có thể gán vào Define name)
vân.. vân...
Nhưng ngoại trừ VBA
Gợi ý nhỏ: Bài này không cần dùng đến macro 4

 

[Po_Pikachu]

- Giả sư cho trước vùng H10:K20 (tạm gọi là vùng 1)
- Dùng chuột quét chọn 1 vùng tùy ý (tạm gọi là vùng 2)
Để kiểm tra sự giao nhau giữa vùng 1 và vùng 2, nếu dùng VBA thì ta sẽ viết như sau:

Function IntRng(Range As Range) As Boolean
  Application.Volatile
  IntRng = Not Intersect(Range, Selection) Is Nothing
End Function

Để kiểm tra, tôi gõ vào 1 cell nào đó (không thuộc vùng H10:K20) công thức =IntRng(H10:K20)
- Quét chọn 1 vùng bất kỳ và bấm F9, nếu:

a) Vùng mà ta quét chọn có giao điểm với H10:K20 thì công thức trả về giá trị = TRUE
b) Vùng mà ta quét chọn không có giao điểm với H10:K20 thì công thức trả về giá trị = FALSE
​

-------------------------
Xin hỏi: Có thể làm được yêu cầu như trên mà không dùng VBA hay không? (chỉ dùng công thức)
-------------------------
Bài này cũng khá hay và.. hóc... Xin mời các cao thủ ra tay! (Quan trọng là xác định được địa chỉ của Selection)

Thế dùng Function VBA được không bác!
Thân.

 

[anhtuan1066]

Thế dùng Function VBA được không bác!
Thân.

Là sao ta? Là hàm tự tạo à?
Đã bảo không dùng VBA rồi mà!
Tóm lại: Bạn không Alt + F11 rồi vẽ vời gì trong đó là được rồi

 

[anhtuan1066]

Giả lập Status Function

Trên thanh Status của Excel có thiết kế sẳn 6 Function: Average, Count, Count Nums, Max, Min và Sum... để khi chúng ta quét chọn 1 vùng nào đó trên bảng tính thì chúng sẽ tự tính toán và hiển thị





Giờ tôi dùng công thức Excel để giả lập chức năng này, tức chúng ta cũng sẽ quét chọn vùng rồi chọn Function trong 1 ComboBox và kết quả sẽ hiển thị ra 1 cell nào đó




Xem file đính kèm này thử nhé
Thú vị chứ? Hi... Hi...
(Các bạn có hướng nào cải tiến hay hơn không?)

 

[anhtuan1066]

Chạy chữ trên cell, dùng công thức

Thường thì người ta thực hiện yêu cầu này bằng code VBA, nhưng với mục đích giải trí, ta hãy thử xem công thức có làm được điều này không nha!
- Đầu tiên ta vào menu Tools\Options, tab Calcuation hãy check vào mục Iteration




- Vào menu Insert\Name\Define và tạo 1 name

Text = "Giải pháp Excel - Công cụ tuyệt vời của bạn                 "

Lưu ý có 20 khoảng trắng sau chuổi
- Tại cell A1, gõ vào công thức:

=MID(Text,2,LEN(Text))&LEFT(Text,1)

- Lại vào menu Insert\Name\Define và sửa lại name Text thành:

Text =Sheet1!$A$1

Giờ hãy bấm F9 và xem kết quả hiển thị tại A1!
Thú vị chứ!

 

[Ba Tê]

Đã xem tất cả các bài trong Topic này, chỉ nói được một câu:" Bái......<100 lần> phục!

 

[lemanhha0276]

Cái này hay quá. cảm ơn anhtuan nhiều.
QUOTE=anhtuan1066;158591]Tôi sưu tầm file này khá hay (phải nói là công phu thì đúng hơn)
Các bạn hãy mở file đính kèm dưới đây rồi làm như sau:
1> Bấm Ctrl + A để chọn toàn bộ bảng tính
2> Vào menu Format\Row\Height và gõ vào số 15.33
3> Vào menu Format\Column\Width và gõ vào số 2.4
Format xong, các bạn nhìn thấy điều gì?[/QUOTE]

 

[anhtuan1066]

Chuổi tự động vừa khít với chiều rộng cell

Có trò này cũng hay hay. Mời Xem đoạn video clip dưới đây

[video=youtube;pBOVK9wiymM]http://www.youtube.com/watch?v=pBOVK9wiymM[/video]


Chú ý cell A7, trong đó là những dấu - màu đỏ. Tại sao nó có thể tự động thêm bớt cho vừa khít với chiều rộng của cell nhỉ?
Hi... Hì...