Excel: Hàm PMT tính số tiền thanh toán hàng kỳ của khoản vay

(Anhgolden's Blog) - Cho 1 ví dụ như sau. Bạn cần vay một khoản tiền là 100 triệu tại thời điểm hiện tại, trả đều đặn hàng tháng trong vòng 3 năm với lãi suất 8% một năm. Vậy mỗi tháng phải trả bao nhiêu tiền? Tổng cộng sau 3 năm phải trả cả gốc và lãi là bao nhiêu tiền?

Cú pháp:

=PMT(rate, nper, pv, [fv], [type])

trong đó:

- rate: là lãi suất hàng kỳ của khoản vay.

- nper: Number of Period - là tổng số kỳ thanh toán

- pv: Present Value - là giá trị khoản vay (nợ gốc)

- [fv]: Future Value (tùy chọn) - là giá trị tương lai (hoặc số dư hoặc còn phải thanh toán khoản vay) của khoản tiền sau khi thực hiện việc thanh toán đợt cuối cùng. Nếu không chọn thì được hiểu mặc định là sẽ trả hết khoản vay.

- [type]: (tùy chọn) - chọn thời điểm thanh toán, giữa số 0 (đầu kỳ) hoặc số 1 (cuối kỳ)

Với ví dụ trên, sẽ được hiểu như sau:

- rate: 8% / 12 (lãi suất hàng kỳ - hàng tháng)

- nper: 3 x 12 (3 năm x 12 tháng)

- pv: 100tr (khoản vay hiện tại hay nợ gốc)

Read More »

Excel: Đếm số lượng Sheet trong Bảng tính

(Anhgolden's Blog) - Để xác định số lượng Sheet trong bảng tính, ta có thể sử dụng cách sau:

Cách 1: Công thức =Info("numfile")

Lưu ý: Khi Insert hoặc Delete Sheet, công thức không tự động update kết quả. Ta cần phải Refresh bằng cách F2 ô công thức và Enter lại.

Cách 2: Dùng VBA - ALT-F11 - Insert Module

Copy và Run đoạn Script sau:

Public Sub dem_so_sheets()
  MsgBox "So sheet trong bang tinh nay la " & Application.Sheets.Count
End Sub

Read More »

Excel: Thể hiện tỷ trọng và tìm giá trị theo thứ hạng

(Anhgolden's Blog) - Để thể hiện tỷ trọng giá trị các ngày trong tháng, so với các tháng cùng kỳ, ta thể hiện qua Conditional formating.

Ngoài ra, để xác định giá trị lớn thứ 1, thứ 2 hoặc thứ n cũng như giá trị nhỏ thứ 1, 2, m. Ta dùng công thức sau:

=Large(Mảng, thứ hạng)
=Small(Mảng, thứ hạng)



Note: Max() = Large(Mảng,1)

Read More »

Functions: Excel vs MS Access vs SQL Server

(Anhgolden's Blog) - Tổng hợp

1. Hàm tìm kiếm chuỗi (Search or Find): Trả kết quả False hoặc Vị trí tìm được. Riêng SQL Server kết quả từ 0 đến N (Vị trí tìm được).

Excel: =Find(Str_searched, InString, [Start])

MS Access: InStr([Start], InString, Str_searched)

SQL Server: CharIndex(Str_searched, InString, [Start])

2. Hàm chuyển đổi chuỗi số sang số (Text to number):

Excel: =Value(text)

MS Access: Val(text)

SQL Server: Convert(Int, text)

3. Hàm lấy 1 phần tử trong chuỗi

Excel: Mid(String,Start,Length)

MS Access: Mid(String,Start,Length)

SQL Server: SubString(String,Start,Length)

4. Hàm điều kiện

Excel: IF(conditions, result-true, result-false)

MS Access: IIF(conditions, result-true, result-false)

SQL Server:

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

Hoặc

CASE

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

4. Hàm lấy ngày hiện tại

Excel: today()

Access: date()

Sql Server: getdate()

Read More »

Access: The search key was not found in any record

(Anhgolden's Blog) - Khi import data từ Excel và Access, bị lỗi như sau:
"The search key was not found in any record". Lưu ý kiểm tra lại Header Row của file data Excel.

Thông thường có Cột trống (không tên) hoặc Tên Cột có ký tự trắng (trống) ở trước (prefix).

Read More »

Excel: Tìm phần tử cuối của Mảng

(Anhgolden's Blog) - Trong trường hợp muốn tìm phần tử cuối của mảng (bao gồm nhiều phần tử trống), ta sử dụng công thức sau:

=Lookup(1,1/Len(B2:F2),B2:F2)

Kết quả: fds

Diễn giải: Tìm ([phần tử],[mảng cần tìm],[trả kết quả của mảng tương ứng])

Xem thêm: cách khác

1) =Lookup(1,1/(B2:F2<>""),B2:F2)

2)=Lookup(1,1/(NOT(ISBLANK(B2:F2))),B2:F2)

Read More »

Excel: Hotfix sửa lỗi Excel 2007 không mở được file Excel 2010 có cài password

(Anhgolden's Blog) - Khi sử dụng Excel 2007 để mở file Excel 2010 có cài password sẽ xuất hiện lỗi:

"The encryption type used is not available, contact the author of the file. More encryption types are available using the High Encryption Pack ".

Xin chia sẻ Hotfix sửa lỗi trên tại: https://support.microsoft.com/en-us/kb/954572

Ghi chú: Lỗi này cũng gặp tương tự khi sử dụng Excel 2003 không mở được file Excel 2007 có cài password.

Read More »

Excel: Hàm loại bỏ ký tự đặc biệt trong chuỗi bằng VBS

(Anhgolden's Blog) - Chia sẻ Hàm VB loại bỏ các ký tự đặc biệt trong chuỗi.

Công thức = RemoveSpecial([Chuỗi])

Thực hiện: Alt-F11 >> Insert Module >> Chèn đoạn Code sau:

Function RemoveSpecial(Chuoi As String) As String

Dim i As Integer
Dim Kytu As String
Dim Ketqua As String

Ketqua = ""

For i = 1 To Len(Chuoi)
    Kytu = Mid(Chuoi, i, 1)
    If Kytu = "-" Or Kytu = "_" Or Kytu = "(" Or Kytu = ")" Or Kytu = " " Then
        Ketqua = Ketqua
    Else
        Ketqua = Ketqua & Kytu
End If

Next

RemoveSpecial = Ketqua

End Function

Read More »

Excel: Hàm tìm kiếm ký tự đặc biệt trong Excel

(Anhgolden's Blog) - Chia sẻ 2 cách: bằng hàm công thức (thông thường) hoặc bằng hàm VBS.

Cách 1: Bằng công thức thông thường.

=ISERROR(OR(FIND("-",[Chuỗi],0),FIND("*",[Chuỗi],0), ... ,FIND("[ký tự đặc biệt]",[Chuỗi],0)))

Cứ mỗi tìm kiếm ký tự đặc biệt mong muốn thì bổ sung: ,FIND("[ký tự đặc biệt]",[Chuỗi],0)

Ghi chú: có dấu phảy (,) ở đằng trước nhé.

Cách 2: Tạo hàm tìm kiếm ký tự đặc biệt trong chuỗi bằng VBS.

Công thức =IsSpecial([Chuỗi])

Nếu Chuỗi tồn tại kỹ tự đặc biệt như [0-9a-zA-Z] hoặc [ _ ] thì cho kết quả là 0 (False), còn ngược lại là 1 (True).

Alt-F11 >> Insert Module >> Chép đoạn Code sau:

Public Function IsSpecial(s As String) As Long

    Dim L As Long, LL As Long
    Dim sCh As String
    IsSpecial = 0
    For L = 1 To Len(s)
        sCh = Mid(s, L, 1)
        If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
        Else
            IsSpecial = 1
            Exit Function
        End If
    Next L
End Function

Read More »

Excel: Công thức chèn Line Break (Alt-Enter)

(Anhgolden's Blog) - Thông thường trong Excel để chèn Line Break - xuống hàng, trong cùng nội dung của Cell ta thường dùng phím tắt Alt-Enter.

Ghi chú: Phải chọn chế độ hiển thị Wrap Text

Trong trường hợp, sử dụng hàm ta có công thức như sau:

= Text1&Char(10)&Text2

Ví dụ: Theo hình bên
= A2&Char(10)&B2
(Chọn chế độ hiển thị là Wrap Text)

Read More »

Excel: Khác nhau giữa hàm Substitute và Replace

(Anhgolden's Blog) - Hàm Substitute và hàm Replace trên Excel đều tìm và thay thế chuỗi ký tự, nhưng có khác nhau:

1. Hàm Substitute: khi muốn tìm và thay thế chuỗi ta sẽ dùng hàm Substitute.

Công thức:

SUBSTITUTE( text, old_text, new_text, [count] )

Diễn giải:
- text: Chuỗi đối tượng.
- old_text: Tìm chuỗi ký tự
- new_text: Thay thế bằng
- [count]: optional - số lần được thực hiện tìm và thay thế.

2. Hàm Replace: sử dụng khi biết vị trí ký tự chuỗi cần thay thế.

Công thức:

REPLACE( old_text, start, number_of_chars, new_text )

Diễn giải:
- old_text: Tìm chuỗi ký tự
- start: bắt đầu từ thứ tự ký tự (từ trái sang phải)
- numbers: số lượng ký tự từ ký tự thứ start
- new_text: Thay thế bằng

Read More »

VBS: Unhide All Hiden Sheets

(Anhgolden's Blog) - Mặc định Excel cho phép chọn nhiều Sheet và Hide (Ẩn), nhưng khi Undide thì phải chọn từng Sheet một.

Xin chia sẻ 1 VBS cho phép Unhide All Hiden Sheets:

B1. Alt + F11 để mở Microsoft Visual Basic for Applications window.

B2. Click Insert > Module và paste đoạn Code sau vào Module Window.

Sub UnhideAllSheets()
    Dim ws As Worksheet
 
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
 
End Sub

Read More »

Excel: Kiểm tra sự trùng lặp dữ liệu

(Anhgolden's Blog) - Trong nhiều trường hợp chúng ta cần kiểm tra sự trùng lặp của dữ liệu.

Xin chia sẻ cách thực hiện trên Excel như sau:

Cách 1: Đặt công thức.

Ở Cell B2 ta đặt công thức như sau:
=COUNTIF($A$1:A1,A1)

Copy công thức cho các Cell còn lại. (B2:B9)

Kết quả >1 tức là dữ liệu bị trùng.

Cách 2: Dùng Format Conditions

Chọn Conditional Formatting - New Rule

Chọn loại "Use a formula..."

Chọn Range cho Applies to...

Có thể dùng loại Rule là Format only unique or duplicate values

Xem thêm về Excel Conditional Formatting tại http://www.anhgolden.com/2013/01/excel-format-conditions-format-theo.html

Read More »

Format thêm nháy đôi (Quotes) cho dữ liệu Excel

(Anhgolden's Blog) - Thông thường để xuất dữ liệu (Export) ra file CSV và nhập dữ liệu (Import) từ file CSVvào Excel. Ta thường dùng dấu nháy đôi ( ": quotes) ở trước và sau dữ liệu.

Chuyển từ:

Nguyễn Văn A,123 đường XYZ, phường A, quận B, TP. HCM

Thành:

"Nguyễn Văn A","123 đường XYZ, phường A, quận B, TP. HCM"

Xin chia sẻ cách thực hiện trên Excel như sau:

B1: Select Cells dữ liệu cần format.

B2: Format Cells - Chọn Custom Catergory:

Nhập Type là: "''"@"''"

Ghi chú: [Nháy đôi][Nháy đơn][Nháy đơn][Nháy đôi]@[Nháy đôi][Nháy đơn][Nháy đơn][Nháy đôi]

Read More »

Excel: Import CSV into Excel with Lines break

(Anhgolden's Blog) - Sửa lỗi Import file CSV tiếng Việt vào Excel, dữ liệu bị xuống dòng.

Thông thường khi Double click vào file CSV sẽ mở bằng Excel và sẽ không hiển thị được nội dung tiếng Việt. Do đó, ta thường Import file CSV vào Excel bằng Text Import Wizard và chọn File origin format là UTF-8:

Tuy nhiên, dữ liệu có thể bị tự động xuống hàng khi chưa hết dòng dữ liệu.

Để khắc phục lỗi này, xin chia sẽ cách thức như sau:
B1: Double click vào file CSV để mở bằng Excel.
B2: Chèn một cột ở vị trí đầu tiên và mỗi dòng có dữ liệu giống nhau ví dụ là BOL (Begin Of Line)
B3: Save lại file CSV.
B4: Mở file CSV bằng NotePad++
B5: Menu NotePad++ Edit >> Lines Operations >> Join lines (Mục đích là Join tất cả các dòng thành 1 dòng).
B6: Ctrol - H: tìm kiếm ký tự "BOL," để thay thế bằng "\n" (Mục đích là Split ra thành từng dòng căn cứ theo ký tự BOL,)
B7: Xóa dòng đầu tiên (dòng trống).

Bây giờ ta có thể thực hiện bước Import file CSV bằng Text Import Wizard và chọn File Origin Format là UTF-8 nói trên và dữ liệu hiển thị đầy đủ format Tiếng Việt và không bị tự động xuống hàng.

Xem thêm: http://www.anhgolden.com/2010/10/hien-thi-noi-dung-tieng-viet-file-excel.html

Read More »

Excel: Hàm chuyển đổi tiếng Việt có dấu sang không dấu

 (Anhgolden's Blog) - Sưu tầm

Chia sẻ hàm chuyển đổi tiếng Việt có dấu sang không dấu trong Excel bằng VB.

Bước 1: Alt-F11 để mở Microsoft Visua Basic

Bước 2: Insert Module

Bước 3: Copy và Paste đoạn Code sau vào phần nội dung của Module (miền bên phải)

Function ConvertToUnSign(ByVal sContent As String) As String
Dim i As Long
Dim intCode As Long
Dim sChar As String
Dim sConvert As String
ConvertToUnSign = AscW(sContent)
For i = 1 To Len(sContent)
sChar = Mid(sContent, i, 1)
If sChar <> "" Then
intCode = AscW(sChar)
End If
Select Case intCode
Case 273
sConvert = sConvert & "d"
Case 272
sConvert = sConvert & "D"
Case 224, 225, 226, 227, 259, 7841, 7843, 7845, 7847, 7849, 7851, 7853, 7855, 7857, 7859, 7861, 7863
sConvert = sConvert & "a"
Case 192, 193, 194, 195, 258, 7840, 7842, 7844, 7846, 7848, 7850, 7852, 7854, 7856, 7858, 7860, 7862
sConvert = sConvert & "A"
Case 232, 233, 234, 7865, 7867, 7869, 7871, 7873, 7875, 7877, 7879
sConvert = sConvert & "e"
Case 200, 201, 202, 7864, 7866, 7868, 7870, 7872, 7874, 7876, 7878
sConvert = sConvert & "E"
Case 236, 237, 297, 7881, 7883
sConvert = sConvert & "i"
Case 204, 205, 296, 7880, 7882
sConvert = sConvert & "I"
Case 242, 243, 244, 245, 417, 7885, 7887, 7889, 7891, 7893, 7895, 7897, 7899, 7901, 7903, 7905, 7907
sConvert = sConvert & "o"
Case 210, 211, 212, 213, 416, 7884, 7886, 7888, 7890, 7892, 7894, 7896, 7898, 7900, 7902, 7904, 7906
sConvert = sConvert & "O"
Case 249, 250, 361, 432, 7909, 7911, 7913, 7915, 7917, 7919, 7921
sConvert = sConvert & "u"
Case 217, 218, 360, 431, 7908, 7910, 7912, 7914, 7916, 7918, 7920
sConvert = sConvert & "U"
Case 253, 7923, 7925, 7927, 7929
sConvert = sConvert & "y"
Case 221, 7922, 7924, 7926, 7928
sConvert = sConvert & "Y"
Case Else
sConvert = sConvert & sChar
End Select
Next
ConvertToUnSign = sConvert
End Function

Bước 4: Trở về Excel

Bước 5: Đặt công thức

=ConvertToUnSign("Chuỗi tiếng Việt có dấu")

Read More »

Excel: Sumif hoặc Countif chỉ định điều kiện đến Cell cụ thể

(Anhgolden's Blog) - Khi sử dụng hàm Sumif (Tính Sum theo điều kiện) hoặc Countif (Tính Count theo điều kiện), ta thực hiện theo công thức sau:

=SUMIF(Range,Criterial,Sum_Range)

Trong đó:
- Range: Mảng gồm các Cell sẽ kiểm tra điều kiện.
- Criteril: Điều kiện
- Sum_Range: Mảng gồm các Cell sẽ tính Tổng (Sum) thỏa mãn điều kiện.

Ví dụ: Tính tổng thu nhập các đối tượng có giới tính là Nam (như trong hình).

Công thức: =SUMIF(C2:C6,"Nam",D2:D6)

Trong trường hợp, ta muốn chỉ định điều kiện đến Cell cụ thể. Ví dụ như Cell F2. Ta sẽ đặt công thức như sau:

Công thức: =SUMIF(C2:C6,"="&F2,D2:D6)

Chúng ta có thể vận dụng cho Hàm Countif và thêm các điều kiện so sánh ">=" hoặc "<=".


Giới thiệu thêm hàm SUMIFS

Công thức: =SUMIFS(Sum_range,Criterial_Range1,Criterial1,...,Criterial_RangeN,CriterialN)

Như vậy với hàm SUMIFS này, chúng ta có thể sử dụng nhiều Mảng điều kiện hơn.

Chúc thành công!

Read More »

Excel Keyboard Shortcut

(Anhgolden's Blog) - Sưu tầm và tổng hợp các Shortkey thường dùng trong Excel

1. Mở Popup Menu Right Click: Shift - F10

2. Chuyển đổi qua lại giữa các Sheet: Ctrl - Page Up hoặc Ctrl - Page Down

3. Insert (Cell, Row, Column): Ctrl-+

4. Delete (Cell, Row, Column): Ctrl--

5. Ẩn và Hiện Cột: Ctrl-0 // Ctrl-Shift-0

6. Ẩn và Hiện Dòng: Ctrl-9 // Ctrl-Shift-9

Read More »

Excel: Công thức tính phí bậc thang

(Anhgolden's Blog)-Trường hợp ta có nhu cầu thiết lập công thức excel để tính phí theo bậc thang.

Ví dụ:
Nếu đạt sản lượng nhỏ hơn hoặc bằng 5.000.000, sẽ có tỷ lệ phí là 5%. Còn nếu đạt sản lượng lớn hơn 5.000.000 và nhỏ hơn hoặc bằng 10.000.000 thì có tỷ lệ 5% và phần lớn hơn 5.000.000 là 10%. Tương tự cho mức sản lượng nhỏ hơn hoặc bằng 18.000.000 thì có tỷ lệ 5%, phần lớn hơn 5.000.000 là 10% và phần lớn hơn 10.000.000 là 15%...

Xin chia sẻ công thức tính bằng excel: [download]

Read More »

Excel: Highlight the active cell

(Anhgolden's Blog)-Để làm nổi bật Cell đang thực thi hoặc Dòng, Cột của Cell đang thực thi, chúng ta có thể làm Highlight qua các code VBA sau:

Bước 1: Alt-F11 để mở Visual Editor

Bước 2: Chọn Workbook - Double click

Bước 3: chép đoạn code sau:

a) Trường hợp muốn Highlight Cell thực thi:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    ' Highlight the active cell - Thay doi tham so (8) de doi mau 
    Target.Interior.ColorIndex = 8
    Application.ScreenUpdating = True
End Sub

b) Trường hợp muốn Highlight dòng và cột của Cell thực thi

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Sh.Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8
        .EntireColumn.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True

End Sub

Ghi chú: Có thể thay đổi tham số ColorIndex = 8 bằng các giá trị khác nếu muốn đổi màu.

Trường hợp chỉ muốn thực hiện Highlight trên Sheet (không phải Workbook) thì Bước 3 chọn Sheet1, hay Sheet2... (mong muốn) và chép code tương tự. Tuy nhiên thay thế:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Bằng

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Read More »