Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Ketoan.biz 18.7.19 2 Comments

Ketoan.biz

Tính năng Protect sheet trong bảng tính excel cho phép bạn bảo vệ, khóa, không cho người khác sửa đổi các ô chứa công thức hay vùng dữ liệu nhạy cảm khá dễ dàng.

Protect sheet bảo vệ bảng tính, chống sửa đổi các ô vùng chọn bằng thiết lập mật khẩu. Tuy nhiên, nếu không may bạn quên mật khẩu, hay bạn cần Unprotect sheet bảng tính khác mà không có mật khẩu thì làm thế nào?

Bài viết sau sẽ trình bày cách khóa một bảng tính, khóa một vùng chọn của bảng tính và cách 'phá pass' Unprotect sheet mà không cần phải dùng phần mềm can thiệp.

Cách bảo vệ bảng tính bằng Protect sheet

Nếu khóa toàn bộ bảng tính trừ vùng chọn: đầu tiên bạn cần chọn (bôi đen) vùng chọn không bị khóa (người dùng khác có thể chỉnh sửa dữ liệu), mở tab Protection trong Format Cells (nhấp phải chuột/Format Cells/Protection):

Nhấp bỏ chọn dấu stick trong mục Locked, nhấp OK.

Nếu chỉ khóa vùng chọn: Bạn chọn toàn bộ bảng tính (nhấn tổ hợp phím Ctr + A) rồi thao tác các bước như trên.

Kế tiếp, bạn chọn vùng dữ liệu cần khóa, cũng thao thao tác tương tự như trên nhưng chọn lại dấu kiểm Locked trong tab Protection của Format Cells trước khi nhấn OK.

Nếu khóa toàn bộ bảng tính: bạn không cần phải thực hiện các bước trên vì Excel mặc định là Locked.

Tiếp theo, bạn nhấp Tab Review, chọn Protect sheet:

Menu Protect sheet xuất hiện như sau:

Trong phần Allow all uers of this worksheet to bạn chọn các tính năng cần khóa, xong nhập mật khẩu vào ô Password to unprotect sheet. Nhấp OK và lập lại mật khẩu lần nữa khi được nhắc.

Lúc này phần chọn của sheet của bạn đã bị khóa, khi nhấp chuột (hoặc nhấn F2) để sửa đổi sẽ không thực hiện được và excel hiện ra một thông báo "xám xịt" về tình trạng của sheet (sheet bị khóa, cần pass để mở).

Mở khóa bảng tính (Unprotect sheet)

Nếu bạn có mật khẩu để mở khóa vùng chọn bị khóa (Unprotect sheet) thì mọi việc quá dễ dàng, chỉ việc thao tác: nhấp Tab Review, chọn Unprotect sheet nhập mật khẩu vào hộp Unprotect sheet khi nó xuất hiện và nhấn OK là xong.

Tuy nhiên, nếu chẳng may bạn quên mật khẩu hoặc cần cập nhật thông tin trên sheet bị khóa của một file excel nào đó mà không pass thì phải xử lý thế nào?

May mắn là chúng ta vẫn có thể xử lý được tình huống này mà không cần phải dùng phần mềm của bên thứ 3 can thiệp, đó là sử dụng Visual Basic Editor.

Các bước thao tác như sau:

Đầu tiên, bạn cần mở file excel bị khóa lên, vào Visual Basic Editor bằng cách nhấn phím tắt ALT + F11. Bạn click chọn vào sheet cần mở khóa:

(Trong hình trên là flie phantichcongno_mau, sheet2 (tonghop))

Tiếp theo, bạn copy đọan code sau dán vào phần General để phá pass cho sheet cần mở khóa:

Sub PasswordBreaker()

If ActiveSheet.ProtectContents = False Then

    MsgBox "Sheet '" & ActiveSheet.Name & "' is unprotected!", vbInformation

Else

    If MsgBox("Sheet '" & ActiveSheet.Name & "' is protected, do you want to unprotect it?", _

    vbYesNo + vbQuestion, "Unprotect Active Sheet") = vbNo Then Exit Sub

    Dim i As Integer, j As Integer, k As Integer

    Dim l As Integer, m As Integer, n As Integer

    Dim i1 As Integer, i2 As Integer, i3 As Integer

    Dim i4 As Integer, i5 As Integer, i6 As Integer

    On Error Resume Next

    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

        ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

        Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

        Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

    Next: Next: Next: Next: Next: Next

    Next: Next: Next: Next: Next: Next

    If ActiveSheet.ProtectContents = False Then MsgBox "Sheet '" & ActiveSheet.Name & "' is unprotected!", vbInformation

End If

End Sub

Truy cập vào menu Run → Run Sub/UserForm (hoặc nhấn F5) để chạy lệnh trên, nhấn Yes:

Nếu thành công, bạn cần xác nhận lại lệnh trên, nhấn OK để hoàn tất.

Như vậy, pass Protect sheet đã được gở bỏ, bạn hoàn toàn thao tác trên bảng tính này một cách bình thường.

Chúc các bạn thành công.

Ketoan.biz

Canh đều, ngắt dòng trong Excel như thế nào?

Ketoan.biz 5.12.18 Add Comment

Ketoan.biz

Như chúng ta biết, mặc định Excel sẽ tràn sang ô khác hoặc bị ẩn khi chúng ta gõ text tương đối dài, khá khó chịu, không đẹp mắt cho lắm.

Chèn nhanh bảng tính excel vào file word

Ketoan.biz 4.6.18 Add Comment

Ketoan.biz

Word và Excel là "anh em song sinh" tích hợp trong bộ Microsoft Office nên chúng có sự tương tác, hỗ trợ qua lại lẫn nhau khá tốt.

Chúng ta biết rằng bản thân word chuyên về soạn thảo văn bản, nên việc thực hiện các phép tính đơn thuần trên word khá khó khăn. Trong khi đó, excel lại mạnh về tính toán, có thể thực hiện nhiều phép tính từ đơn giản đến phức tạp thông qua các hàm, công thức.

>> Cập nhật tỷ giá tự động vào bảng tính excel

Liệu có cách nào khi chúng ta đang soạn thảo văn bản trên word, cần nhu cầu tính toán thì có thực hiện được các phép tính như trên bảng tính excel- vốn rất quen thuộc hay không? Câu trả lời là hoàn toàn có thể khi bạn thực hiện chèn bảng tính excel vào word với một vài thao tác khá đơn giản.

Bài viết sau sẽ được trình bày trên Office 2016 theo 2 cách: Chèn cả bẳng tính excel vào word bằng Object và chèn một phần bảng bảng tính excel vào word bằng Paste Special.

1. Chèn file excel vào word bằng Object

Đầu tiên bạn mở file word cần chèn bảng tính excel lên. Tại menu Insert, đặt trỏ chuột tại vị trí muốn chèn bảng tính, click chọn mũi tên trỏ xuống của tab Object, chọn Object ...

Hộp thoại Object xuất hiện:

Tại đây, chúng ta có hai lựa chọn: Create New nếu muốn chèn bảng tính mới (trắng), Create from File nếu muốn chèn bảng tính từ một file có sẵn.

Trong hình trên chúng ta đang chọn  Create New, và chọn Microsoft Excel 97-2003 Worksheet. Xong, nhấn OK để chèn bảng tính vào word.

Nếu bạn cần chèn file excel có sẵn vào word, hãy chọn Create from File, như hình sau:

Tại ô File name gõ đường dẫn chứa file hoặc click chọn Browse để tìm nơi chứa file excel cần chèn vào word, nhấn Insert hoặc Open. Xong nhấn OK.

Ở chế độ soạn thảo văn bản word (như trên), bạn có thể điều chỉnh khối bảng tính khi rê chuột vào các 6 nút chấm, kéo thả để tăng/ giảm kích thước khối.

Muốn bảng tính chuyển sang chế độ excel, bạn nhấp đúp chuột trái lên bảng tính. Lúc này, bạn có thể thao tác bảng tính như trên excel, như hình sau:

Muốn trở về chế độ soạn thảo văn bản của word, bạn chỉ cần nhấp chuột ngoài vùng bảng tính excel là được.

2. Chèn một phần bảng tính excel vào word bằng Paste Special

Khác trường hợp trên là chèn cả (file) bảng tính vào văn bản word, trường hợp sau chúng ta chỉ chèn một vùng (lựa chọn) của bảng tính excel đưa vào word.

Trường hợp này, bạn copy một vùng bảng tính excel để chèn vào văn bản word nhưng vẫn thực hiện được các tính toán như trên excel.

Tại bảng tính excel, chọn vùng dữ liệu cần copy, nhấn Ctr + C (như hình sau):

Trở về phần soạn thảo văn bản word, tại Menu Home → Chọn Paste → Paste Special, như sau:

Xuất hiện hộp thoại Paste Special:

Chọn Microsoft Excel 2003 Worksheet Object (như hình trên) trong vùng As, xong nhấn OK. Ngay sau đó, vùng bảng tính sẽ được đưa vào văn bản word:

Nếu thao tác đúng, bạn có thể thực hiện các phép tính như trên excel. Việc chuyển sang chế độ excel, bạn chỉ việc nhấp đúp chuột lên vùng bảng tính như cách Chèn excel vào word bằng Object trình bày ở trên.

Việc chèn bảng tính excel vào word khá thuận tiện cho các bạn khi lập các báo cáo, giải trình trên word mà có "đụng" đến excel. Ứng dụng này được kế toán áp dụng khi lập Bản thuyết minh báo cáo tài chính trên file word khá hiệu quả.

Chúc các bạn thành công.

Ketoan.biz

Cách thêm hộp check box vào excel, word

Ketoan.biz 8.3.18 Add Comment

Ketoan.biz

Thường khi thêm check box, chúng ta hay vào Tab Insert → Symboy để chèn hộp kiểm cố định vào văn bản. Tuy nhiên cách này chỉ phù hợp cho dạng văn bản in ra giấy, việc cần khảo sát online sẽ gây khá bất tiện do người dùng không thể tự tích chọn được.

Giải pháp tốt nhất có lẻ là dùng hộp check box tích hợp trong Developer, cả excel và word đều có thể áp dụng được. Hộp kiểm dạng này cho phép người dùng có thể tích chọn / bỏ chọn dễ dàng, thích hợp cho các cuộc khảo sát, thăm dò ý kiến online, tăng tính hiệu quả, chuyên nghiệp.

Bài viết sau sẽ trình bày việc chèn hộp check box vào bảng tính, văn bản với Office 2016.

Thủ thuật tách chuỗi thành số trong excel

Ketoan.biz 17.7.17 1 Comment

Ketoan.biz

Số dạng chuỗi (text) trong excel không thực hiện được các phép tính, cần chuyển chúng thành dạng số (number) để tính toán.

9 thủ thuật excel hay dành cho dân văn phòng

Ketoan.biz 16.5.17 Add Comment

Ketoan.biz

>> 9 phím tắt vàng trong excel mà dân văn phòng phải biết

Sử dụng thủ thuật excel sẽ giúp cho người dùng tiết kiệm được thời gian và tăng hiệu quả công việc.

9 phím tắt vàng trong excel mà dân văn phòng phải biết

Ketoan.biz 10.1.17 Add Comment

Ketoan.biz

>> Một số hàm cơ bản thường dùng trong kế toán

>> Ứng dụng Excel trong kế toán với các hàm tính tổng

Làm việc trên Excel nếu sử dụng các phím tắt sẽ giúp bạn thao tác chính xác và nhanh chóng hơn.

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns

Ketoan.biz 15.8.16 Add Comment

Ketoan.biz

Trong công việc của dân văn phòng, kế toán thường phải làm việc trên các files excel được trích xuất ra từ phần mềm quản lý như phần mềm quản lý nhân sự, phần mềm kế toán ... Tuy nhiên các files này dữ liệu lại "dính" chung với nhau được ngăn cách bởi các dấu "/". "*", "-", ":" ... việc cần làm là phải tách riêng dữ liệu trước khi xử lý, lập các báo cáo phân tích số liệu.

Có nhiều cách tách dữ liệu ra trước và sau các dấu "/". "*", "-", ":" ... như sử dụng công thức kết hợp các hàm tích hợp trong excel để tách chúng. Việc sử dụng hàm nâng cao trong excel để xử lý dữ liệu không phải ai cũng làm được bởi tính phức tạp của nó. May mắn thay, excel đã tích hợp sẳn tính năng tách dữ liệu rất nhanh và cực kỳ đơn giản, đó là tính năng Text to Columns.

Tính năng Text to Columns là một ứng dụng hay của Excel ẩn trong tab Data chuyên tách các dữ liệu thành cột riêng được chỉ định cụ thể.

Trong ví dụ minh họa bên dưới, chúng ta cần tách số liệu (ngăn cách bởi dẫu "-") sang cột riêng, ngày tháng sang cột riêng.

Đầu tiên, bạn chọn vùng dữ liệu cần xử lý, đồng thời click chuột chọn Tab Data → Text to Columns, xuất hiệu hộp thoại sau:

Tiếp theo, các bạn chọn Dellimited rồi click Next để chuyển sang bước kế tiếp:

Ở hình trên, các bạn nhấp chọn dấu kiểm mục Other và nhập vào ô vuông dấu "-", xong nhấp Next → Finish để xem kết quả:

Nếu thao tác dúng, chúng ta sẽ được kết quả như hình bên dưới: Tách dữ liệu số và ngày tháng ra 2 cột E và F.

Lưu ý:

Việc tách dữ liệu trước và sau dấu "," bạn nhấp chọn hộp Comma, tách dữ liệu giữa các khoản trắng bạn chọn hộp Space như hình trên.

Ngoài ra, tách dữ liệu trước và sau dấu bất kỳ ("/". "*", "-", ":" ...), bạn nhấp chọn mục Other rồi nhập vào ô vuông dấu mà mình muốn tách chúng.

Hy vọng với thủ thuật đơn giản trên sẽ hữu ích cho các bạn trong công việc khi phải xử lý một file excel với các tình huống, trường hợp tương tự.

Ketoan.biz

Thủ thuật tách gộp họ tên trong Excel không cần phần mềm

Unknown 27.6.16 2 Comments

Unknown

Trong công việc, đôi khi chúng ta cần phải tách họ và tên riêng trong một danh sách Excel nào đó, từ file Word chuyển qua Excel chẳng hạn.

Trong trường hợp sau, chúng ta cần tách tên riêng một cột và họ (tên lót) riêng một cột trong một bảng tính excel. Công việc sẽ trở nên đơn giản khi chúng ta thực hiện một số thủ thuật sau đây:

1. Đầu tiên, cần copy danh sách họ tên tại cột B sang cột khác, cột E. Chọn vùng danh sách cột E (bôi đen vùng chọn cột E) như hình bên dưới;

2. Nhấn tổ hợp phím Ctrl + F để mở để mở hộp tìm kiếm và thay thuế (Find and Replace), chọn tab Replace để tách tên.

Trong hộp Find what, đánh dấu * và một khoản trắng (nhấn phím * và phím spacebar); Hộp Relace with thì để trống (không làm gì cả);

Mục đích là excel sẽ xóa từ ký tự trắng đầu tiên bên trái cell bảng tính, tức chỉ giữ lại tên.

Chọn Replace All, bạn sẽ thấy hiệu quả ngay lập tức.

Nếu thao tác đúng, bạn sẽ được kết quả như hình sau: Tách được tên riêng trong danh sách tại cột E.

3. Tách họ và tên lót vào cột D:

Công việc còn lại cũng khá đơn giản, tại ô D3 nhập vào công thức: =LEFT(B3,LEN(B3)-LEN(E3))

Copy xuống dưới với nhứng ô còn lại, kết quả chúng ta đã tách được danh sách với tên và họ thành hai cột riêng.

4. Gộp tên họ từ nhiều cột thành một cột

Tức là yêu cầu công việc, quy trình làm ngược lại như đã trình bày ở trên. Chẳng hạn, chúng ta có một danh sách với tên họ nằm ở hai cột D và E và giờ muốn gộp chúng lại thì phải làm thế nào?

Công việc trở nên khá đơn giản: Đó là sử dụng "&" hoặc hàm Concat(Text1,...) để nối chúng lại với nhau:

Tại ô F3, gỏ vào công thức: =D3&" "&E3

Hoặc công thức: =CONCAT(D3," ",E3)

Copy công thức xuống dưới cho những ô còn lại, ta sẽ được kết quả như hình trên.

Lưu ý: 

Nếu tách riêng Họ thành một cột, Tên và Tên lót riêng riêng một cột thì chúng ta thực hiện tương tự như trên,  nhưng tại bước 2 trong hộp Find what, chúng ta nhập khoản trắng trước rồi mới đến dấu * (chỉ để tách họ riêng một cột); Tại bước 3, tại ô F3, chúng ta gỏ vào công thức =RIGHT(B3,LEN(B3)-LEN(E3)).

Nếu bản danh sách ban đầu khi trích xuất mà có các khoản cách giữa các chữ không đồng nhất (trước và sau các chữ có khoản trắng thừa) thì chúng ta buộc phải xử lý loại bỏ khoản trắng thừa rồi mới áp dụng các thủ thuật trên. Loại bỏ khoản trắng bị thừa bằng công thức sau =TRIM(SUBSTITUTE(B3,CHAR(160),"")) với B3 là ô cần xử lý.

Hy vọng với chia sẻ trên sẽ hữu ích cho các bạn trong công việc khi phải "tự tay" tách hay gộp nhiều cột với nhau mà không phải sử dụng đến các phần mềm phức tạp can thiệp.

Ketoan.biz

Ứng dụng Excel trong kế toán với các hàm tính tổng

ketoanthue 22.12.14 Add Comment

ketoanthue

Excel là một công cụ hổ trợ đắc lực cho kế toán trong công việc, từ tính toán các bảng lương, bảng tính giá thành … cho đến thiết lập sổ sách kế toán. Một trong những hàm thông dụng của excel phải kể đến là các hàm tính tổng, giúp kế toán xử lý nhiều tính toán một cách nhanh chóng.

1. HÀM SUMIF:

Nếu bạn không hài lòng với với hàm tính tổng đơn giản là Sum thì bạn có thể tìm hiểu thêm hàm Sumif- tính tổng có điều kiện, giúp bạn tính nhanh với những đối số được chỉ định. Hàm Sumif có hầu hết trong các phiên bản Excel.

Cú pháp: =SUMIF(range,criteria,sum_range)

Trong đó: 

+ Range: Là vùng được chọn có chứa các ô điều kiện.

+ Criteria: Là điều kiện để tính tổng.

+ Sum_range: Vùng cần tính tổng.

 Trong ví dụ (1) tính tổng tiền lương của chức vụ quản lý (QL): Tại ô E24, nhâp vào công thức như hình (1), với Range từ C7-C20, Criteria điều kiện là "QL" (ô C24), Sum_range vùng tính tổng từ J7-J20, kết quả bằng 13.728.846

2. HÀM SUMIFS:

Nếu hàm Sumif chỉ cho phép bạn tính tổng với một điều kiện duy nhất thì hàm Sumifs cho phép bạn tính tổng với nhiều điều kiện cùng lúc. Hàm Sumifs sinh ra là để khắc phục nhược điểm của hàm Sumif, nó xuất hiện từ phiên bản excel 2007 trở lên.

Cú pháp: =SUMIFS (sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

Trong đó:

+ sum_range: bắt buộc, là một vùng các ô để tính tổng cho các ô có giá trị chuỗi, nếu rỗng thì được bỏ qua.

+ criteria_range1: bắt buộc, là một vùng các ô để so sánh với điều kiện tính tổng thứ nhất. 

+ criteria1: bắt buộc, là điều kiện để tính tổng, giá trị của criterial có thể là số, biểu thức, cột tham chiếu, chuỗi.

+ criteria_range2, criteria2, … là tùy chọn, không bắt buộc. Excel cho phép tối đa là 127 cặp điều kiện criteria_range/criteria.

Trong ví dụ (2) tính tổng lương của những người có chức vụ là "Thợ" của bộ phận "Mộc", tại ô E26 điền công thức như hình trên của trường hợp (2), với sum_range là vùng cần tính tổng  từ J7-J20, vùng chứa điều kiện thứ nhất criteria_range1 từ C7-C20, điều kiện thứ nhất criteria1 là chức vụ "Thợ"  là ô C26; Tương tự vùng chứa điều kiện thứ 2 và điều kiên thứ 2 là D7-D20 và D26. Kết quả tại ô E26 bằng 19.273.077

3. HÀM DSUM

Nếu bạn cảm thấy hàm Sumifs hơi phức tạp, thì hàm Dsum được biết đến như một hàm tính tổng rất nhanh mà công thức cũng rất đơn giản. Dsum cũng là hàm tính tổng có điều kiện, nó có hầu hết trong các phiên bản Excel. Hàm Dsum dùng để tính tổng ở trường dữ liệu (Field) trong bảng dữ liệu (Database) thỏa mãn điều kiện (Criterial) cho trước.

Cú pháp: =Dsum(Database,Field,Criterial)

Trong đó:

+ Database: Bảng dữ liệu bao gồm cả cột Tiêu đề

+ Field: Địa chỉ của trường cần tính tổng

+ Criterial: Điều kiện để tính tổng.

Trong ví dụ (3) chúng ta cũng tính tổng lương của những người có chức vụ là "Thợ" của bộ phận "Mộc" xem có đúng với ví dụ (2) không nhé.

Đầu tiên bạn cần tạo bảng điều kiện (Criterial) như hình trên (A32-J33).
Tại ô E29, bạn điền vào công thức như hình trên (trường hợp 3) với bảng dữ liệu (Database) từ A6-J20 (bao gồm cả tiêu đề bảng), trường cần tính tổng (Field) là "Tổng lương", điều kiện để tính tổng (Criterial) từ (A32-J33). Kết quả tại ô E29 cũng bằng 19.273.077, đúng với kết quả trường hợp (2).

Trên đây là một số ví dụ về việc sử dụng hàm Sumif, Sumifs, Dsum, qua đó các bạn cũng nhận thấy dược sự tương đồng cũng như khác nhau của 3 hàm này. Hy vọng các hàm trên sẽ hữu ích cho các bạn khi ứng dụng vào công việc.

Ketoan.biz

Một số hàm Excel cơ bản dùng trong kế toán

ketoanthue 11.9.14 Add Comment

ketoanthue

(Ảnh minh họa, nguồn internet)

I. HÀM LOGIC

 1. Hàm AND

- Cú pháp:   AND (Logical1, Logical2, ….)

- Các đối số:   Logical1, Logical2… là các biểu thức điều kiện.

Hàm trả về giá trị TRUE (1) nếu tất cả các đối số của nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số của nó là sai.

2. Hàm OR

- Cú pháp:   OR (Logical1, Logical2…)

- Các đối số: Logical1, Logical2… là các biểu thức điều kiện.

- Hàm trả về giá trị TRUE (1) nếu bất cứ một đối số nào của nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số của nó là sai.

- Ví dụ:  =OR(F7>03/02/74,F7>01/01/2002)

3. Hàm NOT

- Cú pháp:   NOT(Logical)

- Đối số: Logical là một giá trị hay một biểu thức logic.

- Hàm đảo ngược giá trị của đối số. Sử dụng NOT khi bạn muốn phủ định giá trị của đối số trong phép toán này.

II. NHÓM HÀM TOÁN HỌC

1. Hàm ABS

Lấy giá trị tuyệt đối của một số

- Cú pháp: ABS(Number)

- Đối số: Number là một giá trị số, một tham chiếu hay một biểu thức.

- Ví dụ:   =ABS(A5 + 5)

2. POWER

Hàm trả về lũy thừa của một số.

- Cú pháp: POWER(Number, Power)

- Các tham số:  Number: Là một số thực mà bạn muốn lấy lũy thừa.

- Power: Là số mũ.

- Ví dụ    = POWER(5,2) = 25

3. Hàm PRODUCT

Bạn có thể sử dụng hàm PRODUCT thay cho toán tử nhân * để tính tích của một dãy.

- Cú pháp:    PRODUCT(Number1, Number2…)

- Các tham số: Number1, Number2… là dãy số mà bạn muốn nhân.

4. Hàm MOD

Lấy giá trị dư của phép chia.

- Cú pháp: MOD(Number, pisor)

- Các đối số:   Number: Số bị chia.

- pisor: Số chia.

5. Hàm ROUNDUP

Làm tròn một số.

-  Cú pháp:  ROUNDUP(Number, Num_digits)

- Các tham số:   Number: Là một số thực mà bạn muốn làm tròn lên.

Number_digits: là bậc số thập phân mà bạn muốn làm tròn.

- Chú ý:

  – Nếu Num_digits > 0 sẽ làm tròn phần thập phân.

  – Nếu Num_digits = 0 sẽ làm tròn lên số tự nhiên gần nhất.

  – Nếu Num_digits < 0 sẽ làm tròn phần nguyên sau dấu thập phân.

6. Hàm EVEN

- Làm tròn lên thành số nguyên chẵn gần nhất.

- Cú pháp: EVEN(Number)

- tham số: Number là số mà bạn muốn làm tròn.

- Chú ý:

- Nếu Number không phải là kiểu số thì hàm trả về lỗi #VALUE!

 7. Hàm ODD

Làm tròn lên thành số nguyên lẻ gần nhất.

- Cú pháp: ODD(Number)

- Tham số: Number là số mà bạn muốn làm tròn.

8. Hàm ROUNDDOWN

Làm tròn xuống một số.

- Cú pháp:   ROUNDDOWN(Number, Num_digits)

- Các tham số: tương tự như hàm ROUNDUP.

 III. NHÓM HÀM THỐNG KÊ

A. Nhóm hàm tính tổng

1. Hàm SUM

Cộng tất cả các số trong một vùng dữ liệu được chọn.

- Cú pháp:   SUM(Number1, Number2…)

- Các tham số: Number1, Number2… là các số cần tính tổng.

2. Hàm SUMIF : 

Tính tổng của các ô được chỉ định bởi những tiêu chuẩn đưa vào.

- Cú pháp:  SUMIF(Range, Criteria, Sum_range)

- Các tham số:  Range: Là dãy mà bạn muốn xác định.

- Criteria: các tiêu chuẩn mà muốn tính tổng. Tiêu chuẩn này có thể là số, biểu thức hoặc chuỗi.

- Sum_range: Là các ô thực sự cần tính tổng.

- Ví dụ:    = SUMIF(B3:B8,”<=10″)

- Tính tổng của các giá trị trong vùng từ B2 đến B5 với điều kiện là các giá trị nhỏ hơn hoặc bằng 10.

B. NHÓM HÀM TÍNH GIÁ TRỊ TRUNG BÌNH

1. Hàm AVERAGE

Trả về gi trị trung bình của các đối số.

- Cú pháp:    AVERAGE(Number1, Number2…)

- Các tham số: Number1, Number2 … là các số cần tính giá trị trung bình.

2. Hàm SUMPRODUCT

Lấy tích của các dãy đưa vào, sau đó tính tổng của các tích đó.

- Cú pháp:    SUMPRODUCT(Array1, Array2, Array3…)

- Các tham số: Array1, Array2, Array3… là các dãy ô mà bạn muốn nhân sau đó tính tổng các tích.

- Chú ý:

-  Các đối số trong các dãy phải cùng chiều. Nếu không hàm sẽ trả về giá trị lỗi #VALUE.

C. NHÓM HÀM TÍNH GIÁ TRỊ LỚN NHẤT, NHỎ NHẤT

1. Hàm MAX

Trả về số lớn nhất trong dãy được nhập.

- Cú pháp:   MAX(Number1, Number2…)

- Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị lớn nhất ở trong đó.

2. Hàm LAGRE

Tìm số lớn thứ k trong một dãy được nhập.

- Cú pháp:   LARGE(Array, k)

- Các tham số:  Array: Là một mảng hoặc một vùng dữ liệu.

  k: Là thứ hạng của số bạn muốn tìm kể từ số lớn nhất trong dãy.

3. Hàm MIN

Trả về số nhỏ nhất trong dãy được nhập vào.

- Cú pháp:   MIN(Number1, Number2…)

- Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị nhỏ nhất ở trong đó.

4. Hàm SMALL

Tìm số nhỏ thứ k trong một dãy được nhập vào.

- Cú pháp:  SMALL(Array, k)

- Các tham số:   Array: Là một mảng hoặc một vùng của dữ liệu.

 k: Là thứ hạng của số mà bạn muốn tìm kể từ số nhỏ nhất trong dãy.

D. NHÓM HÀNG ĐẾM DỮ LIỆU

1. Hàm COUNT

Hàm COUNT đếm các ô chứa dữ liệu kiểu số trong dãy.

- Cú pháp:  COUNT(Value1, Value2, …)

- Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

2. Hàm COUNTA

Đếm tất cả các ô chứa dữ liệu.

- Cú pháp:  COUNTA(Value1, Value2, …)

- Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

3. Hàm COUNTIF

Hàm COUNTIF đếm các ô chứa giá trị số theo một điều kiện cho trước.

- Cú pháp:   COUNTIF(Range, Criteria)

- Các tham số:   Range: Dãy dữ liệu mà bạn muốn đếm.

- Criteria: Là tiêu chuẩn cho các ô được đếm.

 Ví dụ:  = COUNTIF(B3:B11,”>100″): (Đếm tất cả các ô trong dãy B3:B11 có chứa số lớn hơn 100)

IV. NHÓM HÀM CHUỖI

1. Hàm LEFT

Trích các ký tự bên trái của chuỗi nhập vào.

- Cú pháp: LEFT(Text,Num_chars)

- Các đối số: Text: Chuỗi văn bản.

Num_Chars: Số ký tự muốn trích.

Ví dụ:  =LEFT(Tôi tên là,3) = “Tôi”

2. Hàm RIGHT

Trích các ký tự bên phải của chuỗi nhập vào.

- Cú pháp: RIGHT(Text,Num_chars)

- Các đối số: tương tự hàm LEFT.

Ví dụ:  =RIGHT(Tôi tên là,2) = “là”

3. Hàm MID

Trích các ký tự từ số bắt đầu trong chuỗi được nhập vào.

- Cú pháp:  MID(Text,Start_num, Num_chars)

- Các đối số: Text: chuỗi văn bản.

  Start_num: Số thứ tự của ký tự bắt đầu được trích.

  Num_chars: Số ký tự cần trích.

4. Hàm UPPER

  Đổi chuỗi nhập vào thành chữ hoa.

- Cú pháp: UPPER(Text)

5. Hàm LOWER

  Đổi chuỗi nhập vào thành chữ thường.

- Cú pháp: LOWER(Text)

6. Hàm PROPER

  Đổi ký từ đầu của từ trong chuỗi thành chữ hoa.

- Cú pháp: PROPER(Text)

  Ví dụ:  =PROPER(phan van a) = “Phan Van A”

7. Hàm TRIM

  Cắt bỏ các ký tự trắng ở đầu chuỗi và cuối chuỗi.

- Cú pháp: TRIM(Text)

V. NHÓM HÀM NGÀY THÁNG

1. Hàm DATE

  Hàm Date trả về một chuỗi trình bày một kiểu ngày đặc thù.

- Cú pháp: DATE(year,month,day)

- Các tham số: Year: miêu tả năm, có thể từ 1 đến 4 chữ số. Nếu bạn nhập 2 chữ số, theo mặc định Excel sẽ lấy năm bắt đầu là: 1900.(Ví dụ)

  Month: miêu tả tháng trong năm. Nếu month lớn hơn 12 thì Excel sẽ tự động tính thêm các tháng cho số miêu tả năm.

  Day: miêu tả ngày trong tháng. Nếu Day lớn hơn số ngày trong tháng chỉ định, thì Excel sẽ tự động tính thêm ngày cho số miêu tả tháng.

Lưu ý:

  Excel lưu trữ kiểu ngày như một chuỗi số liên tục, vì vậy có thể sử dụng các phép toán cộng (+), trừ (-) cho kiểu ngày.(Ví dụ)

2. Hàm DAY

  Trả về ngày tương ứng với chuỗi ngày đưa vào. Giá trị trả về là một số kiểu Integer ở trong khoảng từ 1 đến 31.

- Cú pháp: DAY(Serial_num)

- Tham số: Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.

3. Hàm MONTH

  Trả về tháng của chuỗi ngày được mô tả. Giá trị trả về là một số ở trong khoảng 1 đến 12.

- Cú pháp: MONTH(Series_num)

- Tham số: Series_num: Là một chuỗi ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.

4. Hàm YEAR

  Trả về năm tương ứng với chuỗi ngày đưa vào. Year được trả về là một kiểu Integer trong khoảng 1900-9999.

- Cú pháp: YEAR(Serial_num)

- Tham số:

- Serial_num: Là một dữ liệu kiểu ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác

5. Hàm TODAY

  Trả về ngày hiện thời của hệ thống.

- Cú pháp: TODAY()

- Hàm này không có các đối số.

6. Hàm WEEKDAY

  Trả về số chỉ thứ trong tuần.

- Cú pháp: WEEKDAY(Serial, Return_type)

- Các đối số:   Serial: một số hay giá trị kiểu ngày.

                       Return_type: chỉ định kiểu dữ liệu trả về.

 VI. HÀM VỀ THỜI GIAN

1. Hàm TIME

  Trả về một chuỗi trình bày một kiểu thời gian đặc thù. Giá trị trả về là một số trong khoảng từ 0 đến 0.99999999, miêu tả thời gian từ 0:00:00 đến 23:59:59.

- Cú pháp:  TIME(Hour,Minute,Second)

- Các tham số: Được tính tương tự ở hàm DATE.

- Hour: miêu tả giờ, là một số từ 0 đến 32767.

- Minute: miêu tả phút, là một số từ 0 đến 32767.

- Second: miêu tả giây, là một số từ 0 đến 32767.

2. Hàm HOUR

- Trả về giờ trong ngày của dữ liệu kiểu giờ đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 (12:00A.M) đến 23 (11:00P.M).

- Cú pháp: HOUR(Serial_num)

- Tham số:

  Serial_num: Là dữ liệu kiểu Time. Thời gian có thể được nhập như:

  Một chuỗi kí tự nằm trong dấu nháy (ví dụ “5:30 PM”)

  

  Một số thập phân (ví dụ 0,2145 mô tả 5:08 AM)

  Kết quả của một công thức hay một hàm khác.

3. Hàm MINUTE

  Trả về phút của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

- Cú pháp: MINUTE(Serial_num)

- Tham số:   Serial_num: Tương tự như trong công thức HOUR.

4. Hàm SECOND

  Trả về giây của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

- Cú pháp: SECOND(Serial_num)

- Tham số: Serial_num: Tương tự như trong công thức HOUR.

5. Hàm NOW

  Trả về ngày giờ hiện thời của hệ thống.

- Cú pháp: NOW()

- Hàm này không có các đối số.

VII. NHÓM HÀM DÒ TÌM DỮ LIỆU

1. Hàm VLOOKUP

  Tìm ra một giá trị khác trong một hàng bằng cách so sánh nó với các giá trị trong cột đầu tiên của bảng nhập vào.

- Cú pháp: VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])

- Các tham số:

  

  Lookup Value: Giá trị cần đem ra so sánh để tìm kiếm.

  Table array: Bảng chứa thông tin mà dữ liệu trong bảng là dữ liệu để so sánh. Vùng dữ liệu này phải là tham chiếu tuyệt đối.

  Nếu giá trị Range lookup là TRUE hoặc được bỏ qua, thì các giá trị trong cột dùng để so sánh phải được sắp xếp tăng dần.

  Col idx num: số chỉ cột dữ liệu mà bạn muốn lấy trong phép so sánh.

  Range lookup: Là một giá trị luận lý để chỉ định cho hàm VLOOKUP tìm giá trị chính xác hoặc tìm giá trị gần đúng. + Nếu Range lookup là TRUE hoặc bỏ qua, thì giá trị gần đúng được trả về.

Chú ý:

- Nếu giá trị Lookup value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng Table array, nó sẽ thông báo lỗi #N/A.

- Ví dụ:   =VLOOKUP(F11,$C$20:$D$22,2,0)

- Tìm một giá trị bằng giá trị ở ô F11 trong cột thứ nhất, và lấy giá trị tương ứng ở cột thứ 2.

 2. Hàm HLOOKUP

   Tìm kiếm tương tự như hàm VLOOKUP nhưng bằng cách so sánh nó với các giá trị trong hàng đầu tiên của bảng nhập vào.

- Cú pháp:  HLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])

- Các tham số tương tự như hàm VLOOKUP.

3. Hàm INDEX

Trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay vùng dữ liệu.

- Cú pháp: INDEX(Array,Row_num,Col_num)

 Các tham số:

- Array: Là một vùng chứa các ô hoặc một mảng bất biến.

- Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num tương ứng là tùy ý.

- Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc Col_num được sử dụng.

- Row_num: Chọn lựa hàng trong Array. Nếu Row_num được bỏ qua thì Col_num là bắt buộc.

- Col_num: Chọn lựa cột trong Array. Nếu Col_num được bỏ qua thì Row_num là bắt buộc.

4. Hàm RANK

Dò tìm sắp xếp thứ hạng của một trong danh sách vùng dữ liệu (xếp thứ hạng danh sách).

- Cú pháp: RANK(Number,Ref,[Order])

- Các tham số: 

Number: Số mà bạn muốn tìm thứ hạng.

Ref: Tham chiếu tơi một danh sách các số.

Order: Tùy chọn sắp xếp (nếu là 0 (không) danh sách tham chiếu theo thứ tự giảm dần, nếu là 1 thì tác dụng ngược lại).

Ketoan.biz tổng hợp

Giấu bảng tính Excel thật đơn giản

ketoanthue 24.8.14 Add Comment

ketoanthue

Trong Excel đôi khi bạn cần giấu bảng tính của mình vì lý do tế nhị nào đó. Có nhiều cách để thực hiện điều này như sử dụng thao tác lệnh Format > Sheet > Hide để giấu các bảng tính và dùng lệnh Format > Sheet > UnHide hiện lại bảng tính. 

Tuy nhiên cách này khá đơn giản, người sử dụng dễ dàng mở được nó, xin giới thiệu các bạn một cách giấu sheet thú vị hơn, ít người biết hơn: thao tác trong chương trình VBA (Microsoft Visual Basic).

Bước 1: Tiến hành mở file Excel cần giấu bảng tính lên.

Bước 2: Nhấn ALT+F11 để mở cửa sổ Microsoft Visual Basic ra. Trong cửa sổ Project – VBA Project, nhắp đúp vào VBA Project, tiếp tục nhắp đúp vào Microsoft Excel Objects để mở các bảng tính trong tài liệu, chọn Sheet mà bạn muốn giấu; tiếp theo nhấn phím F4 để mở tùy chọn Visible trong VBA.

Bước 3: Trong tùy chọn Visible (dòng cuối cùng) bạn nhắp vào dấu tam giác chọn 2-xlSheetVeryHidden để có thể giấu bảng tính excel mong muốn.

Bước 4: Đóng cửa sổ làm việc của VBA lại và kiểm tra thành quả: Chắc chắn Sheet mà bạn chọn đã được giấu.
 

Muốn ẩn lại bản tính, bạn thao tác như như trên và chỉ cần  chọn -1-xlSheetVisible là bảng tính đó hiện lại bình thường.

 Blogketoan.tk