40 mẹo dùng Excel

1. Làm hình ẩn đằng sau trang văn bản ( watermark ) như ở trong Word :

Trong Excel chức năng Watermark là không sẵn sàng nhưng bạn cũng có thể làm như sau
1.Bấm menu View --> bấm chọn Header and Footer
2.Hộp Page Setup mở ra --> bạn bấm chọn Custom Header hoặc Custom Footer --> bạn có thể bấm trong một trong ba phần
Left section, Center section, hoặc Right section
3.Bạn bấm nút Insert Picture ( nút thứ hai từ bên phải sang ), để chèn hình ảnh bạn muốn đưa
4.Bấm Format Picture - nút cuối cùng trong dãy nút ở Footer hoặc Header --> cửa sổ Format Picture mở ra --> chọn tab Size - để thay đổi kích thước bức hình .

Thống kê mô tả

Bạn có thể sử dụng các phân tích ToolPak add-in để tạo ra thống kê mô tả. Ví dụ, bạn có thể điểm số của 14 người tham gia một thử nghiệm.

Anova

Ví dụ này hướng dẫn bạn cách thực hiện một yếu tố duy nhất ANOVA (phân tích phương sai) trong Excel. Một yếu tố duy nhất hoặc một chiều ANOVA được sử dụng để kiểm định giả thuyết rằng các phương tiện của một số quần thể đều bình đẳng.

Dưới đây bạn có thể tìm thấy tiền lương của những người có bằng cấp về kinh tế, y học hay lịch sử.

H _0: μ ₁ = μ ₂ = μ ₃
H _1: ít nhất là một trong những phương tiện khác nhau.

F-Test

Ví dụ này hướng dẫn bạn cách thực hiện một chiếc F-Test trong Excel. F-kiểm tra được sử dụng để kiểm định giả thuyết rằng sự chênh lệch giữa hai nhóm bằng nhau.

Dưới đây bạn có thể tìm thấy những giờ học của 6 nữ sinh viên và 5 nam sinh viên.

H _{0: 1} σ ² = σ ₂ ²
H _1: σ ₁ ² ≠ σ ₂ ²

Thanh trạng thái - Status Bar

Cách nhanh nhất để xem trung bình, số lượng, số đếm, tối thiểu, tối đa hoặc tổng hợp của các ô được lựa chọn là bằng cách nhìn vào thanh trạng thái.

1. Chọn một loạt các ô.

t-Test

Ví dụ này hướng dẫn bạn cách thực hiện một t-Test trong Excel. T-Test được sử dụng để kiểm định giả thuyết rằng các phương tiện của hai dân đều bình đẳng.

Dưới đây bạn có thể tìm thấy những giờ học của 6 nữ sinh viên và 5 nam sinh viên.

H _0: μ ₁ - μ ₂ = 0
H _1: μ ₁ - μ ₂ ≠ 0

Vẽ đường di chuyển trung bình - Moving Average

Ví dụ này dạy bạn làm thế nào để tính toán di chuyển trung bình của một chuỗi thời gian trong Excel. Một avearge di chuyển được sử dụng để mịn ra bất thường (đột biến) để dễ dàng nhận ra xu hướng.

1. Đầu tiên, chúng ta hãy nhìn vào chuỗi thời gian của chúng tôi.

Làm mịn đồ thị theo cấp số nhân - Exponential Smoothing

í dụ này dạy bạn làm thế nào để áp dụng làm mịn theo cấp số nhân với một chuỗi thời gian trong Excel. Làm mịn theo cấp số nhân được sử dụng để mịn ra bất thường (đột biến) để dễ dàng nhận ra xu hướng.

1. Đầu tiên, chúng ta hãy nhìn vào chuỗi thời gian của chúng tôi.

Tương quan

Hệ số tương quan (một giá trị giữa -1 và +1) cho bạn biết cách mạnh mẽ hai biến có liên quan đến nhau. Chúng ta có thể sử dụng chức năng CORREL hoặc Phân tích ToolPak add-in trong Excel để tìm hệ số tương quan giữa hai biến.

- Một hệ số tương quan của 1 cho thấy một mối tương quan tích cực hoàn hảo. Như tăng biến X, Y biến tăng.Như giảm biến X, Y biến giảm.

Hồi quy

Ví dụ này hướng dẫn bạn cách thực hiện một phân tích hồi quy trong Excel và làm thế nào để diến giải một cách tổng quát nhất thông tin đầu ra .


Dưới đây bạn có thể tìm thấy dữ liệu của chúng tôi. Câu hỏi lớn là: có một mối quan hệ giữa lượng bán (đầu ra) và Giá và Quảng cáo (đầu vào). Nói cách khác: chúng ta có thể dự đoán lượng bán nếu chúng ta biết giá và quảng cáo?

Vấn đề vận tải

Sử dụng giải quyết trong Excel để tìm số của các đơn vị để vận chuyển từ mỗi nhà máy để mỗi khách hàng giảm thiểu tổng chi phí.

Xây dựng mô hình

Mô hình chúng ta sẽ giải quyết trông như sau trong Excel.

1. Xây dựng các vấn đề giao thông vận tải này, trả lời ba câu hỏi sau đây.

một quyết định được thực hiện. là gì? Đối với vấn đề này, chúng ta cần Excel để tìm ra bao nhiêu đơn vị để vận chuyển từ mỗi nhà máy sản xuất đến từng khách hàng.

b. những hạn chế trên những quyết định này là gì? Mỗi nhà máy có một nguồn cung cấp cố định và mỗi khách hàng có nhu cầu cố định.

c. các biện pháp chung về hoạt động cho những quyết định này là gì? Các biện pháp chung về hoạt động là tổng chi phí của lô hàng, do đó mục tiêu là để giảm thiểu số lượng này.

2. Để thực hiện các mô hình dễ hiểu, tên phạm vi sau đây.

Phạm vi Tên	Tế bào
UnitCost	C4: E6
Lô hàng	C10: E12
TotalIn	C14: E14
Nhu cầu	C16: E16
TotalOut	G10: G12
Cung cấp	I10: I12
TotalCost	I16

3. Chèn các chức năng sau.

Giải thích: Các chức năng SUM tính toán tổng số vận chuyển từ mỗi nhà máy (Tổng Out) cho mỗi khách hàng (Tổng số Trong). Tổng chi phí bằng với SUMPRODUCT của UnitCost và lô hàng.

Dùng thử và Lỗi

Với công thức này, nó trở nên dễ dàng để phân tích bất kỳ giải pháp thử nghiệm.

Ví dụ, nếu chúng tôi gửi 100 đơn vị từ Nhà máy 1 tới khách hàng 1, 200 đơn vị từ Nhà máy 2 đến khách hàng 2, 100 đơn vị từ Nhà máy 3 đến khách hàng 1 và 200 đơn vị từ Nhà máy 3 đến khách hàng 3, Tổng Out bằng cấp và Total Trong tương đương nhu cầu. Giải pháp này có tổng chi phí 27800.

Nó không phải là cần thiết để sử dụng thử và sai. Chúng tôi sẽ mô tả tiếp theo như thế nào Solver Excel có thể được sử dụng để nhanh chóng tìm ra giải pháp tối ưu.

Giải quyết các mô hình

Để tìm ra giải pháp tối ưu, thực hiện các bước sau đây.

1. Trên tab dữ liệu, nhấp vào Solver.

Lưu ý: không thể tìm thấy nút Solver? Bấm vào đây để tải Solver add-in .

Nhập các thông số người giải quyết (đọc). Kết quả phải nhất quán với hình ảnh dưới đây.

Bạn có sự lựa chọn của gõ tên phạm vi hoặc nhấp vào các tế bào trong bảng tính.

2. Nhập TotalCost cho các Mục tiêu.

3. Bấm vào Min.

4. Nhập lô hàng cho các tế bào biến đổi.

5. Nhấn nút Add để nhập các hạn chế sau đây.

6. Nhấn nút Add để nhập các hạn chế sau đây.

7. Kiểm tra 'Hãy biến Không bị giới Không tiêu cực' và chọn 'đơn LP.

8. Cuối cùng, nhấp vào giải quyết.

Kết quả:

Giải pháp tối ưu:

Kết luận: nó là tối ưu để vận chuyển 100 đơn vị từ Nhà máy 1 tới khách hàng 2, 100 đơn vị từ Nhà máy 2 đến khách hàng 2, 100 đơn vị từ Nhà máy 2 đến khách hàng 3, 200 đơn vị từ Nhà máy 3 đến khách hàng 1 và 100 đơn vị từ Nhà máy 3 đến khách hàng 3. Giải pháp này cung cấp cho các chi phí tối thiểu là 26.000. Tất cả các hạn chế được hài lòng.

===================================================
Để 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é http://thuthuataccess.com/forum/forum-21.html

Vấn đề phân công

Sử dụng giải quyết trong Excel để tìm sự phân công của người với nhiệm vụ giảm thiểu tổng chi phí.

Xây dựng mô hình

Mô hình chúng ta sẽ giải quyết trông như sau trong Excel.

Vốn đầu tư

Sử dụng Solver trong Excel để tìm ra sự kết hợp của vốn đầu tư nhằm tối đa hóa lợi nhuận.
Xây dựng mô hình


Mô hình chúng ta sẽ giải quyết trông như sau trong Excel.

Bảng dữ liệu

hay vì tạo ra các kịch bản khác nhau , bạn có thể tạo ra một bảng dữ liệu để nhanh chóng thử các giá trị khác nhau cho các công thức. Bạn có thể tạo một bảng dữ liệu biến một hoặc một bảng dữ liệu biến hai.

Giả sử bạn sở hữu một cửa hàng sách và có 100 cuốn sách trong lưu trữ. Bạn bán một số% cho mức giá cao nhất là $ 50 và một số% với mức giá thấp hơn $ 20. Nếu bạn bán 60% cho mức giá cao nhất, ô D10 dưới đây tính toán tổng lợi nhuận của 60 * $ 50 + 40 * $ 20 = $ 3800.

Nhóm các mục của bảng Pivot

Ví dụ này hướng dẫn bạn cách để nhóm một loạt các items trong bảng Pivot. Ở đây, chúng ta tìm hiểu làm thế nào để nhóm các sản phẩm và làm thế nào để nhòm các ngày theo tháng.

Dưới đây bạn có thể tìm thấy một trục bảng. Quay trở lại Pivot bảng để học cách tạo ra bảng pivot này.

Tra bảng giá có nhiều tham số

Tôi có 1 bảng giá thép Excel có nhiều tham số để tra được giá như sau:

Định dạng bảng

Định dạng nhanh một loạt các ô bằng cách chọn một định dạng bảng. Bạn cũng có thể tạo ra định dạng bảng của riêng bạn. Định dạng nhanh một ô bằng cách chọn một định dạng cell.

1. Nhấp vào bất kỳ ô trong tập dữ liệu.

2. Trên tab Home, trong nhóm Styles, nhấn Format as Table, và chọn một kiểu bảng.

Lưu ý: chọn New Table Style để tạo ra phong cách bảng của riêng bạn hoặc nhấp chuột phải một phong cách bảng và nhấn Bản sao tạo ra một phong cách bảng mới tương tự như hiện tại. Sửa đổi một phong cách bảng tùy chỉnh ảnh hưởng đến tất cả các bảng trong một bảng tính sử dụng mà bảng phong cách. Điều này có thể tiết kiệm rất nhiều thời gian.

3. Excel sẽ tự động chọn các dữ liệu cho bạn. Kiểm tra 'bảng của tôi có tiêu đề' và nhấp vào OK.

Kết quả. Đây là một cách khác để chèn một bảng .

Để chuyển đổi bảng này trở lại mức bình thường của các ô (và giữ cho các định dạng), thực hiện theo các bước sau.

4. Trên tab Design, trong nhóm Tools, nhấn Convert to Range.

Kết quả. Một loạt định dạng độc đáo của các ô.

Lưu ý: để loại bỏ các phong cách bảng, lựa chọn phạm vi của các ô, trên tab Home, trong nhóm Styles, nhấn Normal.

===================================================
Để 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é http://thuthuataccess.com/forum/forum-21.html

Tham khảo theo cấu trúc

Khi làm việc với bảng trong Excel, bạn có thể sử dụng tài liệu tham khảo cấu trúc để làm cho công thức của bạn dễ hiểu hơn.

Ví dụ, chúng ta có bảng sau.

Bảng Pivot đa cấp - Multi level

Chúng ta có thể kéo nhiều hơn một field trong bảng pivot. Chúng tôi sẽ xem xét một ví dụ về nhiều hàng, nhiều vùng giá trị và nhiều vùng lọc báo cáo.

Hãy nhớ rằng, tập hợp dữ liệu của chúng tôi bao gồm 214 hàng và 6 vùng. ID đơn đặt hàng, sản phẩm, loại, Số tiền, ngày và Quốc gia.

Phân phối tần số

Bạn có biết rằng bạn có thể sử dụng bảng pivot để dễ dàng tạo ra một phân phối tần số trong Excel? Bạn cũng có thể sử dụng các phân tích ToolPak để tạo ra một biểu đồ .


Hãy nhớ rằng, tập hợp dữ liệu của chúng tôi bao gồm 214 hàng và 6 lĩnh vực. ID đơn đặt hàng, sản phẩm, loại, Số tiền, ngày và Quốc gia.

Biểu đồ trục - Pivot Chart

Biểu đồ trục - Pivot Chart

Một biểu đồ trục là đại diện hình ảnh của một bảng pivot trong Excel. Biểu đồ Pivot và bảng Pivot được kết nối với nhau.


Dưới đây bạn có thể tìm thấy một trục bảng hai chiều. Quay trở lại Bảng Pivot để học cách tạo ra bảng pivot này.

Bộ phân tách - Slicer

Excel 2010 làm cho nó có thể chèn các Slicer nhanh chóng và dễ dàng để lọc các bảng Pivot. Tuy nhiên, bằng cách sử dụng bộ lọc báo cáo cho kết quả chính xác.


Dưới đây bạn có thể tìm thấy một trục bảng hai chiều. Quay trở lại phần Bảng Pivot để học cách tạo ra bảng pivot này.

Cập nhật Pivot Table

Bất kỳ thay đổi bạn thực hiện cho các tập dữ liệu không được tự động chọn của các trục bảng. Làm mới trục bảng hoặc thay đổi các nguồn dữ liệu để cập nhật các bảng trụ với những thay đổi áp dụng.

Làm mới (Refresh)

[Excel 2010] Chức năng Rows to repeat at top bị ẩn

Hỏi: Trong Excel 2010 chức năng Rows to repeat at top bị ẩn giờ sao khắc phục, bác nào biết chỉ dùm em nhé!
Đáp: Vụ này là do bác đang trong chế độ Print. Bác quay lại bảng tính rồi chọn lệnh Page Setup/Page Layout là được
Xem thêm cách thiết lập

Lệnh đếm có điều kiện - CountIF

Lệnh đếm có điều kiện trong Excel hơi rắc rối. Chúng ta nghiên cứu một ví dụ đơn giản sau đây

1.  Ví dụ, chúng ta muốn đếm số lượng tế bào có chứa Google hay Facebook (một cột).

Tính toán trường / Item

ví dụ này hướng dẫn bạn cách chèn một trường tính toán hoặc mục tính theo trục bảng.


Dưới đây bạn có thể tìm thấy một trục bảng. Quay trở lại Pivot bảng để học cách tạo ra bảng pivot này.

Biểu đồ đường - Line Charts

Biểu đồ đường được sử dụng để hiển thị các xu hướng theo thời gian. Sử dụng một biểu đồ đường thẳng nếu bạn có nhãn văn bản, ngày tháng hoặc một vài nhãn hiệu số trên trục hoành. Sử dụng một biểu đồ phân tán (XY ​​biểu đồ) để hiển thị dữ liệu XY khoa học.

Để tạo ra một biểu đồ đường thẳng, thực hiện các bước sau đây.

Biều đồ hình bánh - Pie Chart

Biểu đồ Pie được sử dụng để hiển thị sự đóng góp của mỗi giá trị (slice) với tổng số (bánh). Biểu đồ luôn luôn sử dụng một loạt dữ liệu.

Để tạo ra một biểu đồ cho các loạt số liệu năm 2009, thực hiện theo các bước sau.

1. Chọn dãy A1: D2.

2. Trên tab Insert, trong nhóm Charts, chọn Pie, và chọn Pie.

Biểu đồ dạng Bars

Một biểu đồ Bars là phiên bản nằm ngang của một biểu đồ cột. Sử dụng một biểu đồ thanh Bar nếu bạn có nhãn văn bản lớn.

Để tạo ra một biểu đồ thanh, thực hiện các bước sau đây.

Biểu đồ vùng

Một biểu đồ vùng là một biểu đồ phù hợp với các vùng dưới đường đầy màu sắc. Sử dụng một biểu đồ vùng xếp chồng lên nhau để hiển thị sự đóng góp của mỗi giá trị với tổng số thời gian.

Để tạo ra một biểu đồ vùng, thực hiện các bước sau đây.

1. Chọn phạm vi A1: D7.

2. Trên tab Insert, trong nhóm Charts - Area -> Kiểu Area mong muốn.

Biểu đồ phân tán - Scatter Charts

Sử dụng một biểu đồ phân tán (XY ​​biểu đồ) để hiển thị dữ liệu XY khoa học. Bảng xếp hạng phân tán thường được sử dụng để tìm hiểu nếu có một mối quan hệ giữa biến X và Y.
Straight Lines


Để tạo ra một biểu đồ phân tán, thực hiện các bước sau đây.


1. Chọn dãy A1: D22.


2. Trên tab Insert, trong nhóm Charts, chọn phân tán, và chọn phân tán với các đường thẳng.





Lưu ý: cũng thấy phân tán chủng với dòng mượt mà trên phân tán với các đường thẳng.





Kết quả:





Lưu ý: chúng tôi đã thêm một ngang và dọc tiêu đề trục . Trục x của một biểu đồ phân tán là một trục giá trị, vì vậy bạn có nhiều nhân rộng trên trục lựa chọn (giống như một trục thẳng đứng mà luôn luôn là một trục giá trị).
Chỉ dấu (marker)


Để tìm hiểu xem có một mối quan hệ giữa X (tiền lương của một người) và Y (/ giá xe của mình), thực hiện các bước sau đây.


1. Chọn dãy A1: B10.


2. Trên tab Insert, trong nhóm Charts, chọn phân tán, và chọn phân tán với chỉ dấu.





Kết quả:





Lưu ý: chúng tôi đã thêm một đường xu hướng thấy rõ ràng mối quan hệ giữa hai biến

===================================================

Để 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é http://thuthuataccess.com/forum/forum-21.html

Trục biểu đồ

Hầu hết các loại biểu đồ có hai trục: trục ngang (hoặc trục x) và một trục thẳng đứng (hoặc trục y).


Để tạo ra một biểu đồ cột, thực hiện các bước sau đây.


1. Chọn dãy A1: B7.


2. Trên tab Insert, trong nhóm Charts, chọn cột, và chọn cột Clustered.



Loại trục


Như bạn có thể thấy dưới đây, Excel cũng cho thấy những ngày giữa và 2013/08/24 2013/09/01. Để loại bỏ những ngày này, thay đổi kiểu trục từ trục ngày để trục văn bản.


1. Nhấp chuột phải trên trục ngang, và sau đó nhấp vào Format Axis.





2. Bấm vào trục văn bản.





3. Nhấn Close.


Kết quả:



Đề trục


Để thêm một tiêu đề trục ngang hay dọc, thực hiện các bước sau đây.


1. Chọn biểu đồ. Biểu đồ Công cụ kích hoạt các tab theo ngữ cảnh.


2. Trên tab Layout, nhấn đề Axis.


3. Bấm vào tiểu học dọc trục Tiêu đề, xoay Tiêu đề.





4. Nhập tiêu đề trục thẳng đứng. Ví dụ, lượt truy cập.


Kết quả:



Trục Quy mô


Theo mặc định, Excel tự động xác định các giá trị trên trục thẳng đứng. Để thay đổi các giá trị, thực hiện các bước sau đây.


1. Nhấp chuột phải vào trục thẳng đứng, và sau đó nhấp vào Format Axis.





2. Nhấp vào nút lựa chọn cố định bên cạnh tối đa.


3. Nhập vào giá trị 12000.





4. Click Close.


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é http://thuthuataccess.com/forum/forum-21.html

Data Series

Một hàng hoặc cột của con số được vẽ trong một biểu đồ được gọi là một loạt dữ liệu. Bạn có thể vẽ một hoặc nhiều dòng dữ liệu trong biểu đồ.

Để tạo ra một biểu đồ cột, thực hiện các bước sau đây.

1. Chọn phạm vi A1: D7.

2. Trên tab Insert, trong nhóm Charts, chọn cột, và chọn cột Clustered.

Tạo một Sheet cho biểu đồ

Cho đến nay chúng tôi đã chỉ nhìn thấy bảng xếp hạng trên cùng một bảng tính như các nguồn dữ liệu (bảng xếp hạng nhúng). Tuy nhiên, bạn cũng có thể di chuyển một biểu đồ cho một tờ riêng biệt mà chỉ có một biểu đồ (tấm biểu đồ).

Để di chuyển một biểu đồ để một tấm biểu đồ, thực hiện các bước sau đây.

1. Chọn biểu đồ. Biểu đồ Công cụ kích hoạt các tab theo ngữ cảnh.

2. Trên tab Design, nhấp vào Di chuyển đồ.

Đường xu hướng - Trend Line

Ví dụ này hướng dẫn bạn cách để thêm một đường xu hướng đến một biểu đồ trong Excel.

1. Nhấp chuột phải vào dòng dữ liệu, và sau đó nhấp vào Add Trendline ...