22/12/20

Hàm và Ngôn ngữ M trong Power query

Tài liệu của Tác Giả: PTM0412 Diễn Đàn: Giải Pháp Excel: Power query là 1 công cụ rất mạnh mẽ trong Excel và trong Power BI, dùng để lấy và ... thumbnail 1 summary
Tài liệu của Tác Giả: PTM0412
Diễn Đàn: Giải Pháp Excel:

Power query là 1 công cụ rất mạnh mẽ trong Excel và trong Power BI, dùng để lấy và xử lý dữ liệu từ rất nhiều nguồn khác nhau. Chúng ta có thể sử dụng Power query bằng cách sử dụng menu (ribbon) trên giao diện Excel cũng như giao diện Query editor một cách trực quan từng bước. Tuy nhiên so sánh với lập trình VBA của Excel thì làm như vậy mới chỉ dừng ở mức record macro.

Tiến lên một bước nữa với ngôn ngữ M, ta có thể chỉnh sửa M-Code theo ý muốn, thậm chí viết ra hẳn những query riêng biệt cho mình không qua thao tác có sẵn của giao diện.

Mọi ngôn ngữ lập trình đều cần phải có những hiểu biết căn bản của ngôn ngữ đó, và tài liệu này phần nào đáp ứng những kiến thức căn bản nhất của ngôn ngữ M.

Do đây chỉ là tài liệu lý thuyết nên khi đọc sẽ cảm thấy khô khan vì thiếu nhiều những thí dụ minh hoạ, tuy nhiên người đọc sẽ cần phải nắm vững lý thuyết và ứng dụng vào thực hành rất nhiều mới có thể sử dụng thành thạo ngôn ngữ này.

Cụ thể như mục viết hàm và nhất là hàm đệ quy, phải có được bài toán cụ thể, phù hợp thì mới ứng dụng được, còn không thì mới chỉ là lý thuyết suông.
Mong rằng tài liệu ngắn ngủi này mang lại chút kiến thức nhỏ bé cho các bạn nào cần dùng.

DOWNLOAD

===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

24/11/20

Chuyển công thức ở chuỗi text thành hàm trong Excel

Chuyển Text thành Formulas là một trong những thủ thuật hay mà mình mong muốn được chia sẻ đến bạn trong bài viết này. Trường hợp tuy hiếm g... thumbnail 1 summary
Chuyển Text thành Formulas là một trong những thủ thuật hay mà mình mong muốn được chia sẻ đến bạn trong bài viết này. Trường hợp tuy hiếm gặp với các công việc liên quan đến tính toán, tuy nhiên với thiết kế hay dự toán coppha nhôm, mình thường xuyên dùng tới.

11/11/20

HƯỚNG DẪN SỬ DỤNG SUBTOTAL ĐỂ CỘNG DỒN CÁC MỤC GIỐNG NHAU TRONG EXCEL

HƯỚNG DẪN SỬ DỤNG SUBTOTAL ĐỂ CỘNG DỒN CÁC MỤC GIỐNG NHAU TRONG EXCEL Đôi khi, chúng ta có nhu cầu cộng hàng loạt các mã hàng bán được tro... thumbnail 1 summary

HƯỚNG DẪN SỬ DỤNG SUBTOTAL ĐỂ CỘNG DỒN CÁC MỤC GIỐNG NHAU TRONG EXCEL

Đôi khi, chúng ta có nhu cầu cộng hàng loạt các mã hàng bán được trong 1 bảng tính. Thì subtotal là công cụ phù hợp nhất trong trường hợp này.

Trong clip này, tôi sẽ hướng dẫn các bạn làm điều đó.

Excel bị ẩn TAB tên Sheet thì làm như thế nào

Excel bị ẩn TAB tên Sheet thì làm như thế nào Ví dụ như file sau: Ta vào file =>Option=>Advanced => Tìm tới dòng Show sheet... thumbnail 1 summary



Excel bị ẩn TAB tên Sheet thì làm như thế nào

Ví dụ như file sau:

Ta vào file =>Option=>Advanced =>

Tìm tới dòng Show sheet tab, check vào



===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

27/10/20

Tự tạo danh sách để fill

Làm thế nào để chỉ cần gõ 1 giá trị đầu, sau đó kéo xuống sẽ tự điền giá trị tuần tự Bằng cách tự tạo 1 danh sách, bạn có thể chỉ cần gõ 1 g... thumbnail 1 summary
Làm thế nào để chỉ cần gõ 1 giá trị đầu, sau đó kéo xuống sẽ tự điền giá trị tuần tự

Bằng cách tự tạo 1 danh sách, bạn có thể chỉ cần gõ 1 giá trị đầu, sau đó fill xuống hoặc fill ngang bằng cái Fill Handle, danh sách sẽ được tự động điền vào tuần tự.​
Excel cung cấp cho bạn 1 khả năng tiết kiệm đáng kể thời gian cho việc nhập liệu, không chỉ cho số mà cả cho chuỗi. Nó có tạo sẵn cho bạn vài danh sách: tháng trong năm, thứ trong tuần. Bạn chỉ cần gõ 1 từ đầu (có trong list), rồi kéo cái Fill Handle, danh sách sẽ tự động được điền vào với những giá trị kế tiếp của list. Thí dụ bạn gõ Tue, và fill xuống, các ô kế sẽ tự động được điền là Wed, Thu, Fri, Sat, Sun, Mon, và trở lại Tue nếu còn tiếp.​

23/10/20

LÀM THẾ NÀO ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT Ô

Thỉnh thoảng, thay vì phải đếm tất cả ký tự trong một ô, bạn có thể chỉ cần phải đếm sự xuất hiện của một ký tự, chữ số, hay biểu tượng đặc ... thumbnail 1 summary

Thỉnh thoảng, thay vì phải đếm tất cả ký tự trong một ô, bạn có thể chỉ cần phải đếm sự xuất hiện của một ký tự, chữ số, hay biểu tượng đặc biệt.

Để đếm tần suất xuất hiện của một ký tự xác định trong một ô, hãy sử dụng hàm LEN kết hợp với hàm SUBSTITUTE:

=LEN(ô)-LEN(SUBSTITUTE(ô, ký tự,””))

Để hiểu rõ ví dụ hơn, hãy xem qua ví dụ dưới đây nhé.

Gỉả sử, bạn lưu giữ một cơ sở dữ liệu của những món hàng được giao, nơi mà mỗi loại hàng có ký hiệu nhận dạng riêng biệt. Và mỗi ô có một vài món hàng cách nhau bởi dấu phẩy, khoảng trống, hay bất cứ dấu phân cách nào. Nhiệm vụ của bạn là hãy đếm số lần xuất hiện của ký hiệu nhận dạng riêng biệt được xác định trong mỗi ô.

Gỉả sử rằng danh sách các món hàng nằm ở cột B (bắt đầu từ B2) và chúng ta sẽ đếm số lần xuất hiện của “A”, dưới đây là công thức:

=LEN(B2)-LEN(SUBSTITUTE(B2,”A”,””))



CÔNG THỨC ĐẾM KÝ TỰ NÀY TRONG EXCEL HOẠT ĐỘNG NHƯ THẾ NÀO

Để hiểu quy luật của công thức này, hãy chia nó ra thành từng phần nhỏ:

Đầu tiên, bạn đếm tổng chiều dài chuỗi của ô B2:

LEN(B2)

Sau đó, bạn dùng hàm SUBSTITUTE để loại bỏ số lần xuất hiện của “A” trong B2 bằng cách thay thế nó bằng một chuỗi rỗng(“”):

SUBSTITUTE(B2,”A”,””)

Sau đó, bạn đếm chiều dài chuỗi mà không có ký tự “A”:

LEN(SUBSTITUTE(B2,”A”,””))

Cuối cùng, bạn lấy tổng chiều dài chuỗi trừ cho chiều dài của chuỗi không có ký tự “A”.

Do đó, bạn tính được số ký tự “được loại bỏ”, kết quả này cũng là số lần xuất hiện của ký tự đó trong một ô.

Thay vì xác định ký tự bạn muốn đếm trong công thức, bạn có thể gõ nó vào một vài ô, rồi tham chiếu ô đó trong công thức. Bằng cách này, người dùng của bạn sẽ có khả năng đếm số lần xuất hiện của bất cứ ký tự nào họ nhập vào ô đó mà không phá công thức của bạn:



  • Lưu ý. Hàm SUBSTITUTE của Excel là một hàm phân biệt dạng chữ, và vì thế các công thức trên đều phân biệt dạng chữ theo. Ví dụ, trong ảnh chụp màn hình ở trên, ô B3 có 3 lần xuất hiện của “A”-hai lần viết hoa, và một lần viết thường. Công thứ c chỉ đếm các ký tự viết hoa bởi vì chúng ta cung cấp “A” cho hàm SUBSTITUTE.
Nguồn: hocexcel online
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

22/10/20

Tăng số lần undo Cho Excel

Tất cả chúng ta đều đã quen thuộc với chức năng Undo của Excel, cho phép chúng ta làm lại những sai lầm của mình. Tuy nhiên, mặc định, chúng... thumbnail 1 summary
Tất cả chúng ta đều đã quen thuộc với chức năng Undo của Excel, cho phép chúng ta làm lại những sai lầm của mình. Tuy nhiên, mặc định, chúng ta chỉ có thể Undo được 16 lần. Chiêu này giúp bạn có thể tăng số lần Undo lên nhiều hơn, có thể đến 100 lần.
Khi bạn sử dụng chức năng Undo của Excel, và bạn đã thực hiện Undo 16 lần, thì nút Undo bị mờ đi, không thể Undo được nữa. Ngoài ra, khi bạn nhấn nút Save để lưu bảng tính, thì nút Undo cũng bị mờ đi, và danh sách những tác vụ (những hành động đã thực hiện trên bảng tính) mà bạn đã thực hiện bị mất sạch. Đó là do khi bạn lưu bảng tính, Excel cho rằng bạn đã hài lòng với bảng tính này (thì mới nhấn Save), và nó thấy rằng không cần thiết phải giữ lại danh sách những tác vụ trước khi lưu nữa.

22/7/20

Tạo các định dạng số cho riêng bạn

Excel đã có những định dạng số của riêng nó nhưng thỉnh thoảng bạn cần sử dụng một định dạng số không có sẵn trong Excel. Sử dụng chiêu này,... thumbnail 1 summary
Excel đã có những định dạng số của riêng nó nhưng thỉnh thoảng bạn cần sử dụng một định dạng số không có sẵn trong Excel. Sử dụng chiêu này, bạn có thể tạo ra các định dạng số mà bạn có thể điều chỉnh cho nhu cầu riêng của bạn.​

Trước khi bạn thử những chiêu này, sẽ có ích cho bạn nếu bạn hiểu được làm thế nào Excel nhận biết được các định dạng của ô. Excel nhận biết một định dạng của ô khi có 4 phần sau (từ trái sang phải): số dương, số âm, giá trị 0, và giá trị chuỗi. Mỗi phần được ngăn cách bởi một dấu chấm phẩy (.​

Khi bạn tạo ra một định dạng số cho riêng bạn, bạn không cần phải định dạng tất cả 4 phần này. Nói cách khác, nếu định dạng của bạn chỉ có hai phần thì phần đầu tiên được sử dụng cho cả các số dương và các giá trị 0, trong khi phần thứ 2 sẽ được sử dụng cho số âm.​

Nếu định dạng của bạn chỉ bao gồm một phần, tất cả các loại số sẽ dùng một định dạng. Chuỗi bị ảnh hưởng bởi các định dạng riêng chỉ khi bạn sử dụng tất cả 4 phần, phần cuối sẽ sử dụng cho chuỗi .​

9/5/20

Thao tác trên dữ liệu với Advanced Filter

Chiêu 32: Thao tác trên dữ liệu với Advanced Filter Nếu bạn đã quen với công cụ Auto Filter, bạn hẳn đã thấy những hạn chế của AutoFilter. N... thumbnail 1 summary
<700 and="" mluong=""><700 and="" mluong=""><700 and="" mluong="">
Chiêu 32: Thao tác trên dữ liệu với Advanced Filter
Nếu bạn đã quen với công cụ Auto Filter, bạn hẳn đã thấy những hạn chế của AutoFilter. Nếu bạn cần xử lý chọn lọc dữ liệu ngoài giới hạn này, Advanced Filter là 1 chọn lựa tốt.​
Dù cho bị hạn chế, AutoFilter cũng đã là 1 công cụ hữu ích để chỉ thể hiện 1 phần dữ liệu lên màn hình theo 1 vài điều kiện nào đó. Nhưng đôi khi bạn không thể chọn lọc thông tin cần thiết khi dùng AutoFilter.​
Xin nói thêm, Excel 2003 và kể cả Excel 2007 chỉ có thể lọc 1 lần 2 điều kiện (And hoặc Or) cho 1 cột. Hơn thế, nếu bạn muốn lọc dữ liệu theo 2 tiêu chí trên 2 cột, thì chỉ có thể kết hợp bằng toán tử And giữa các điều kiện của 2 cột. ​

24/4/20

Sắp xếp ngẫu nhiên

Chiêu thứ 31: Bạn có thể dùng Excel để chọn ra một cách ngẫu nhiên ba người thắng cuộc – 1, 2 và 3 từ một danh sách trên bảng tính. Để thự... thumbnail 1 summary
Chiêu thứ 31:

Bạn có thể dùng Excel để chọn ra một cách ngẫu nhiên ba người thắng cuộc – 1, 2 và 3 từ một danh sách trên bảng tính. Để thực hiện điều này một cách dễ dàng nhất và công bằng nhất, bạn nên dùng hàm RAND kết hợp với tính năng sắp xếp của Excel.

Giả sử bạn có một bảng dữ liệu như hình sau:

224546

Tại ô A2 bạn nhập vào hàm =RAND và sao chép xuống đến ô A10. Bạn có thể sắp xếp các cột Name, Age và ID No. theo cột A và từ đó chọn ra được ngẫu nhiên 3 người thắng cuộc.

Hàm RAND sẽ được tự động tính lại mỗi khi có sự thay đổi trên bảng tính, do vậy chúng ta có thể nhấn F9 để ép Excel tính toán lại và từ đó chọn ra được những phần tử trong danh sách một cách ngẫu nhiên. Tuy nhiên chúng ta cần phải tạo một thủ tục sắp xếp lại dữ liệu sau khi Excel tạo ra bộ số ngẫu nhiên mới.

Để việc chọn lựa được dễ dàng, chúng ta sẽ ghi một Macro và gán nó vào một nút lệnh trên bảng tính. Mỗi khi muốn chọn ra nhóm người thắng cuộc thì bạn chỉ cần nhấn nút lệnh này. Bạn làm theo hướng dẫn sau:

Bước này bạn sẽ tiến hành ghi Macro, bạn chọn một ô bất kỳ và vào Developer ➝ Code ➝ Record Macro (E2003: Tools ➝ Macro ➝ Record New Macro…). Chọn 4 cột A, B, C và D rồi nhấn F9. Vào Data ➝ Sort & Filter ➝ Sort ➝ chọn sắp xếp cột A theo thứ tự tùy ý (nhỏ ➝ lớn hoặc lớn ➝ nhỏ).

224547

Bước tiếp theo, bạn hãy vào ngăn Developer ➝ nhóm Controls ➝ Insert (E2003: View ➝ Toolbars ➝ Forms) ➝ chọn Button (Form Control) và vẽ thành một nút trên bảng tính ➝ chọn tên Macro vừa ghi ➝ nhấn OK để hoàn tất.

224548

Bạn chọn cột A và ẩn nó để người dùng không thấy các số ngẫu nhiên. Mỗi lần bạn nhấp chuột lên nút Pick Winner thì dữ liệu của bạn sẽ được sắp xếp một cách ngẫu nhiên và bạn có thể dễ dàng chọn ra 3 người thắng cuộc.

224549

Lưu ý: Hàm RAND trong các phiên bản trước Excel 2003 có tồn tại lỗi trong việc phát số ngẫu nhiên. Về mặt lý thuyết, hàm RAND chỉ trả về các giá trị từ 0 đến 1, điều này không phải luôn luôn đúng khi phát nhiều số ngẫu nhiên vì đôi khi hàm trả về giá trị nhỏ hơn 0. Xem thêm thông tin về việc Microsoft thay đổi giải thuật phát số ngẫu nhiên trong các phiên bản Excel 2003 & 2007 tại http://support.microsoft.com/default.aspx?kbid=828795.


Tải Ví Dụ

===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

18/4/20

Chiêu 30: Sort thứ tự dựa trên nhiều hơn ba cột

Chiêu 30: Sort thứ tự dựa trên nhiều hơn ba cột Chức năng sort của Excel bị giới hạn với sort trong phạm vi 3 cột. Trong hầu hết các trườn... thumbnail 1 summary
Chiêu 30: Sort thứ tự dựa trên nhiều hơn ba cột

Chức năng sort của Excel bị giới hạn với sort trong phạm vi 3 cột. Trong hầu hết các trường hợp, sort theo 3 cột là đủ nhưng đôi khi bạn cần sort nhiều hơn 3 cột dữ liệu. Chiêu này sẽ giúp bạn vượt qua hạn chế này.

Với ví dụ sau, chúng tôi giả sử bạn có các cột dữ liệu liên quan với nhau A, B, C, D và E, và bạn muốn sort dữ liệu này đầu tiên theo cột A, tiếp theo là cột B, kế đến là cột C, tiếp nữa là cột D và cuối cùng là cột E.

Để làm điều này, bạn sort lần lượt theo thứ tự ngược: sort theo cột cuối cùng trước và tiếp theo tuần tự ngược đến cột đầu tiên. Chọn các cột từ A đến E, sau đó chọn Data ➝ Sort. Chọn để sort theo thứ tự cột C đầu tiên, tiếp theo là đến cột D và sau đó đến cột E. Nhấn vào sort. Bây giờ chọn các cột từ A đến E và chọn Data ➝ Sort. Lúc này, sort theo thứ tự cột A trước rồi mới đến cột B. Click sort và mọi thứ sẽ được sắp xếp theo thứ tự.

Excel đã sort theo 5 cột thay vì 3 cột. Nếu bạn muốn Excel tự động làm điều này, bạn có thể sử dụng một macro mà sẽ sort theo vùng chọn và giả định rằng dữ liệu của bạn có các tiêu đề cột được xác định và định dạng ở hàng đầu tiên của vùng chọn. Nếu các tiêu đề được in đậm, Excel sẽ cho biết chúng là các tiêu đề và sẽ không sort chúng. Thay vào đó, nó sẽ sort cột đầu tiên bên trái trước rồi mới tới cột bên phải cho đến tối đa là 256 cột.

Đoạn code macro bạn cần sử dụng phải được đặt trong một module chuẩn. Để làm điều này, bạn chọn Tools ➝ Macro ➝ Visual Basic Editor (Hoặc nhấn Alt+F11), sau đó chọn Insert ➝ Module và gõ đoạn code sau vào:


Sub SortByX( )
Dim i As Long
For i = Selection.Columns.Count To 1 Step -1
Selection.Sort Key1:=Selection.Cells(2, i), _
Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next i
End Sub
Để quay lại excel, bạn đóng cửa sổ code này lại hoặc nhấn Alt+ Q. Ngay khi bạn dán đoạn code này vào, bạn đã có thể thực hiện được việc sort này: Tô chọn vùng cần sort, kể cả tiêu đề, rồi chạy code.​
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

17/4/20

Chiêu thứ 29: Tùy biến chú thích của ô bảng tính

Chiêu thứ 29: Tùy biến chú thích của ô bảng tính Khi chúng ta chèn chú thích cho ô bằng lệnh Review ➝ Comments ➝ New Comment (E2003: Inser... thumbnail 1 summary
Chiêu thứ 29: Tùy biến chú thích của ô bảng tính

Khi chúng ta chèn chú thích cho ô bằng lệnh Review ➝ Comments ➝ New Comment (E2003: Insert ➝ Comment). Excel sẽ mặc định chèn tên người dùng của máy tính vào hộp chú thích. Bạn có thể thay đổi điều này bằng cách vào Office ➝ Excel Options ➝ Popular (E2003: Tools ➝ Options ➝ General). Bạn nhập lại đoạn văn bản mặc định mong muốn tại hộp User name:

224541

Mặc dù chú thích cho ô có mục đích chính là hiển thị các thông báo cho bạn hoặc cho người dùng khác, bạn có thể tùy biến nó nhằm mục đích làm rõ hơn chủ ý của bạn.

Trước tiên, bạn cần tùy biến thanh Quick Access Toolbar (QAT) để thêm một nút lệnh vào đó, các bước thực hiện như sau: Office ➝ Excel Options ➝ Customize. Tại hộp Choose commands from bạn chọn Smart Art Tools | Format Tab ➝ nhấn nút Add để thêm vào QAT ➝ nhấn OK hoàn tất (E2003: gọi thanh Drawing bằng cách View ➝ Toolbars ➝ Drawing).

224542
 Chèn một chú thích cho ô bằng cách vào ngăn Review ➝ tại nhóm Comments ➝ chọn New Comment (E2003: Insert ➝ Comment). Một hộp chú thích sẽ xuất hiện và bạn có thể nhập nội dung chú thích vào đó.

Để thay đổi hình dạng của hộp chú thích này bạn nhấp trái chuột lên khung viền của nó để thoát khỏi chế độ nhập liệu ➝ chọn lệnh Change AutoShape từ thanh QAT ➝ chọn kiểu hình dạng mong muốn trong các nhóm hình Basic Shapes, Block Arrow, Flow Chart, CalloutsStars and Banners ➝ sau khi chọn hình thì hình dạng hộp chú thích của ô sẽ thay đổi tức thì.

224543

Đối với phiên bản Excel 2007 thì việc hiệu ứng 3-D cho hộp chú thích được thiết lập mặc định và các tùy chọn Shadow Setting đã bị loại bỏ. Bạn có thể thay đổi các tùy chọn đổ bóng, hiệu ứng 3-D trong phiên bản Excel trước đó như sau: chọn khung viền của hộp chú thích ➝ vào thanh Drawing ➝ chọn nút lệnh Shadow Settings ➝ chọn kiểu mong muốn:

224544

Thêm ảnh vào chú thích

Một điều cũng khá thú vị là bạn có thể dùng các hộp chú thích để hiển thị các ảnh mà không làm ảnh hưởng đến các nội dung khác. Thí dụ như, bạn có thể chèn một đồ thị vào hộp chú thích nhằm minh họa tốt hơn cho các số liệu mà không cần phải lúc nào cũng hiện đồ thị lên.

Để thêm hình, bạn chọn ô đang có chú thích ➝ nhấp phải chuột lên ô ➝ chọn Edit Comment ➝ chọn khung viền của hộp chú thích ➝ nhấp phải chuột ➝ chọn Format Comment (E2003: nhấp chuột 2 lần liên tiếp lên khung viền của hộp chú thích) ➝ chọn ngăn Colors and Lines ➝ tại hộp Color, chọn Fill Effects ➝ chọn nút Select Picture ➝ chọn hình và nhấn Insert ➝ OK ➝ OK.

224545


 Trích xuất nội dung chú thích

Để trích xuất nội dung trong hộp chú thích, chúng ta cần viết một hàm người dùng đơn giản. Bạn nhấn tổ hợp ALT+F11 để vào cửa sổ VBE hoặc vào ngăn Developer ➝ Code ➝ Visual Basic (E2003: Tools ➝ Macro ➝ Visual Basic Editor), sau đó vào Insert ➝ chọn Module ➝ nhập vào đoạn mã sau:


Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function


Vào File ➝ Save (Ctrl+S) để lưu Module, sau đó vào File ➝ Close and Return to Microsoft Excel (ALT+Q) để trở về cửa sổ bảng tính. Bạn vào một ô trống nào đó và nhập vào:

=GetCommentText(B2)

Với B2 là ô đang có chú thích. Nhấn Enter để xem kết quả.
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

15/4/20

Trích xuất dữ liệu số trong 1 chuỗi bằng VBA

Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA ​ Bạn thường lấy dữ liệu từ nguồn ngoài, chẳng hạn từ Internet, trong đó có dữ liệu... thumbnail 1 summary
Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA

Bạn thường lấy dữ liệu từ nguồn ngoài, chẳng hạn từ Internet, trong đó có dữ liệu số xen lẫn chữ như: “1,254.00VND” hoặc “USD 2,500.00”, thậm chí còn phức tạp hơn.​
Và cũng có khi bạn đã nhập liệu hỗn hợp text và số không theo quy luật nào để có thể lấy riêng số ra bằng các hàm tách chuỗi thông thường.​
Dùng 1 hàm tự tạo viết bằng VBA, bạn có thể trích xuất riêng phần số ra, dù cho chuỗi có kiểu dạng gì đi nữa.​

14/4/20

Chuyển đổi con số dạng văn bản sang số thực

Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực Các giá trị số (number) trong Excel được mặc định canh lề phải và văn bản (text)... thumbnail 1 summary
Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực

Các giá trị số (number) trong Excel được mặc định canh lề phải và văn bản (text) thì canh lế trái. Do vậy, cách đơn giản để nhận biết các giá trị số và văn bản trong một cột trên bảng tính là bạn thiết lập chế độ canh lề mặc định cho cột đó. Bạn vào Home ➝ nhóm Alignment ➝ chọn Format Cells ➝ vào ngăn Alignment (E2003: Format ➝ Cells ➝ Alignment) ➝ chọn General tại hộp Horizontal để thiết lập việc canh lề mặc định cho cột đang chọn ➝ nhấn OK để đóng hộp thoại Format Cells lại.

13/4/20

Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn.

Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn. Khi nhập dữ liệu hay khi sao chép và dán dữ liệu từ ng... thumbnail 1 summary
Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn.


Khi nhập dữ liệu hay khi sao chép và dán dữ liệu từ nguồn khác vào Excel thì các ký tự không mong muốn sẽ xuất hiện trong toàn bộ bảng tính của bạn. Sử dụng chiêu này bạn có thể khắc phục được những rắc rối khi gỡ bỏ các ký tự không mong muốn bằng tay. Chức năng thay thế (replace) trong Excel có thể giúp bạn gỡ bỏ các ký tự không mong muốn trong bảng tính, nhưng phải qua một vài bước phụ.


Ví dụ như, bạn có thể thay thế những ký tự không mong muốn bằng chuỗi rỗng tựa như nó chưa hề tồn tại. Muốn vậy bạn cần biết mã của từng ký tự mà bạn muốn gỡ bỏ. Tất cả các ký tự đều mang một mã riêng và Excel sẽ cho bạn biết nó là gì khi bạn sử dụng hàm CODE.

Hàm CODE sẽ trả về một mã số cho ký tự đầu tiên trong một chuỗi. Mã này tương đương ký tự mà máy tính của bạn đã thiết lập.


Để thực hiện điều này, chọn một trong các ô có chứa những ký tự không mong muốn. Từ thanh công thức, bôi đen ký tự và sao chép ký tự đó. Tiếp theo chọn ô trống bất kỳ (A1 chẳng hạn) và dán ký tự đó vào ô đã chọn (A1).


Tại ô khác, nhập công thức sau:

=CODE($A$1)

Công thức này trả về mã của ký tự không mong muốn.


Chọn toàn bộ dữ liệu của bạn, chọn Home ➝ Editing ➝ Find & Select ➝ Replace (với phiên bản trước Excel 2007: chọn Edit ➝ Replace…), ở khung Find what: nhấn phim Alt và gõ số 0 kèm theo code đã đưa ra bởi công thức trên. Nếu mã số là 163 thì nhấn Alt và nhấn 0163. (Hoặc bạn có thể để con trỏ chuột tại ô có ký tự không mong muốn, sao chép ký tự đó và dán vào ô Find what cũng được)

Bỏ trống khung Replace With và nhấn Replace all. Việc làm này sẽ xóa bỏ tất cả những ký tự không mong muốn rất nhanh qua việc dò tìm mã ký tự. Lặp lại các bước ở trên cho mỗi ký tự không mong muốn tiếp theo.
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

11/4/20

Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác

Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác Trước tiên, chúng ta cần chuẩn bị một số dữ liệu ... thumbnail 1 summary
Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác

Trước tiên, chúng ta cần chuẩn bị một số dữ liệu để thực hành bài này. Bạn hãy tạo một sheet mới và đặt tên là List và tại ô A1 nhập vào tiêu đề “Objects”. Tại ô B1 nhập vào tiêu đề “Corresponding List”. Vùng A2:A5 bạn nhập vào từ “Can”. Vùng A2:A9 nhập vào từ “Sofa”. Vùng A10:A13 nhập vào từ “Shower”. Vùng A14:A17 nhập vào từ “Car”. Sau đó, trong vùng B2:B17 bạn nhập vào các từ sau: Tin, Steel, Opener, Lid, Bed, Seat, Lounge, Cushion, Rain, Hot, Cold, Warm, Trip, Journey, Bonnet, và Boot.

10/4/20

Dùng nhiều List cho 1 Combobox

Chiêu số 24: Dùng nhiều List cho 1 Combobox Excel cung cấp cho bạn những cách để chọn 1 thứ trong 1 danh sách để nhập liệu vào 1 ô. Trong ... thumbnail 1 summary
Chiêu số 24: Dùng nhiều List cho 1 Combobox

Excel cung cấp cho bạn những cách để chọn 1 thứ trong 1 danh sách để nhập liệu vào 1 ô. Trong đó có công cụ combobox, từ danh sách xổ xuống của combobox, bạn có thể chọn 1 ngày trong tuần, 1 tháng trong năm, hoặc 1 sản phẩm trong danh sách. Nếu bạn cần chọn lựa trong 3 danh sách khác nhau, bạn sẽ nghĩ rằng bạn cần 3 cái combobox.​


9/4/20

Bật, tắt chức năng Conditional Formatting bằng 1 checkbox

Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox Bạn thường dùng Data Validation để giới hạn dữ liệu nhập vào, hoặc ... thumbnail 1 summary
Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox

Bạn thường dùng Data Validation để giới hạn dữ liệu nhập vào, hoặc dùng Conditional Formatting để cảnh báo khi nhập dữ liệu trùng. Nhưng đôi khi bạn muốn tắt chúc năng cảnh báo của CF. Có 1 cách đơn giản để bật tắt chức năng này bằng cách sử dụng 1 checkbox. Xem thêm bài Chiêu số 18: Điều khiển Conditional Formating bằng checkbox.
Nhưng ở đây là CF tô màu dữ liệu nhập trùng.

Trong thí dụ dưới đây, bạn sẽ dùng CF để đánh dấu (tô màu) những ô có dữ liệu xuất hiện nhiều hơn 1 lần trong vùng dữ liệu. Giả sử trong vùng A1:A100 bạn đã thiết lập CF với công thức như sau:
=CountIf($A$1:$A$100,$A1)>1

Bây giờ bạn muốn tuỳ ý bật tắt cái CF này. Trước tiên bạn cùng công cu Form hoặc Control Toolbox, vẽ 1 cái Checkbox lên sheet. Thiết lập Property Cell Link cho cái checkbox này là 1 ô nào đó, thí dụ K1. Ta vào Name box đặt nuôn tên cho ô này là IsCheck.

Bây giờ click chọn vùng dữ liệu A1:A100 sao cho ô A1 là ô hiện hành. Thiết lập Conditional Formatting cho vùng này với công thức sau:
=AND(COUNTIF($A$1:$A$100,$A1)>1,IsCheck)
Nhấn nút Format để tô màu theo ý muốn, rồi OK.

Sau khi hoàn thành, khi bạn check vào cái checkbox, ô K1 sẽ thành True, và những ô có dữ liệu trùng sẽ bị tô màu.
Trái lại, khi bạn uncheck cái checkbox, ô K1 sẽ có giá trị False, và chẳng có ô nào bị tô màu nữa.

Cũng như chiêu số 18, bạn hoàn toàn có thể dùng cái toggle Button làm 1 cái công tắc bật tắt y như cái checkbox.

Chắc cũng cần nói lại cách mà công tắc này hoạt động:

Do công thức của bạn là AND(điều kiện 1, điều kiện 2), nên chỉ cần 1 trong 2 điều kiện không thoả (=False), nguyên đk chung sẽ False, CF không hoạt động; vì CF chỉ có tác dụng khi điều kiện chung là True.​
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

8/4/20

Tạo hiệu ứng 3D trong các bảng tính hay các ô

Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô Bất cứ khi nào bạn nhìn thấy một hiệu ứng 3D trong một chương trình hoặc một ứ... thumbnail 1 summary
Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô

Bất cứ khi nào bạn nhìn thấy một hiệu ứng 3D trong một chương trình hoặc một ứng dụng, chẳng hạn như Excel, ắt hẳn bạn sẽ thấy một sự đánh lừa qua thị giác được tạo ra bởi cách định dạng đặc biệt. Chính bạn cũng có thể tạo ra hiệu ứng này một cách dễ dàng bằng cách định dạng một ô hoặc một dãy các ô. Phiên bản Excel 2007 đã đưa vào các styles của ô, vì vậy bạn có thể tạo hiệu ứng 3D và lưu nó lại để sử dụng vào bất cứ khi nào bạn muốn.​

7/4/20

Tô màu dòng xen kẽ

Chiêu thứ 21: Tô màu dòng xen kẽ Ắt hẳn bạn đã từng thấy bảng tính Excel với những dòng tô màu cách nhau, thí dụ dòng chẵn thì tô màu xám, ... thumbnail 1 summary
Chiêu thứ 21: Tô màu dòng xen kẽ
Ắt hẳn bạn đã từng thấy bảng tính Excel với những dòng tô màu cách nhau, thí dụ dòng chẵn thì tô màu xám, dòng lẻ không tô hoặc tô màu khác.​
Trình bày bảng tính tô màu cách dòng như vậy khiến cho bảng tính có vẻ Pro và làm cho người dùng dễ đọc dữ liệu. Bạn có thể định dạng từ từ bằng tay từng dòng, nếu bạn đủ kiên nhẫn, nhưng bạn biết rồi đấy, kiên nhẫn 1 lần thì chưa đủ. Bạn sẽ phải bực mình khi cần xoá dòng, chèn dòng, cập nhật dữ liệu.​

6/4/20

Đếm hoặc cộng những ô đã được định dạng có điều kiện

Chiêu thứ 20: Đếm hoặc cộng những ô đã được định dạng có điều kiện Chúng ta thường hỏi: "Làm thế nào để tính toán với những ô đã được... thumbnail 1 summary
Chiêu thứ 20: Đếm hoặc cộng những ô đã được định dạng có điều kiện
Chúng ta thường hỏi: "Làm thế nào để tính toán với những ô đã được tô một màu cụ thể nào đó?" Câu hỏi này thường được nêu ra, bởi vì Excel không có một hàm bình thường nào để thưc hiện được nhiệm vụ này; tuy nhiên, nó có thể được thực hiện bằng một hàm tự tạo.

Vấn đề duy nhất xảy ra với việc sử dụng hàm tự tạo, là nó không thể lọc ra bất kỳ một loại định dạng nào đã được áp dụng bởi việc định dạng có điều kiện (conditional formatting). Tuy nhiên, suy nghĩ một tí, bạn vẫn có thể có được kết quả tương tự mà không phải cần đến một hàm tự tạo.

1/4/20

Đánh dấu những ô chứa công thức bằng Conditional Formatting

Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting Khi một ô có chứa dữ liệu, bạn có thể muốn biết dữ liệu trong ô ... thumbnail 1 summary

Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting


Khi một ô có chứa dữ liệu, bạn có thể muốn biết dữ liệu trong ô đơn thuần là dữ liệu nhập vào, hay dữ liệu là kết quả của 1 công thức. Bạn có thể chỉ cần click chọn ô đó và xem trên thanh công thức. Bạn cũng có thể dùng phím tắt Ctrl + ~ để chuyển qua lại giữa chế độ xem giá trị và xem công thức.

31/3/20

Điều khiển Conditional Formating bằng checkbox.

Chiêu số 18: Điều khiển Conditional Formating bằng checkbox. Mặc dù Conditional Formating là 1 trong những chiêu mạnh của Excel, nhưng mu... thumbnail 1 summary

Chiêu số 18: Điều khiển Conditional Formating bằng checkbox.


Mặc dù Conditional Formating là 1 trong những chiêu mạnh của Excel, nhưng muốn bật hay tắt nó bằng ribbon hay menu thì khá bực bội. Bây giờ ta biến hoá bằng cách điều khiển bằng 1 checkbox giống như 1 công tắc (hoặc 1 cái toggle Button càng giống hơn).​
Conditional Formating có từ đời Excel 97, gán định dạng cho những ô nào thoả 1 số điều kiện nào đó. Điều kiện có thể là 1 điều kiện về giá trị, nhưng ta có thể tuỳ biến nhiều hơn khi dùng điều kiện là công thức, dựa vào đó ta có thể thay đổi định dạng cho những ô này, khi có sự thay đổi giá trị của ô khác.​

Sử dụng Data-Validation khi danh sách nguồn nằm trong một Sheet khác

Chiêu thứ 17: Sử dụng Data-Validation khi danh sách nguồn nằm trong một Sheet khác Sử dụng Data-Validation là một cách dễ nhất để áp dụng m... thumbnail 1 summary
Chiêu thứ 17: Sử dụng Data-Validation khi danh sách nguồn nằm trong một Sheet khác
Sử dụng Data-Validation là một cách dễ nhất để áp dụng một quy tắc nhập liệu cho một dãy dữ liệu. Theo mặc định, Excel chỉ cho phép Data-Validation sử dụng những danh sách nguồn nằm trong cùng một Sheet với dãy dữ liệu sẽ được áp dụng quy tắc này. Tuy nhiên, vẫn có cách để lách khỏi chuyện đó.​

30/3/20

Cứu dữ liệu từ một bảng tính bị lỗi

Chiêu thứ 16: Cứu dữ liệu từ một bảng tính bị lỗi Khi bảng tính bị lỗi (corrupt), bị hư, nghĩa là bạn bị mất hết dữ liệu, mà nhiều khi sự m... thumbnail 1 summary
Chiêu thứ 16: Cứu dữ liệu từ một bảng tính bị lỗi
Khi bảng tính bị lỗi (corrupt), bị hư, nghĩa là bạn bị mất hết dữ liệu, mà nhiều khi sự mất mát này còn đau hơn là mất tiền. Chiêu này sẽ giúp bạn một vài cách để phục hồi lại phần nào dữ liệu trong những bảng tính bị lỗi.​

Giảm kích thước file Excel bị phình to bất thường

Chiêu thứ 15: Giảm kích thước file Excel bị phình to bất thường Bạn có bao giờ có 1 file Excel bị tăng kích thước đến mức báo động chả biết... thumbnail 1 summary
Chiêu thứ 15: Giảm kích thước file Excel bị phình to bất thường
Bạn có bao giờ có 1 file Excel bị tăng kích thước đến mức báo động chả biết nguyên nhân? Có nhiều lý do làm cho bảng tính bị phình lên về kích thước file và có những cách để loại trừ. Những bí quyết sau đây có thể giúp bạn nếu bạn có 1 file Excel có kích thước lớn bất thường.​
Có khi nào bạn bị bội thực do cố ăn mà chưa tiêu hoá kịp chưa? File Excel cũng thế. Nó bị phình ra là do bạn cố nhồi nhét đủ thứ vào bắt nó phải thực hiện, nó phải nuốt trọng chứ không kịp nhai, hậu quả là nó sẽ không làm việc như ý muốn.​

26/3/20

Gỡ bỏ những liên kết ma trong Excel

Bạn mở một bảng tính, nhận ngay một thông báo: " Update links ", nhưng chẳng có link nào! Làm thế nào để Update một link khi nó k... thumbnail 1 summary

Bạn mở một bảng tính, nhận ngay một thông báo: "Update links", nhưng chẳng có link nào! Làm thế nào để Update một link khi nó không tồn tại?


Những liên kết ngoài (external links) là những liên kết tham chiếu đến một bảng tính khác, chúng có thể nằm trong các công thức, trong các Name, hoặc trong các đối tượng được chèn vào (các đồ thị, các hình...). Khi những liên kết này bị "gãy", phần lớn lý do là do việc di chuyển hoặc sao chép một Sheet đến một bảng tính khác. Và khi đó, chúng trở thành những "liên kết ma", nghĩa là thấy đường link, nhưng không biết chúng được dẫn đến từ đâu. Sau đây là một vài cách giúp bạn xử lý những liên kết ma này.

Sao chép công thức giữ nguyên tham chiếu tương đối

Sao chép công thức giữ nguyên tham chiếu tương đối (giống như sao chép tham chiếu tuyệt đối) Trong Excel, 1 công thức có thể chứa những t... thumbnail 1 summary
Sao chép công thức giữ nguyên tham chiếu tương đối (giống như sao chép tham chiếu tuyệt đối)


Trong Excel, 1 công thức có thể chứa những tham chiếu tương đối hoặc tuyệt đối. Khi 1 công thức có tham chiếu tương đối được sao chép, thì do tính chất, tham chiếu sẽ chạy theo đến chỗ mới. Chỉ có công thức có tham chiếu tuyệt đối mới giữ nguyên khi sao chép.

25/3/20

Tạo riêng một thanh công cụ cho riêng một bảng tính Excel cụ thể

Chiêu số 12: Bạn đã từng tạo ra thanh công cụ riêng cho mình? Bạn có thấy rằng, thanh công cụ này luôn luôn được nạp vào Excel, và luôn hiể... thumbnail 1 summary
Chiêu số 12:
Bạn đã từng tạo ra thanh công cụ riêng cho mình? Bạn có thấy rằng, thanh công cụ này luôn luôn được nạp vào Excel, và luôn hiển thị, với bất kỳ bảng tính nào, với bất kỳ người sử dụng nào? Có bao giờ, bạn muốn rằng, thanh công cụ tự tạo này chỉ được hiển thị với một bảng tính cụ thể nào đó mà thôi không?​

Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating

Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating Dù cho công cụ Conditional Formatin g đã cải tiến r... thumbnail 1 summary
Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating

Dù cho công cụ Conditional Formating đã cải tiến rất nhiều trong Excel 2007, giúp chúng ta tìm những dữ liệu trùng (Duplicate), nhưng nó vẫn chưa cung cấp những tính năng tìm ra những dữ liệu xuất hiện 2 lần hoặc nhiều hơn.

24/3/20

Sử dụng định dạng theo điều kiện để tìm dữ liệu trùng trong Excel

 (Người dịch: TTPhong2007) Định dạng theo điều kiện của Excel thường được sử dụng để xác định giá trị trong phạm vi cụ thể trên bảng tính, ... thumbnail 1 summary
 (Người dịch: TTPhong2007)
Định dạng theo điều kiện của Excel thường được sử dụng để xác định giá trị trong phạm vi cụ thể trên bảng tính, nhưng chúng ta có thể cải tiến nó để nhận dạng sự trùng lắp dữ liệu trong một danh sách hoặc một bảng. Chức năng định dạng theo điều kiện trong Excel 2007 đã được cải tiến, do vậy bài hướng dẫn này thích hợp cho các phiên bản từ Excel 2003 về trước. ​

23/3/20

Khóa và bảo vệ những ô có chứa công thức trong Excel

Chiêu thứ 9: Khóa và bảo vệ những ô có chứa công thức Chiêu này giúp bạn cho phép người khác thay đổi các ô có chứa dữ liệu, nhưng cấm họ ... thumbnail 1 summary
Chiêu thứ 9: Khóa và bảo vệ những ô có chứa công thức

Chiêu này giúp bạn cho phép người khác thay đổi các ô có chứa dữ liệu, nhưng cấm họ thay đổi các ô chứa công thức. Bạn cũng có thể bảo vệ các ô có chứa công thức mà không cần phải bảo vệ toàn bộ trang tính của bạn.

Khi tạo một bảng tính, thường chúng ta sẽ phải dùng đến một số công thức, và khi chia sẻ bảng tính cho mọi người, có thể bạn muốn rằng, không ai có thể can thiệp (xóa, sửa...) những vào những ô có chứa công thức. Cách dễ nhất là cũng phổ biến nhất là Protect (bảo vệ) bảng tính. Tuy nhiên, Protect bảng tính không chỉ ngăn không cho can thiệp vào các ô chứa công thức, mà nó không cho can thiệp vào tất cả, nghĩa là không ai có thể làm gì bảng tính của bạn. Đôi khi, bạn lại không muốn như vậy, bạn chỉ muốn bảo vệ các ô chứa công thức thôi, còn những ô chứa dữ liệu thì không.​


Giới hạn vùng cuộn của bảng tính trong Excel

Chiêu thứ 8: Giới hạn vùng cuộn của bảng tính Nếu bạn không muốn thanh cuộn kéo bảng tính lên xuống hoặc qua phải nhiều, hoặc bạn có dữ li... thumbnail 1 summary
Chiêu thứ 8: Giới hạn vùng cuộn của bảng tính

Nếu bạn không muốn thanh cuộn kéo bảng tính lên xuống hoặc qua phải nhiều, hoặc bạn có dữ liệu ở 1 vùng nào đó mà không muốncho nguời dùng xem, bạn có thể giới hạn vùng nhìn được của bảng tính trong phạm vi bạn cho phép.
Các bảng tính tạo bởi Excel 2007 có số cột tối đa 16.384, Excel trước đó là 256 (từ A đến IV), và có số dòng tối đa là 1.048.576 (trước đó là 65.536). Nhưng thường thì bạn hiếm khi sử dụng hết. Bạn có thể giới hạn chỉ cho người dùng xem trong 1 phạm vi nào đó, còn dữ liệu nhạy cảm, bạn để ngoài vùng đó. Ngoài ra nó còn hạn chế khi ai đó vô tình kéo thanh cuộn xuống dòng 500.000 và đến lượt người khác la làng rằng kéo mãi chẳng thấy cái gì mà xem.
Bạn có thể dùng cách đơn giản là dấu (Hide) những dòng và cột muốn dấu hoặc bằng cách định nghĩa 1 vùng cho phép xem hoặc chỉ kích hoạt vùng có dữ liệu.

Tạo chỉ mục cho các Sheet trong Workbook Excel

Chiêu thứ 7: Tạo chỉ mục cho các Sheet trong Workbook Nếu bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rất nhiều work... thumbnail 1 summary
Chiêu thứ 7: Tạo chỉ mục cho các Sheet trong Workbook

Nếu bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rất nhiều worksheet (trang tính), bạn sẽ cảm thông được sự khó khăn trong việc tìm kiếm một trang tính nào đó. Khi đó, có lẽ ta nên lập một chỉ mục các trang tính đang có để thuận tiện cho việc điều hướng trong bảng tính.

21/3/20

Tự thiết kế một bảng tính mẫu (template) trong Excel

Nếu như bạn thường xuyên phải thực hiện cùng một kiểu trình bày cho bảng tính của mình, bạn có thể tự thiết kế ra một bảng tính mẫu cho riê... thumbnail 1 summary

Nếu như bạn thường xuyên phải thực hiện cùng một kiểu trình bày cho bảng tính của mình, bạn có thể tự thiết kế ra một bảng tính mẫu cho riêng bạn, là một bảng tính đã có sẵn tiêu đề, một số cột, một số hàng nào đó, thậm chí là cả Name và công thức; đồng thời còn có thể tạo ra một hộp thoại riêng vào trong Thư viện các Templates của Excel.

Ẩn sheet sao cho người dùng không thể dùng lệnh unhide để hiện ra

Đôi khi bạn có những thông tin trên 1 sheet nào đó và bạn không muốn người dùng đọc được. Bạn muốn có 1 khu vực (có thể là 1 sheet) ghi chép... thumbnail 1 summary
Đôi khi bạn có những thông tin trên 1 sheet nào đó và bạn không muốn người dùng đọc được. Bạn muốn có 1 khu vực (có thể là 1 sheet) ghi chép những dữ liệu, công thức, những chi tiết vụn vặt và không muốn cho ai nhìn thấy.

Ngăn chặn các nhắc nhở không cần thiết trong excel

Exel luôn luôn cảnh báo hoặc nhắc nhở bạn xác nhận các hành động và thao tác của bạn. Bài này sẽ hướng dẫn bạn cách loại bỏ các thông báo ho... thumbnail 1 summary
Exel luôn luôn cảnh báo hoặc nhắc nhở bạn xác nhận các hành động và thao tác của bạn. Bài này sẽ hướng dẫn bạn cách loại bỏ các thông báo hoặc nhắc nhở.

Các kiểu cảnh báo mà tôi đề cập ở đây chính là các câu hỏi mà Excel hỏi bạn có cho macro hoạt động hay không hoặc là các thông báo về các hành động xóa một trang bảng tính.

Ngăn chận người sử dụng thực hiện một số hành động nào đó nhất định trong excel

Mặc dù Excel có hỗ trợ việc bảo vệ cho bảng tính (Protect Sheet, Protect Workbook), nhưng công cụ thô sơ này không bao gồm việc chỉ cho phép... thumbnail 1 summary
Mặc dù Excel có hỗ trợ việc bảo vệ cho bảng tính (Protect Sheet, Protect Workbook), nhưng công cụ thô sơ này không bao gồm việc chỉ cho phép người sử dụng một số đặc quyền hạn chế - trừ khi bạn làm theo những chiêu sau đây.​



Bạn có thể quản lý việc tương tác với các bảng tính của bạn bằng cách giám sát và đáp ứng các sự kiện. Các sự kiện (event), là những hành động xảy ra khi bạn làm việc với các bảng tính hoặc các Sheet. Những sự kiện này thường bao gồm việc mở (open) một bảng tính, lưu (save) nó, và đóng (close) nó. Bạn có thể bảo Excel chạy những mã Visual Basic (macro) một cách tự động khi xảy ra bất kỳ một trong những sự kiện như thế.​


20/3/20

Nhập dữ liệu đồng thời vào nhiều sheet.

Thông thường người dùng Excel có những dữ liệu muốn nhập vào nhiều sheet khác nhau. Bạn có thể dùng công cụ Group các sheet lại để có thể ... thumbnail 1 summary


Thông thường người dùng Excel có những dữ liệu muốn nhập vào nhiều sheet khác nhau. Bạn có thể dùng công cụ Group các sheet lại để có thể nhập dữ liệu đồng thời. Nhưng cũng có 1 cách khác nhanh chóng và linh hoạt hơn nếu bạn dùng vài dòng lệnh VBA.
Nhưng phải nói đến công cụ Group cái đã nhỉ? Công cụ Group dùng để nhập dữ liệu vào nhiều sheet 1 lúc chắc có nhiều người chưa dùng đến. Nó dùng để kết nối các sheets với nhau trong phạm vi work book.

1. Group các sheet lại bằng tay:

Để nhóm các sheet lại, các bạn chỉ việc click chọn sheet thứ nhất, nơi bạn sẽ nhập liệu từ bàn phím vào, rồi vừa nhấn Ctrl, vừa nhấn vào tên những sheet khác mà bạn muốn nhập liệu đồng thời. Khi bạn gõ bất kỳ cái gì vào sheet hiện hành, dữ liệu đó cũng sẽ tự động nhập vào các sheet còn lại trong nhóm. Điệp vụ hoàn tất nhanh chóng (nghe giống 007 nhỉ).

Tùy biến cửa sổ làm việc của bảng tính

Excel cho phép hiển thị đồng thời nhiều bảng tính và cho phép tùy biến cách hiển thị và sắp xếp chúng. Sau khi thiết lập các tùy biến, chúng... thumbnail 1 summary
Excel cho phép hiển thị đồng thời nhiều bảng tính và cho phép tùy biến cách hiển thị và sắp xếp chúng. Sau khi thiết lập các tùy biến, chúng ta có thể lưu thành một tập tin workspace (không gian làm việc) .xlw để sử dụng lại sau này.

Những "tuyệt chiêu" trong Excel-P3-Những mẹo nhỏ khi dùng công thức

Một sai lầm rất lớn khi sử dụng công thức là cho nó  tham chiếu đến toàn bộ các cột trong mảng dữ liệu . Điều này làm cho Excel phải kiểm ... thumbnail 1 summary

Một sai lầm rất lớn khi sử dụng công thức là cho nó tham chiếu đến toàn bộ các cột trong mảng dữ liệu. Điều này làm cho Excel phải kiểm tra hàng ngàn ô, nếu không nói là hàng triệu, để chắc chắn không bỏ sót một dữ liệu nào.


Những tuyệt chiêu Excel -P2- Những mẹo nhỏ khi định dạng

Những mẹo nhỏ khi định dạng Ngoài việc thiết kế cấu trúc của bảng tính cho hợp lý, việc định dạng cho nó cũng là một vấn đề cần bàn đến. ... thumbnail 1 summary

Những mẹo nhỏ khi định dạng
Ngoài việc thiết kế cấu trúc của bảng tính cho hợp lý, việc định dạng cho nó cũng là một vấn đề cần bàn đến. Mặc dù một bảng tính nên được định dạng sao cho dễ đọc và dễ theo dõi, nhưng chúng ta ít khi nghĩ đến việc sử dụng thời gian cho có hiệu quả. Hãy luôn luôn định dạng thật đơn giản. Rất nhiều người lãng phí thời gian vào việc định dạng một bảng tính mặc dù không nhất thiết phải làm như vậy, và chính điều này làm ảnh hưởng đến hiệu quả công việc. Việc thường xuyên áp dụng những định dạng phức tạp cho bảng tính còn làm cho nó tăng kích thước, và cho dù bảng tính của bạn có thể trông giống như một tác phẩm nghệ thuật, nhưng nó lại có thể là một nỗi sợ hãi cho người khác. Một trong những kiểu phối màu tốt nhất mà bạn nên áp dụng cho một bảng tính, là màu đen, màu trắng và màu xám.


Những tuyệt chiêu trong Excel -P1 -Lời nói đầu

Trong quá trình thực hiện một bảng tính, chúng ta có thể mắc phải vô số lỗi, hoặc có thể mắc phải những sai lầm ngớ ngẩn, và thường không để... thumbnail 1 summary
Trong quá trình thực hiện một bảng tính, chúng ta có thể mắc phải vô số lỗi, hoặc có thể mắc phải những sai lầm ngớ ngẩn, và thường không để ý tới những cái có thể giúp chúng ta xử lý bảng tính nhanh hơn, gọn gàng hơn... Xin hân hạnh giới thiệu đến các bạn cuốn sách "Một ngày một tuyệt chiêu", lược dịch từ cuốn Excel Hacks, của hai tác giả David và Raina Hawley. Những "tuyệt chiêu" này sẽ giúp bạn tránh được những lỗi, và làm việc với bảng tính một cách có hiệu quả hơn.

17/3/20

8 Lỗi Excel làm bạn cảm thấy khó chịu

Bạn có để ý đến mũi tên nhỏ màu xanh lá cây ở góc trên bên trái của một ô không? Bạn biết đấy, dấu hiệu nhỏ phiền phức này được Excel sử dụn... thumbnail 1 summary


Bạn có để ý đến mũi tên nhỏ màu xanh lá cây ở góc trên bên trái của một ô không? Bạn biết đấy, dấu hiệu nhỏ phiền phức này được Excel sử dụng để chỉ ra có điều gì đó không đúng với những giá trị trong ô.

16/3/20

Tài liệu Hướng dẫn sử dụng công cụ Pivot Table

Mục lục 1. ĐẶT VẤN ĐỀ.  ...................................................................................................................... thumbnail 1 summary
Mục lục
1. ĐẶT VẤN ĐỀ.  .........................................................................................................................  2
2. TỔNG QUAN VỀ CƠ SỞ DỮ LIỆU.  ......................................................................................  2
2.1. Khái niệm cơ sở dữ liệu.  ....................................................................................................  2
2.2. Đặc điểm của Cơ sở dữ liệu.  ..............................................................................................  2
2.3. Những chú ý khi xây dựng Cơ sở dữ liệu trên file Excel. .................................................  2
3. PIVOT TABLE.  ........................................................................................................................  3
3.1. Khái niệm. .........................................................................................................................  3
3.2. Cơ chế xử lý của Pivot Table  .............................................................................................  6
3.3. Những thao tác cơ bản với Pivot Table. ............................................................................  9
3.3.1. Kéo thả trường để tiến hành phân tích, tổng hợp.  .......................................................  9
3.3.2. Thay đổi kiểu hiển thị thống kê  ................................................................................  10
3.3.3. Chỉnh sửa SubTotals, GrandTotals  ...........................................................................  10
3.3.3. Thay đổi vùng dữ liệu của Pivot Table  .....................................................................  12
3.3.4. Thay đổi phương thức thống kê  ................................................................................  12
3.3.5. Tổng hợp dữ liệu từ nhiều nguồn..............................................................................  14
Bấm Get Data để lựa chọn kiểu CSDL:  ......................................................................................  15
4. ỨNG DỤNG PIVOT TABLE LẬP BÁO CÁO XUẤT NHẬP TỒN.  ...................................  17
4.1. Dữ liệu mẫu và Form báo cáo xuất nhập tồn  ...................................................................  17
4.2. Cách thực hiện.  ................................................................................................................  17
5. KẾT LUẬN ............................................................................................................................  20

DOWNLOAD
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

4/3/20

Addin Excel viết bởi Thủ Thuật Access

Hiện tại mình đang tổng hợp các hàm tự viết thường dùng vào 1 addins, Bạn nào thấy có hàm cần dùng thì tải về cài vô. Tải file bên dưới về... thumbnail 1 summary
Hiện tại mình đang tổng hợp các hàm tự viết thường dùng vào 1 addins,
Bạn nào thấy có hàm cần dùng thì tải về cài vô.
Tải file bên dưới về giải nén được 2 file, file sample.xlsx là ví dụ và cách phát biểu, file TTACExcelAddin.xla là addins

3/3/20

[Giáo Trình]Phân tích Thống kê bằng Excel

Tác giả Nguyễn Ngọc Anh Nguyễn Đình Chúc Đoàn Quang Hưng MỤC LỤC 1  GIỚI THIỆU ................................................................ thumbnail 1 summary
Tác giả
Nguyễn Ngọc Anh
Nguyễn Đình Chúc
Đoàn Quang Hưng

MỤC LỤC
1  GIỚI THIỆU ................................................................................................................ 4 
2  NHẬP DỮLIỆU ......................................................................................................... 5 
3  BỘCÔNG CỤDATA ANALYSIS TOOLPACT ...................................................... 5 
4  THỐNG KÊ MÔ TẢ................................................................................................... 6 
5  PHÂN PHỐI CHUẨN* ............................................................................................... 8 
6  XÂY DỰNG KHOẢNG TIN CẬY CHO TRUNG BÌNH TỔNG THỂ.................. 12 
6.1  Khi qui mô của mẫu thống kê lớn (n lớn hơn 30) .............................................. 12 
6.2  Mẫu nhỏ(ít hơn 30 quan sát) ............................................................................. 15 
7  KIỂM ĐỊNH GIẢTHUYẾT VỀTRUNG BÌNH TỔNG THỂ................................ 16 
8  KIỂM ĐỊNH SỰKHÁC BIỆT GIỮA HAI TRUNG BÌNH TỔNG THỂ................ 18 
8.1  Mẫu lớn: ............................................................................................................. 18 
8.2  Mẫu nhỏ: Một trong hai mẫu có sốlượng các quan sát nhỏhơn 30. ................. 22 
9  TƯƠNG QUAN TUYẾN TÍNH VÀ PHÂN TÍCH HỒI QUI* ................................ 26 
9.1  Phân tích tương quan tuyến tính ......................................................................... 27 
9.2  Phân tích hồi qui ................................................................................................. 29 

DOWNLOAD
===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

2/3/20

Thủ thuật chèn hình ảnh trực tuyến vào Excel bằng URL

XIn giới thiệu cách chèn hình ảnh trực tuyến vào Excel thông qua URL bằng code VBA. Hỗ trợ các bạn trong việc lên danh sách, thống kê đơn hà... thumbnail 1 summary

XIn giới thiệu cách chèn hình ảnh trực tuyến vào Excel thông qua URL bằng code VBA. Hỗ trợ các bạn trong việc lên danh sách, thống kê đơn hàng, hay quản lý websile có hình ảnh minh hoạ được dễ dàng và nhanh chóng hơn.

29/2/20

sửa lỗi: "This document contains macros......cannot be removed by the Document Inspector"

Bỗng 1 ngày bạn phá Excel gì đó và lưu lại nó báo lỗi như sau: "Privacy warning: This document contains macros, ActiveX controls, XML ... thumbnail 1 summary
Bỗng 1 ngày bạn phá Excel gì đó và lưu lại nó báo lỗi như sau:
"Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector"

Đừng lo lắng, hãy làm theo hướng dẫn sau:
- Vào Excel Options
- Bấm mục Trust Center, bấm tiếp nút Trust Center Settings
- Chọn mục Privacy Options
- Xem mục "Remove personal information from file properties on save" có đang check không? Nếu có, hãy bỏ dấu check rồi save file.

Các bạn cũng lưu ý nếu file của bạn thực sự chứa macro hay code VBA thì lưu file dưới định dạng XLSM nhé, .XLSX không hỗ trợ macro và VBA

===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

Hàm FORMULATEXT - Hàm trả về công thức dạng chuỗi

Hàm FORMULATEXT: Trả về công thức ở dạng chuỗi. Chỉ áp dụng cho Excel từ bản Office 2013 trở lên. Cú pháp: FORMULATEXT( reference ) Tron... thumbnail 1 summary
Hàm FORMULATEXT: Trả về công thức ở dạng chuỗi.
Chỉ áp dụng cho Excel từ bản Office 2013 trở lên.

Cú pháp:
FORMULATEXT(reference)
Trong đó: Reference    Bắt buộc. Tham chiếu đến một ô hoặc phạm vi ô

Chú thích
  • Hàm FORMULATEXT trả về những gì được hiển thị trong thanh công thức nếu bạn chọn ô được tham chiếu.
  • Đối số Reference có thể là tham chiếu đến trang tính hay sổ làm việc khác.
  • Nếu đối số Reference là tham chiếu đến sổ làm việc khác đang không mở, hàm FORMULATEXT trả về giá trị lỗi #N/A.
  • Nếu đối số Reference là tham chiếu đến toàn bộ một hàng hoặc cột, hoặc đến phạm vi hay tên đã xác định chứa nhiều ô, thì hàm FORMULATEXT sẽ trả về giá trị trong ô ngoài cùng phía trên bên trái của hàng, cột hoặc phạm vi đó.
Trong các trường hợp sau, hàm FORMULATEXT trả về giá trị lỗi #N/A:
  • Ô được dùng làm đối số Reference không chứa công thức.
  • Công thức trong ô dài hơn 8192 ký tự.
  • Công thức không thể được hiển thị trong trang tính do chế độ bảo vệ trang tính.
  • Sổ làm việc bên ngoài chứa công thức không mở trong Excel.
  • Kiểu dữ liệu không hợp lệ được dùng làm đầu vào sẽ tạo ra giá trị lỗi #VALUE! .

Đối với Office 2010 trở về trước, các bạn có thể tạo hàm tương tự bằng cách, nhấn alt+F11, tạo 1 module và dán nội dung như sau để dùng được hàm này:
Function FORMULATEXT(r As Range)
FORMULATEXT = r.Formula
End Function


===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

27/2/20

Cải thiện tốc độ xử lý công việc với Excel

Dân văn phòng nếu sử dụng thành thạo các hàm thống kê, tìm kiếm, trích lọc… sẽ nhập, tính toán, phân tích dữ liệu nhanh hơn. Từng xử lý thôn... thumbnail 1 summary


Dân văn phòng nếu sử dụng thành thạo các hàm thống kê, tìm kiếm, trích lọc… sẽ nhập, tính toán, phân tích dữ liệu nhanh hơn.

Từng xử lý thông tin khách hàng đến vài MB nhưng đến hàm nào cần dùng phải tra Google tìm hiểu làm chậm tiến độ công việc, chị Cẩm Nhung (quận 8) nhận định, không giỏi Excel là bất lợi.

26/2/20

Tự Tạo hàm thay thế nhiều kết quả cách nhau bằng dấu chấm phẩy bằng chuỗi tìm kiếm được trong bảng

Tự Tạo hàm thay thế nhiều kết quả cách nhau bằng dấu chấm phẩy bằng chuỗi tìm kiếm được trong bảng Hàm : MultiLookup : thay thế nhiều kết ... thumbnail 1 summary
Tự Tạo hàm thay thế nhiều kết quả cách nhau bằng dấu chấm phẩy bằng chuỗi tìm kiếm được trong bảng

Hàm : MultiLookup : thay thế nhiều kết quả cách nhau bằng dấu chấm phẩy bằng chuỗi tìm kiếm được trong bảng
Phát Biểu
=MultiLookup(chuỗi có bao gồm nhiều giá trị cách nhau dấu chấm phẩy ;Bảng tìm kiếm)

Đề bài như sau:

Ta có 1 bảng tìm kiếm như sau:

Và 1 chuỗi như sau
A;C;E;F

Mục tiêu của hàm là trả về kết quả sau:

Thiếu máu;Viêm phế quản;Sâu răng;Cận


Thực hiện:
Nhấn alt+F11 tạo 1 module và dán đoạn code sau vào


Function MultiLookup(s As String, R As Range)
' s bao gom nhieu chuoi cach nhau bang dau cham phay
'R laf bang can tim kiem
s1 = Split(s, ";")
For Each s2 In s1
    s = WorksheetFunction.Substitute(s, s2, WorksheetFunction.VLookup(s2, R, 2, 0))
Next
MultiLookup = s
End Function

Sử dụng hàm:
=MultiLookup(chuỗi có bao gồm nhiều giá trị cách nhau dấu chấm phẩy ;Bảng tìm kiếm)



Ví dụ
=MultiLookup(G2;B2:C8)



Kết quả

Thiếu máu;Viêm phế quản;Sâu răng;Cận

Xem file mẫu
https://www.dropbox.com/s/8gkmibgxwzntsxt/Multilookup.xlsm?dl=0

===================================================
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html

15/2/20

Sử dụng hàm SUMPRODUCT và Công thức mảng

Hàm SumProduct: Cấu trúc SUMPRODUCT(array1,array2,array3, ...) Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng n... thumbnail 1 summary
Hàm SumProduct:
Cấu trúc SUMPRODUCT(array1,array2,array3, ...)
Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,...