“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
KINH NGHIỆM
SỬ DỤNG MỘT SỐ HÀM CỦA PHẦN MỀM MICROSOFT EXCEL
TRONG THỐNG KÊ SỐ LIỆU PHỔ CẬP GIÁO DỤC
1
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
A. MỞ ĐẦU
I. ĐẶT VẤN ĐỀ
1. Thực trạng của vấn đề đòi hỏi phải có giải pháp mới để giải quyết
Khi mới được phân công đảm nhận phụ trách công tác phổ cập bậc trung học
cơ sở, bậc trung học, được quý thầy cô đi trước hướng dẫn bằng cách kẻ bảng trên
giấy và đếm theo phương pháp thủ công từng số liệu cần thiết của mỗi thôn, mỗi đối
tượng khác nhau, sau đó tính tổng các số liệu. Phương pháp này có nhược điểm là dễ
sai sót, khi đã sai một đối tượng hay một độ tuổi thì chúng tôi phải kẻ bảng khác và
đếm lại từ đầu nhưng số liệu cuối cùng cũng chưa đảm bảo chính xác.
Tiếp đến, chúng tôi lại áp dụng phương pháp lọc đếm trong Excel: Sử dụng
lệnh AutoFilter. Phương pháp làm này có ưu điểm là chúng ta lọc và đếm được số
liệu từng nội dung một cách chính xác và nhanh hơn phương pháp đếm thủ công
thông thường, nhưng có một số nhược điểm là thao tác nhiều và không lưu được dữ
liệu, khi có sai sót ta phải lọc đếm lại, tốn nhiều thời gian.
Sau đó, chúng tôi lập các bảng tính, thiết lập hàm trong các bảng tính ở mỗi
trang phổ cập để thống kê số liệu của trang đó mà khi thay đổi các thông tin về một
cá nhân học sinh thì bảng tính này vẫn thống kê chính xác các nội dung điều tra,
nhưng khi thay đổi số lượng đối tượng cần thống kê thì phải chỉnh sửa lại hàm. Do đó
phải chỉnh sửa hàm ở từng độ tuổi và vì vậy có sự bất tiện khi sử dụng cho nhiều năm
và khó áp dụng cho các đơn vị trường khác.
2. Ý nghĩa và tác dụng của giải pháp mới
Khi chưa áp dụng phương pháp mới vào công tác xử lí số liệu phổ cập, chúng
tôi rất vất vả với số liệu báo cáo hàng năm, tốn nhiều thời gian và huy động nhiều cán
bộ, giáo viên tham gia vào công tác thống kê tổng hợp nhưng kết quả vẫn thiếu chính
xác, số liệu biểu bảng chênh lệch giữa năm trước và năm điều tra, số liệu bậc trung

động đối tượng học sinh trong độ tuổi vào trường hoặc nâng cao chất lượng để đảm
bảo tỉ lệ học sinh tốt nghiệp… mà phổ cập giáo dục còn đòi hỏi làm tốt công tác điều
tra, cập nhật, tổng hợp số liệu dân số, độ tuổi, trình độ văn hóa… và cả thiết lập hồ sơ
đầy đủ, chính xác, khoa học theo quy định. Đây là khía cạnh tưởng chừng như đơn
giản nhưng thực tế rất phức tạp và là yếu tố quyết định đến kết quả công tác phổ cập
giáo dục của mỗi nhà trường, mỗi địa phương. Nếu mỗi nhà trường thực hiện điều
tra, cập nhật, tổng hợp số liệu chính xác để báo cáo kịp thời thì ban chỉ đạo phổ cập
giáo dục các cấp có nhiều thuận lợi trong việc xây dựng kế hoạch và chỉ đạo hoạt
động chung của công tác phổ cập.
Để công tác phổ cập đạt kết quả, ngoài công tác huy động, duy trì sĩ số và nâng
cao chất lượng, phải làm tốt công tác điều tra, cập nhật, tổng hợp số liệu để hoàn
thành các biểu mẫu thống kê. Việc thiết lập các biểu mẫu thống kê thời gian qua còn
gặp nhiều khó khăn, mất nhiều thời gian nhưng vẫn thường xuyên sai sót.
Công tác điều tra, cập nhật và xử lý số liệu, báo cáo số liệu phổ cập giáo dục
của xã Ân Hữu từ bậc trung học cơ sở đến bậc trung học, cách làm trước đây khi thực
hiện tính số liệu các biểu mẫu thường đếm thủ công nên có nhiều hạn chế, mất nhiều
thời gian, dễ sai sót và khi thực hiện còn gặp không ít khó khăn:
Một là: Trường chưa có cán bộ chuyên trách làm công tác phổ cập mà có cán
bộ giáo viên kiêm nhiệm công tác phổ cập, không thể dành nhiều thời gian để thực
3
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
hiện thường xuyên; không trực tiếp giảng dạy THPT nhưng phải làm điều tra, cập
nhật số liệu phổ cập bậc trung học nên gặp nhiều trở ngại trong công tác điều tra, cập
nhật thường xuyên các số liệu.
Hai là: Trong quá trình điều tra và thống kê, xử lý số liệu còn nhiều sai sót, số
liệu không khớp giữa các năm, một khi đã sai thì phải tính lại từ đầu, tốn rất nhiều
thời gian.
Ba là: Khi nhập số liệu mới hoặc thay đổi thông tin trong một trang của sổ phổ
cập hoặc điều tra năm mới thì buộc tính lại tất cả.
Những khó khăn trên đây đã dẫn đến việc cập nhật, tổng hợp, xử lí số liệu

- Tạo địa chỉ tương đối, thiết lập các hàm ở các trang phổ cập và các bảng phụ
để đếm các số liệu mà khi thay đổi số đối tượng cần thống kê ở các trang phổ cập ta
không cần phải chỉnh sửa hàm. Do đó, chỉ cần lập hàm một lần ta có thể dùng thống
kê số liệu tương ứng với nhiều độ tuổi, nhiều thời điểm, nhiều đơn vị trường khác
nhau.
- Thiết lập hàm ở các mẫu thống kê (chẵn hạn mẫu 1, mẫu 2, mẫu 3 bậc trung
học) để lấy số liệu cần thiết từ các số liệu đã tính ở các trang phổ cập và các bảng
phụ.
II. MÔ TẢ GIẢI PHÁP CỦA ĐỀ TÀI
1. Thuyết minh tính mới
a) Tạo địa chỉ tương đối
 Mục đích của việc tạo địa chỉ tương đối
- Khi thống kê học sinh phổ cập khó khăn lớn nhất là chọn vũng dữ liệu cần
thống kê, bởi vì nếu chỉ cần chọn thiếu một dòng trong bảng tính sẽ dẫn tới sai lệch
kết quả thống kê cuối cùng. Địa chỉ tương đối là một giải pháp giúp cho người dùng
có thể chọn vùng dữ liệu một cách đầy đủ ngay cả khi thêm hay bớt dữ liệu vào một
trang phổ cập.
- Địa chỉ tương đối là một ô chứa địa chỉ của vùng cần chọn do đó khi thực
hiện các công thức khác trên các mẫu của trang phổ cập người thiết lập công thức chỉ
cần chọn vào ô địa chỉ tương đối là có thể chọn được vùng dữ liệu cần chọn chính vì
thế sẽ rất dễ dàng sao chép công thức không phải mất thời gian thay đổi vùng dữ liệu,
chỉ cần thay đổi tên sheet của trang phổ cập vùng dư liệu sẽ tự động thay đổi.
Ví dụ: Khi thống kê tổng số học sinh trong diện phổ cập độ tuổi 14 (Năm sinh
1998, Hình 1) làm theo cách thông thường ta dùng hàm
= COUNTA('14'!G7:G103); G7 là dòng đầu tiên và G103 là dòng cuối của
một trang phổ cập.
5
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
Hình 1
=COUNTA('15!G7:G135) để thống kê số học sinh thuộc diện phổ cập độ tuổi

Công thức cụ thể các ô D2, E2, …U2 (Hình 1):
="14!"&ADDRESS($A$2,COLUMN())&":"&ADDRESS($C$2,COLUM
N())
Khi đó bảng tính dữ liệu sẽ có kết quả như sau:
Hình 2
- Làm tương tự với các sheet còn lại để có địa chỉ tương đối cho tất cả các cột
trong bảng tính một trang của sổ phổ cập.
b) Tạo công thức cho mẫu số 1
 Tính tổng số học sinh thuộc diện phổ cập
7
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
- Cách thực hiện
Dùng hàm:
= COUNTA(INDIRECT(ô chứa địa chỉ vùng cần lấy dữ liệu))
- Công dụng của hàm này là sẽ đếm tất cả các ô không phải là ô trống, do đó
những học sinh trong diện phổ cập được cập nhật trong bảng tính sẽ được đếm vì ô
chứa địa chỉ tương đối chỉ là một chuỗi ký tự ghi địa chỉ của vùng cần chọn do đó để
chuyển thành địa chỉ thực sự của vùng cần chọn ta cần dùng hàm INDIRECT. Công
dụng của hàm này là sẽ chuyển đổi chuổi kí tự giống như địa chỉ của một vùng hay
một ô thành địa chỉ thự sự của ô hay vùng đó. Ngoài ra để tiện dụng ta chỉ cần chép
công thức vừa tính và thay đổi tên sheets để đếm số học sinh thuộc diện phổ cập ở
các độ tuổi khác nhau theo yêu cầu của mẫu 1 (Hình 3).
Hình 3
- Hàm tính cho từng nội dung
Trên cơ sở địa chỉ tương đối tạo từ từng trang phổ cập ô G2 là cột chỗ ở hiện
nay, ô D2 là cột nữ, ô T2 là cột khuyết tật, chết, chuyển đi ta lập các hàm sau:
8 A B C D E
9
Độ
tuổi

trong độ tuổi
Nữ
Số khuyết tật, chết,
chuyển đi
10
1 2 3 4 5
11 14 1998 97 1 3
12 15 1997 128 61 6
13 16 1996 131 61 2
14 17 1995 137 77 3
15
Tổng 15-17
396 199 11
16
18 1994 142 68 0
17 19 1993 222 118 10
18 20 1992 161 68 7
19 21 1991 196 88 12
20
Tổng 18-21
721 342 29
Ở trên ta đã tính các cột 3, 4, 5.
Tương tự như cách trên các nội dung còn lại ta thực hiện hàm đếm có điều kiện
đang học lớp nào, của độ từng độ tuổi hoặc bỏ học…
Công thức tổng quát: =COUNTIF(INDIRECT('sheet'!$cột$hàng),"lớp") ở
đây cột và hàng là địa chỉ tương đối ta đã lập trong từng trang (độ tuổi), lớp ở đây là
điều kiện ta cần tính.
Ví dụ: Tính học sinh đang học lớp 11 của độ tuổi 16 (năm sinh 1996) tại ô K13
ta thực hiện hàm: =COUNTIF(INDIRECT('16'!$K$2),"11")
9

thời theo từng thôn nên cần phải tạo ra bảng phụ để có thể tính một cách dễ dàng theo
yêu cầu của mẫu thống kê.
- Cách thực hiện
+ Dùng hàm đếm có điều kiện COUNTIF để đếm các đối tượng thuộc diện
phổ cập của tùng thôn với vùng điều kiện là cột thôn trong bảng một trang của sổ phô
cập.
+ Cấu trúc hàm countif như sau: = Countif(vùng điều kiện; điều kiện). Ở đây
vùng điều kiện được sử dụng bằng địa chỉ tương đối do đó ta cần dùng hàm indirect
để chuyển từ địa chỉ tương đối sang địa chỉ của Excel.
+ Công thức áp dụng trong bảng phụ này được minh họa cụ thể như sau:
=COUNTIF(INDIRECT('14'!$G$2);'mau 2'!$B10). Công thức này dùng để tính
kết quả số học sinh thuộc diện phổ cập thôn Hà Đông ở độ tuổi 14.
+ Sao chép công thức theo cột để tính cho các đối tượng thuộc diện phổ cập
các thôn khác trong độ tuổi 14.
+ Sao chép công thức và thay đổi tên sheet cho các độ tuổi còn lại.
- Kết quả
11
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
Sau khi sao chép công thức và đổi tên sheet cho hợp lý thì bảng phụ 1 có kết
quả như sau: Hình 5
Hình 5
Khi có được bảng phụ 1 thì việc tính tổng số đốii tượng phổ cập từ 15-18 đơn
giản là việc sử dụng hàm SUM.
 Tạo bảng phụ 2 để tính số học sinh chết chuyển đi
- Mục đích
+ Trang thống kê yêu cầu tính số học sinh chết hoặc chuyển đi được ghi chú
trong trang phổ cập theo từng thôn và độ tuổi là từ 15-18 đối với bảng 2 và 18-21 đối
với bảng 3.
+ Vấn đề là cần phải thống kê với cùng lúc 2 điều kiện là có ghi chú chết hoặc
chuyển đi và có thôn theo yêu cầu. Do đó với Excel 2003 ta chỉ có thể dùng mà mảng

chứa điều kiện 2=điều kiện 1&điều kiện 2;1; “”)) Sau đó nhấn tổ hợp phím
Ctrl+Shift+ Enter để tính số học sinh tốt nghiệp THCS; THPT.
+ Công thức cho tính số học sinh 15 tuổi tốt nghiệp THCS năm 2012 thôn Hà
Đông như sau:
=SUM(IF(INDIRECT('15'!$G$2)&INDIRECT('15'!$L$2)='mau 2'!
$B10&S$8;1;""))
+ Công thức tính cho số học sinh 18 tuổi tốt nghiệp THPT năm 2012 thôn Hà
Đông như sau: =SUM(IF(INDIRECT('18'!$G$2)&INDIRECT('18'!
$M$2)='mau3'!$B10&'mau3'!$C$8;1;""))
13
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
Sao chép công thức và thay đổi tên sheets cho phù hợp.
- Kết quả Tốt nghiệp THCS (hình 7)
Sau khi sao chép công thức thì kết quả của bảng phụ 3 như sau:
Hình 7
- Kết quả đối tượng tốt nghiệp THPT (hình 8)
Hình 8
14
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
 Tạo bảng phụ 5 để tính số học sinh bỏ học độ tuổi 15-21
- Mục đích
Dùng để tính số học sinh bỏ học được đánh dấu x trong 1 trang sổ phỏ cập ở
các độ tuổi.
- Cách thực hiện
Dùng hàm mảng để tính tương tự như các bảng phụ ở trên với cột điều kiện là
cột bỏ học và điều kiện là “x”. tức có đánh dấu x vào cột bỏ học.
- Kết quả thực hiện
Thực hiện sao chép công thức và thay đổi tên sheet tương tự như ở các bảng
phụ bên trên ta có kết quả bảng phụ 5 như sau:
Hình 9

Hà Đông
=COUNTI
FS(INDIR
ECT('14'!
$G$2);
$B10;INDI
RECT('14'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('15'
!$G$2);
$B10;IND
IRECT('1
5'!
$U$2);"*"
)
=COUNTIF
S(INDIREC
T('16'!
$G$2);
$B10;INDIR
ECT('16'!
$U$2);"*")
=COUNTI
FS(INDIR
ECT('17'!
$G$2);
$B10;INDI
RECT('17'

$G$2);
$B10;INDI
RECT('21'!
$U$2);"*")
Liên Hội
=COUNTI
FS(INDIR
ECT('14'!
$G$2);
$B11;INDI
RECT('14'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('15'
!$G$2);
$B11;IND
IRECT('1
5'!
$U$2);"*"
)
=COUNTIF
S(INDIREC
T('16'!
$G$2);
$B11;INDIR
ECT('16'!
$U$2);"*")
=COUNTI
FS(INDIR

)
=COUNTI
FS(INDIR
ECT('21'!
$G$2);
$B11;INDI
RECT('21'!
$U$2);"*")
Hội Nhơn
=COUNTI
FS(INDIR
ECT('14'!
$G$2);
$B12;INDI
RECT('14'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('15'
!$G$2);
$B12;IND
IRECT('1
5'!
$U$2);"*"
)
=COUNTIF
S(INDIREC
T('16'!
$G$2);
$B12;INDIR

$B12;IND
IRECT('2
0'!
$U$2);"*"
)
=COUNTI
FS(INDIR
ECT('21'!
$G$2);
$B12;INDI
RECT('21'!
$U$2);"*")
Xuân Sơn
=COUNTI
FS(INDIR
ECT('14'!
$G$2);
$B13;INDI
RECT('14'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('15'
!$G$2);
$B13;IND
IRECT('1
5'!
$U$2);"*"
)
=COUNTIF

=COUNT
IFS(INDI
RECT('20'
!$G$2);
$B13;IND
IRECT('2
0'!
$U$2);"*"
)
=COUNTI
FS(INDIR
ECT('21'!
$G$2);
$B13;INDI
RECT('21'!
$U$2);"*")
17
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
Phú Văn 1
=COUNTI
FS(INDIR
ECT('14'!
$G$2);
$B14;INDI
RECT('14'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('15'
!$G$2);

S(INDIRE
CT('19'!
$G$2);
$B14;INDI
RECT('19'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('20'
!$G$2);
$B14;IND
IRECT('2
0'!
$U$2);"*"
)
=COUNTI
FS(INDIR
ECT('21'!
$G$2);
$B14;INDI
RECT('21'!
$U$2);"*")
Phú Văn 2
=COUNTI
FS(INDIR
ECT('14'!
$G$2);
$B15;INDI
RECT('14'!
$U$2);"*")

'!
$U$2);"*"
)
=COUNTIF
S(INDIRE
CT('19'!
$G$2);
$B15;INDI
RECT('19'!
$U$2);"*")
=COUNT
IFS(INDI
RECT('20'
!$G$2);
$B15;IND
IRECT('2
0'!
$U$2);"*"
)
=COUNTI
FS(INDIR
ECT('21'!
$G$2);
$B15;INDI
RECT('21'!
$U$2);"*")
Coäng
=SUM(K10
:K15)
=SUM(L1

được độ chính xác cao.
Có thể áp dụng tại những đơn vị khác nhau với kích thước các trang phổ cập
khác nhau và tên đơn vị khác nhau. Người dùng chỉ cần thay đổi tên đơn vị là có thể
áp dụng thực tế tại đơn vị mình.
Người sử dụng cũng có thể thêm hay bớt tên học sinh tùy ý mà không sợ làm
sai lệch dữ liệu thống kê.
18
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
 Nhược điểm
Với phương pháp này đòi hỏi cần phải nhập dữ liệu một cách đồng nhất nếu
không excel sẽ hiểu sai và thống kê sai ví dụ trong excel kí tự “ HÀ ĐÔNG” sẽ được
hiểu khác với kí tự “Hà Đông” hay “hà đông”.
Ngoài ra để có thể thực hiện thống kê được các dữ liệu kiể số trên trang phổ
cập cần được chuyển đổi thành kiểu chuỗi để có thể sử dụng hàm countifs.
Những nhược điểm trên cũng là những điểm đáng lưu ý cho người sử dụng
trong quá trình nhập liệu vào trang phổ cập.
e) Lưu ý
Để đảm bảo công thức được sử dụng đúng sau khi thiết lập xong nên khóa các
ô chứa công thức lại và chỉ để lại những những ô cần nhập liệu để đảm bảo công thức
không bị sai lệch.
Đối với bậc trung học cơ sở ta thực hiện tương tự như bậc trung học.
2. Khả năng áp dụng
Khi vận dụng đề tài “Kinh nghiệm sử dụng một số hàm của phần mềm
Microsoft Excel trong thống kê số liệu phổ cập giáo dục” đã giúp chúng tôi tránh
được những sai sót trong báo cáo số liệu, không mất nhiều thời gian. Đề tài này vận
dụng vào công tác phổ cập bậc trung học xã Ân Hữu có hiệu quả nhất vào các năm
2011, 2012. Có thể xem đây là mẫu nhập thông tin “Một trang của sổ phổ cập” để các
đơn vị khác nhau nhập thông tin theo đúng thực tế của đơn vị mình hoặc các thông
tin của các độ tuổi khác nhau đều cho ra kết quả thống kê số liệu đúng.
Số lượng các hàm Excel vận dụng trong Đề tài này tương đối ít, chỉ cần lập

tài này vẫn cần được nghiên cứu, hoàn thiện hơn, đem lại nhiều tiện ích và hiệu quả
cao hơn. Bên cạnh đó chúng tôi cố gắng thực hiện xây dựng hàm kiểm tra, đối chiếu
số liệu để đảm bảo tuyệt đối chính xác.
Để phát huy hiệu quả của Đề tài “Kinh nghiệm sử dụng một số hàm của phần
mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”, chúng tôi xin kiến
nghị với lãnh đạo phòng GD&ĐT Hoài Ân triển khai áp dụng rộng rãi, từ đó có nhiều
sự đóng góp, xây dựng giúp phương pháp này hoàn thiện hơn, góp phần vào thực
hiện có hiệu quả công tác phổ cập giáo dục hàng năm./.
20
“Kinh nghiệm sử dụng một số hàm của phần mềm Microsoft Excel trong thống kê số liệu phổ cập giáo dục”
TÀI LIỆU THAM KHẢO
1. Dữ liệu phổ cập bậc trung học xã Ân Hữu từ năm 2007 đến năm 2012.
2. Báo cáo tổng kết công tác phổ cập GDPT năm 2011 và Kế hoạch công tác
phổ cập GDPT năm 2012 của Ban chỉ đạo phổ cập giáo dục huyện Hoài Ân.
3. Sổ phổ cập bậc THPT xã Ân Hữu năm 2011, 2012.
4. Các biểu mẫu thống kê phổ cập bậc THPT xã Ân Hữu năm 2011, 2012.
5. Tài liệu thủ thuật Excel.
6. Tài liệu hàm Excel cơ bản.
21