Hiển thị kết quả tìm kiếm từ 1 đến 3 trên tổng số: 3
  1. #1
    Tham gia ngày
    03 2007
    Nơi Cư Ngụ
    TP. HCM
    Bài gởi
    2,025
    Cảm ơn
    2,622
    Được cảm ơn 17,662 lần trong 1,760 bài viết

    Smile Offset truyền kỳ

    Offset truyền kỳ


    Kỳ 1: Giới thiệu hàm Offset


    Công dụng:
    Trả về tham chiếu đến một vùng nào đó, cách một ô hoặc một dãy ô một khoảng cách với số dòng hoặc số cột được chỉ định trước. Chúng ta có thể chỉ định số dòng, số cột của vùng tham chiếu trả về.


    Cú pháp:
    =OFFSET(reference, rows, cols, height, width)

    Trong đó:
    • reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!.
    • rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. Ví dụ nếu rows là 3, sẽ có 3 dòng trả về và nằm bên dưới reference. Khi rows là số dương thì các dòng trả về nằm bên dưới reference, khi rows là số âm thì các dòng trả về nằm bên trên reference.
    • cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. Ví dụ nếu cols là 4 sẽ có 4 cột trả về và nằm bên phải của reference. Khi cols là số dương thì các cột trả về nằm bên phải reference, khi cols là số âm thì các cột trả về nằm bên trái reference.
    • height: là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
    • width: là số cột của vùng tham chiếu cần trả về. Width phải là số dương.
    Lưu ý:
    • Nếu rows và cols làm cho tham chiếu trả về vượt ra ngoài phạm vi của một worksheet, hàm Offset sẽ báo lỗi #REF!
    • Nếu bỏ qua height và width, thì height và width sẽ có kích thước mặc định là height và width của reference.
    • Offset thật sự không di chuyển bất cứ ô nào, cũng không thay đổi bất kỳ phần chọn nào, nó chỉ trả về tham chiếu mà thôi. Chúng ta có thể sử dụng Offset với bất kỳ hàm nào cần đối số là một tham chiếu. Ví dụ, công thức Sum(Offset(C10,1,2,3,1)) sẽ tính tổng các giá trị của một dãy gồm ba dòng, một cột; đây là dãy nằm bên dưới ô C10 một dòng và bên phải ô này hai cột.





    Các ví dụ





    TP.


    Kỳ 2: Offset và Validation List
    Tập tin đính kèm Tập tin đính kèm

  2. Có 148 thành viên cảm ơn TranThanhPhong về bài viết này:

    7thing, anchung79, angelnguyenlinh, angelofmine, binhan2007, bivily, bogay, byby_2323, cachoichien, camchuongdo, chenwentai, chienlh, Chu quyen, coffeeicon, dash177, dauquangvuong, dinhngoccam, dinhphuong9327, dohuuthuc, dudieuwa, dungvovan, duongthanhtu89, duyle86, dvu58, Easywash, eokakoe, excellearner, fangjing_phi, firstknight, Gbco, gia nguyên, hahonghanh, hai08, hanhks, HaNoi_MuaLanhGia, heocon405, hetxay, hoabillynguyen, Hoangiumun, hoangquynh_aof, hoangthuymi, Hoàng Dũ, Hoàng Trọng Nghĩa, hongnhungpx, HuuThanh, huydung257, huyen_htc, huyhoang1768, kha10607, khoa_pr, l01012011, lacbuidoi, lanr, lebinh319, lehoaithanh, lehungnp, lenguyen878, leonardo208, lethuycaylua, lhlaptecco5, LIENTHUYTHANH, linhngoc, linton, loan.dkl, lonelyheart1184, lsxinh, luanvien177, mai1tyeu, maiducbong, maihoangduong, mailinhphong, mandem, mọt sách ham học, Mr Okebab, mrdam188, MrZy, muabui88, mymichau, mytho, namcobain, namphuongson, nddong109, ngoctranphuong, nguenphuc, nguyen duc man, nguyenbaobn88, nguyenhongphong, NguyenHongQuyen, nguyentam2T, nhan_dv, nhung2008, ninhhoanghiep, nmhung49, nonzero, ObserveR, oliverlely, onlylove_th, Pakinac, phamduydn, Phanhanhdai, phnam77, phuyen89, pns56, quangtranevn, quean, quick87, QuocPhong, SAD LOVE, saintpiox, soiconkthn, songvui, sonlamhpu, spttkhai, sumisumo, Tống Văn Đệ, tedaynui, tessuarai, thang_aof_cva, thanhsang5979, theanhhn79, thongbao, ThuNghi, thuthuy0685, tienquen, tieuyeutinh, TKT, topgun, tqforever, trande314, trung78952, truonghaiau08, trxbach, tuan73, tuyphong1983, uthoiemve, vanquangdy, vantan920, vieetj.bq, viendo, Vincentphan, xuan.nguyen82, xuanhien0786, xuannguyen12012, xuan_ha919, yeuthamhangxom

  3. #2
    Tham gia ngày
    03 2007
    Nơi Cư Ngụ
    TP. HCM
    Bài gởi
    2,025
    Cảm ơn
    2,622
    Được cảm ơn 17,662 lần trong 1,760 bài viết

    Smile Offset truyền kỳ

    Kỳ 2: Offset và Validation List

    a. Có bao giờ bạn muốn tạo một Validation List mà nội dung của nó thay đổi theo sự thay đổi của một List khác (danh sách). Bài viết này sẽ hướng dẫn từng bước cách làm:

    Giả sử bạn có danh sách gồm 2 cột: OS (hệ điều hành) và Versions (Phiên bản) (Xem file đính kèm). Bây giờ bạn muốn tạo một Validation List để khi tại ô G3 ta chọn một OS nào đó thì danh sách trong ô H3 sẽ chỉ hiển thị các Versions của OS mà ta đang chọn (xem hình 6).



    Qui trình thực hiện:

    B1. Tạo một danh sách A1:B34 (xem file đính kèm), với 2 cột OS và Versions. Lưu ý một OS có thể có nhiều Version.

    B2. Đặt tên cho vùng A2:A34 với tên là “OS”. Bạn quét chọn vùng A2:A34 | nhấn tổ hợp <Ctrl+F3> | Chọn New… gõ OS vào hộp Name, chọn Scope là Worksheet Offset2 hoặc Workbook đều được và tại Refers to gõ vào =A2:A34 | Nhấn nút OK (Excel 2007). Nếu dùng các phiên bản Excel trước thì sau khi nhấn <Ctrl+F3> là có thể khai báo việc đặt tên vùng và sao đó nhấn nút Add để hoàn thành.



    B3. Tạo một danh sách chứa tên các hệ điều hành được trích ra từ cột OS (danh sách này không trùng lặp). Bạn quét chọn các ô D2:D34 dùng để chứa danh sách kết quả trích lọc không trùng | nhập vào công thức mảng trích lọc sau:

    =IF(ISERR(INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(IND IRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS)))))) ,"",
    INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:" &ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS))))))

    Kết thúc công thức bằng tổ hợp phím <Ctrl+Shift+Enter>. Công thức trên sẽ trích ra các loại hệ điều hành (OS) và mỗi loại chỉ lấy một lần, các dòng trùng sẽ thay bằng “”.



    B4. Sau khi đã có danh sách các OS duy nhất, bạn hãy đặt tên cho danh sách này. Lưu ý, có khả năng có những loại OS khác do vậy ở đây khi đặt tên là dùng hàm Offset để tham chiếu đến vùng dữ liệu động.

    Bạn nhấn <Ctrl+F3> | Chọn New… | đặt tên cho vùng là OSValList tại Name | tại Refers to nhập vào công thức sau;
    =OFFSET(Offset2!$D$2,0,0,COUNTA(Offset2!$D$2:$D$34 )-COUNTIF(Offset2!$D$2:$D$34,""),1)

    Công thức trên giúp trả về vùng tham chiếu các OS duy nhất một cách động.



    B5. Tạo bảng nhỏ như hình bên dưới. Chọn ô G3 và đặt tên là Val1Cell, chọn ô H3 đặt tên là Val2Cell (theo cách đặt tên nêu trên hoặc có thể đặt tên bằng cách gõ trực tiếp vào Name Box).



    B6. Đây là bước quan trọng giúp cho danh sách chọn tại H3 sẽ thay đổi phụ thuộc vào tùy chọn tại G3. Bạn đặt tên cho vùng Versions với tham chiếu trả về thay đổi theo tùy chọn của G3 như sau:

    Nhấn <Ctrl+F3> | chọn New… | tại hộp Name nhập vào tên là Versions | tại Refers to nhập vào công thức sau;

    =OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,OS,0)+1,2, ,,)),0,0,COUNTIF(OS,Val1Cell),1)

    Công thức này giúp chọn ra vùng Versions tương ứng với việc chọn OS tại ô G3.

    B7. Áp Validation cho 2 ô G3 và H3:

    Chọn ô G3 | vào Data | Data Validation | Settings | chọn List | tại Source nhập vào =OSValList | nhấn nút Ok hoàn tất.

    Chọn ô H3 | vào Data | Data Validation | Settings | chọn List | tại Source nhập vào =Versions | nhấn nút Ok hoàn tất.



    Các bước đã hoàn tất, các bạn hãy chọn thử một OS nào đó tại G3 và qua ô H3 xem danh sách Version tương ứng.

    Các cải tiến cho công cụ này chúng ta có thể tiếp tục thảo luận trên GPE.

    TP.
    Tập tin đính kèm Tập tin đính kèm
    thay đổi nội dung bởi: TranThanhPhong, 14-10-08 lúc 01:09 PM

  4. Có 156 thành viên cảm ơn TranThanhPhong về bài viết này:

    2337, @baoquang, anchung79, angelnguyenlinh, anhtitomo, anhtuan1066, bechanga, binhb15dlna, bivily, bodien19, bogay, boyamater, Buon Qua Em Oi, cadafi, camchuongdo, canhsatlangtu113, Cao_Thanh_Ha, Cá ngừ F1, chandatn, Chu quyen, coffeeicon, demonevil, dinhphuong9327, dochoiviet.form, dohuuthuc, dudieuwa, duongmanhquan, duongthanhtu89, duyle86, duynam, dvu58, gau2603, gaucon_angiang, Gbco, gia nguyên, Good-Luck, hangkhunglam, happy_lisa_happy, headbear, heocon405, hetxay, hoabillynguyen, hoangminhtien, hoangthuymi, hoangtuech90, hoangvuluan, Hoàng Trọng Nghĩa, hong gam, hongnhungpx, honka, hostcuibap, HUE2007, hung29kt, HuuThanh, huydung257, iamcuong, johnkeynes, johnnylinhanh, jv251285, kittulip, l01012011, lacbuidoi, lam273, lebinh319, lenova, leonardo208, LeVanVi_UEL, LIENTHUYTHANH, linhngoc, linton, lminhv, logica, luanvien177, luonchinguoc, luong duyen, mai1tyeu, maihoangduong, mailinhphong, MicrosoftExcel, milanodesire, muabui88, muadem, mymichau, namcobain, ndhmoney686, ngdung, ngocmaidaiquoc24483, nguyenhongphong, nguyennhungptit, nhan_dv, nhthung, nhung2008, ninhhoanghiep, oliverlely, ongcusike, onlylove_th, Pakinac, phonglan978, PhongPhan, pns56, Quang Tinh, quangtranevn, quick87, QuocPhong, saintpiox, sendme, silent2692, skulblaka, soiconkthn, songvui, sonlamhpu, son_sajv, sumisumo, tedaynui, TeThienDaiThanh, thaibinh_excel, thaitam87, thangbx, thang_aof_cva, thanhcong2101989, thanhthu2004, Thay, thb.58, TKT, tonggiap, topgun, tqforever, trande314, Trần Văn Bình, trung78952, trungtuanthanh, truonghaiau08, truongthikt, tsf, tuoitrevietnam, tuvuduy, tuyetnhi, tuyphong1983, vanhesing, Vanpham2007, vanquangdy, vieetj.bq, vinhsonghinh, vutienluc, Vũ Trọng Cường, whitemouse, Xuan Phuong, xuan.nguyen82, xuannguyen12012, xuan_ha919, yenbinhta3, yeuthamhangxom, Yin.kn

  5. #3
    Tham gia ngày
    07 2007
    Nơi Cư Ngụ
    Dalat
    Bài gởi
    4,900
    Cảm ơn
    4,386
    Được cảm ơn 21,674 lần trong 3,973 bài viết
    Công thức này:
    =IF(ISERR(INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(IND IRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS)))))) ,"",
    INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:" &ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS))))))
    Nếu đã dùng Excel2007 để làm, thì tại sao không dùng IFERROR:
    =IFERROR(INDEX(OS, SMALL(IF(MATCH(OS, OS, 0) = ROW(INDIRECT("1:" & ROWS(OS))), MATCH(OS, OS, 0), ""), ROW(INDIRECT("1:" & ROWS(OS))))),"")
    thay đổi nội dung bởi: TranThanhPhong, 09-12-08 lúc 09:05 PM Lý do: đề tài đóng


Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 1 người đang xem đề tài này. (0 thành viên và 1 khách)

Đề tài tương tự

  1. Hỏi cách dùng hàm Offset
    Viết bởi feelingyes trong chuyên mục Hàm và công thức Excel
    Trả lời: 9
    Bài mới gởi: 29-04-14, 04:23 PM
  2. Hỏi về Hàm Offset và Validation
    Viết bởi vinhtamqb trong chuyên mục Tìm kiếm, dò tìm và tham chiếu
    Trả lời: 9
    Bài mới gởi: 03-12-13, 08:43 PM
  3. Xin bài tập Hàm Offset và Data-Validation
    Viết bởi nguyenhongphong trong chuyên mục Tìm kiếm, dò tìm và tham chiếu
    Trả lời: 13
    Bài mới gởi: 16-05-12, 03:08 PM
  4. Hỏi về bài tập hàm OFFSET - VALIDATION
    Viết bởi thanhthan1961 trong chuyên mục Hàm và công thức Excel
    Trả lời: 6
    Bài mới gởi: 07-04-11, 06:57 PM
  5. Xin giúp đỡ về hàm Offset và Validation
    Viết bởi tranloc trong chuyên mục Giải thích, gỡ rối, xử lý lỗi công thức
    Trả lời: 0
    Bài mới gởi: 24-09-09, 05:08 PM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]