Cập nhật nội dung chi tiết về Export Dữ Liệu Ra Excel Dùng Poi mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Một vấn đề mà tôi nhận được nhiều request nữa đó là làm sao để kết xuất dữ liệu ra Excel nhằm các mục đích báo cáo, tương tác cơ bản người dùng,…
Nhận thấy đó cũng là điều rất cần thiết nên tôi viết bài này nhằm hỗ trợ các bạn gặp khúc mắc vê vấn đề này.
Bạn có thể dùng Eclipse hay IDE nào đó cũng được miễn là bạn reference đến thư viện này. Ở đây tôi dùng Eclipse.
Trong ví dụ này tôi kết nối đến cơ sở dữ liệu Access(bạn có thể khác) để lấy dữ liệu sau đó kết xuất ra excel. Cơ sở dữ liệu này có cấu trúc như sau:
Code của chương trình như sau: Lớp ConnectDB.java
package vovanhai.wordpress.com; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class ConnectDB { private Connection con; public ConnectDB(String accessDBName) throws Exception{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+accessDBName; con=DriverManager.getConnection(url); } /** * Lấy 1 bảng cơ sở dữ liệu * @param tableName là bảng cơ sở dữ liệu cần lấy * @return * @throws Exception */ public ResultSet getTable(String tableName)throws Exception{ String sql="select * from "+tableName; PreparedStatement pstmt=con.prepareStatement(sql); return pstmt.executeQuery(); } /** * Lấy 1 resultset từ câu truy vấn * @param sql là câu truy vấn * @return * @throws Exception */ public ResultSet getTableFromSQL(String sql)throws Exception{ PreparedStatement pstmt=con.prepareStatement(sql); return pstmt.executeQuery(); } /** * Lấy 1 connection * @return */ public Connection getConnection(){ return con; } /** * Đóng kết nối * @throws Exception */ public void CloseConnection()throws Exception{ if(con!=null) con.close(); } }Lớp Export2Office .java
package vovanhai.wordpress.com; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class Export2Office { private Workbook wb; public Export2Office(Workbook wb) { this.wb=wb; } /** * Export 1 result set ra file excel * @param rs là result set cần xuất * @param sheetName là tên của sheet trong workbook * @throws Exception */ public void Export(ResultSet rs, String sheetName)throws Exception{ int nos=wb.getNumberOfSheets(); for (int i = 0; i < nos; i++) { String sn=wb.getSheetName(i); if(sn.equalsIgnoreCase(sheetName)){ sheetName+="$1"; } } Sheet sheet = wb.createSheet(sheetName); ResultSetMetaData rsmd=rs.getMetaData(); Row row = sheet.createRow((short)0); int colNum=rsmd.getColumnCount();//số fields có trong bảng for (int i = 0; i < colNum; i++) { String col=rsmd.getColumnName(i+1); Cell cell=row.createCell(i); cell.setCellValue(col); } while(rs.next()){ row=sheet.createRow(rs.getRow()); for (int i = 0; i < colNum; i++) { String val=rs.getString(i+1); Cell cell=row.createCell(i); cell.setCellValue(val); } } } /** * Lấy tất cả các bảng của cơ sở dữ liệu sau đó export ra thành các sheet trong 1 file excel * @param AccessDBFilePath là đường dẫn tuyệt đối đến file access * @param excelFilePath là đường dẫn đến file excel kết xuất * @throws Exception */ public void ExportAllDBFiles(String AccessDBFilePath,String excelFilePath)throws Exception{ ConnectDB condb=new ConnectDB(AccessDBFilePath); Connection con=condb.getConnection(); DatabaseMetaData dbm=con.getMetaData(); String []types={"TABLE"}; ResultSet rsm= dbm.getTables(null, null, null, types); while(rsm.next()){ String tableName=rsm.getString("TABLE_NAME"); ResultSet rs=condb.getTable(tableName); Export(rs,tableName); } FileOutputStream fos=new FileOutputStream(excelFilePath,true); wb.write(fos); fos.close(); condb.CloseConnection(); } /** * Export 1 table * @param AccessDBFilePath la file access cần export * @param tableName là bảng cần export * @param excelFilePath đường dẫn đến file excel cần tạo * @throws Exception */ public void ExportTable(String AccessDBFilePath,String tableName,String excelFilePath)throws Exception{ ConnectDB condb=new ConnectDB(AccessDBFilePath); ResultSet rs=condb.getTable(tableName); Export(rs,tableName); String fileName=excelFilePath; FileOutputStream fos=new FileOutputStream(fileName,true); wb.write(fos); fos.close(); condb.CloseConnection(); } }Lớp Export2Office_Driver.java
package vovanhai.wordpress.com; import java.io.FileOutputStream; import java.sql.ResultSet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; public class Export2Office_Driver { public static void main(String[] args) throws Exception{ System.out.println("waiting for export..."); long before=System.currentTimeMillis(); Workbook wb=new HSSFWorkbook(); Export2Office ep=new Export2Office(wb); ep.ExportAllDBFiles("school_DB.mdb", "ex1.xls"); Workbook wb2=new HSSFWorkbook(); Export2Office ep2=new Export2Office(wb2); ep2.ExportTable("school_DB.mdb","tblClass", "ex2.xls"); Workbook wb3=new HSSFWorkbook(); ConnectDB condb=new ConnectDB("school_DB.mdb"); Export2Office ep3=new Export2Office(wb3); String sql="select * from tblClass c inner join tblStudents s on s.classID=c.classID "+ "where s.classID='cdth4a'"; ResultSet rs=condb.getTableFromSQL(sql); ep3.Export(rs, "Join"); FileOutputStream fos=new FileOutputStream("ex3.xls",true); wb3.write(fos); fos.close(); condb.CloseConnection(); long after=System.currentTimeMillis(); System.out.println("finished after "+(after-before)+" milliseconds"); } }Chạy thử ứng dụng, sau đó chọn project, nhấn F5 để refresh lại project, ta có kết quả
Mở thử các file excel để thưởng thức nhỉ!
Hướng Dẫn Xuất Dữ Liệu Lớn Ra File Excel Với Thư Viện Apache Poi
Trong bài viết trước, tôi đã hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI. Với lượng dữ liệu ít khoảng vài nghìn dòng trở lại, khi xuất excel và .xlsx chúng ta có thể sử dụng các lớp có tiếp đầu ngữ HSSF, XSSF để xuất dữ liệu ra file excel mà không ảnh hưởng nhiều đến hiệu suất của chương trình. Tuy nhiên, với dữ liệu rất lớn khoảng vài chục nghìn dòng trở lên thì thời gian xử lý sẽ tương đối chậm, tốn nhiều bộ nhớ. May mắn là thư viện Apache POI còn thêm một class khác là SXSSF giúp chúng ta giải quyết vấn đề này.
SXSSF (Streaming version of XSSFWorkbook) là một phần mở rộng API của XSSF, được sử dụng khi xuất các file excel lớn và có bộ nhớ heap sapce hạn chế. Do SXSSF mở rộng từ XSSF nên chỉ hỗ trợ xuất file có phần mở rộng là .xlsx ( Microsoft Excel 2007 trở về sau).
Trong phần tiếp theo của bài này, tôi sẽ hướng dẫn các bạn đọc và ghi file excel sử dụng lớp có tiếp đầu ngữ là SXSSF. Nếu bạn chưa biết cách xuất dữ liệu ra file excel sử dụng thư viện Apache POI thì hãy xem bài viết Hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI.
Để tiện theo dõi, tôi sẽ sử dụng lại ví dụ của bài viết trước, chỉ thay đổi các lớp có tiếp đầu ngữ HSSF, XSSF bằng SXSSF.
Khởi tạo SXSSF
Trước khi đi vào phần ví dụ, chúng ta hãy tìm hiểu cách khởi tạo SXSSF:
SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFWorkbook workbook = new SXSSFWorkbook(50); SXSSFWorkbook workbook = new SXSSFWorkbook(-1);Lưu ý:
rowAccessWindowSize : xác định số lượng hàng (row) có thể được truy cập nhiều nhất thông qua SXSSFSheet.getRow. Khi một hàng (row) mới được tạo ra thông qua SXSSFSheet.createRow và nếu tổng số các bản ghi vượt quá giá trị được chỉ định (rowAccessWindowSize), khi đó hàng (row) với giá trị chỉ mục thấp nhất sẽ được làm mới (flushed) và không thể được truy cập thông qua SXSSFSheet.getRow nữa.
Các thao tác trên SXSSF như: createRow, getRow, autoSizeColumn, … chỉ ảnh hưởng đến các record trong phạm vi rowAccessWindowSize được chỉ định.
Phương thức autoSizeColumn: chỉ tự động điều chỉnh cỡ trong phạm vi rowAccessWindowSize được chỉ định. Để có thể autoSizeColumn đúng trên tất cả các record, cần đánh dấu theo dõi các cột trong bảng để tự động điều chỉnh định cỡ. Việc xác định độ rộng phù hợp nhất cho một ô rất đắt, điều này có thể ảnh hưởng đến hiệu suất chương trình.
sheet.trackColumnForAutoSizing(columnIndex); sheet.trackAllColumnsForAutoSizing();Ví dụ sử dụng SXSSF
Book.java
package com.gpcoder.apachepoi; public class Book { private Integer id; private String title; private Integer quantity; private Double price; private Double totalMoney; public Book() { super(); } public Book(Integer id, String title, Integer quantity, double price) { super(); this.id = id; this.title = title; this.quantity = quantity; this.price = price; } @Override public String toString() { return "Book [id=" + id + ", title=" + title + ", quantity=" + quantity + ", price=" + price + ", totalMoney=" + totalMoney + "]"; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public Integer getQuantity() { return quantity; } public void setQuantity(Integer quantity) { this.quantity = quantity; } public Double getPrice() { return price; } public void setPrice(Double price) { this.price = price; } public Double getTotalMoney() { return totalMoney; } public void setTotalMoney(Double totalMoney) { this.totalMoney = totalMoney; } }WriteExcelUsingSXSSF.java
package com.gpcoder.apachepoi; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; public class WriteExcelUsingSXSSF { public static final int COLUMN_INDEX_ID = 0; public static final int COLUMN_INDEX_TITLE = 1; public static final int COLUMN_INDEX_PRICE = 2; public static final int COLUMN_INDEX_QUANTITY = 3; public static final int COLUMN_INDEX_TOTAL = 4; private static CellStyle cellStyleFormatNumber = null; public static void main(String[] args) throws IOException { final String excelFilePath = "C:/demo/books_large.xlsx"; writeExcel(books, excelFilePath); } SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet sheet = workbook.createSheet("Books"); sheet.trackAllColumnsForAutoSizing(); int rowIndex = 0; writeHeader(sheet, rowIndex); rowIndex++; for (Book book : books) { SXSSFRow row = sheet.createRow(rowIndex); writeBook(book, row); rowIndex++; } writeFooter(sheet, rowIndex); int numberOfColumn = 5; autosizeColumn(sheet, numberOfColumn); createOutputFile(workbook, excelFilePath); System.out.println("Done!!!"); } Book book; for (int i = 1; i <= 5; i++) { book = new Book(i, "Book " + i, i * 2, i * 1000); listBook.add(book); } return listBook; } private static void writeHeader(SXSSFSheet sheet, int rowIndex) { CellStyle cellStyle = createStyleForHeader(sheet); SXSSFRow row = sheet.createRow(rowIndex); SXSSFCell cell = row.createCell(COLUMN_INDEX_ID); cell.setCellStyle(cellStyle); cell.setCellValue("Id"); cell = row.createCell(COLUMN_INDEX_TITLE); cell.setCellStyle(cellStyle); cell.setCellValue("Title"); cell = row.createCell(COLUMN_INDEX_PRICE); cell.setCellStyle(cellStyle); cell.setCellValue("Price"); cell = row.createCell(COLUMN_INDEX_QUANTITY); cell.setCellStyle(cellStyle); cell.setCellValue("Quantity"); cell = row.createCell(COLUMN_INDEX_TOTAL); cell.setCellStyle(cellStyle); cell.setCellValue("Total money"); } private static void writeBook(Book book, SXSSFRow row) { if (cellStyleFormatNumber == null) { short format = (short) BuiltinFormats.getBuiltinFormat("#,##0"); SXSSFWorkbook workbook = row.getSheet().getWorkbook(); cellStyleFormatNumber = workbook.createCellStyle(); cellStyleFormatNumber.setDataFormat(format); } SXSSFCell cell = row.createCell(COLUMN_INDEX_ID); cell.setCellValue(book.getId()); cell = row.createCell(COLUMN_INDEX_TITLE); cell.setCellValue(book.getTitle()); cell = row.createCell(COLUMN_INDEX_PRICE); cell.setCellValue(book.getPrice()); cell.setCellStyle(cellStyleFormatNumber); cell = row.createCell(COLUMN_INDEX_QUANTITY); cell.setCellValue(book.getQuantity()); cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA); cell.setCellStyle(cellStyleFormatNumber); int currentRow = row.getRowNum() + 1; String columnPrice = CellReference.convertNumToColString(COLUMN_INDEX_PRICE); String columnQuantity = CellReference.convertNumToColString(COLUMN_INDEX_QUANTITY); cell.setCellFormula(columnPrice + currentRow + "*" + columnQuantity + currentRow); } private static CellStyle createStyleForHeader(Sheet sheet) { Font font = sheet.getWorkbook().createFont(); font.setFontName("Times New Roman"); font.setBold(true); font.setFontHeightInPoints((short) 14); font.setColor(IndexedColors.WHITE.getIndex()); CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setBorderBottom(BorderStyle.THIN); return cellStyle; } private static void writeFooter(SXSSFSheet sheet, int rowIndex) { SXSSFRow row = sheet.createRow(rowIndex); SXSSFCell cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA); cell.setCellFormula("SUM(E2:E6)"); } private static void autosizeColumn(SXSSFSheet sheet, int lastColumn) { for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) { sheet.autoSizeColumn(columnIndex); } } private static void createOutputFile(SXSSFWorkbook workbook, String excelFilePath) throws IOException { try (OutputStream os = new FileOutputStream(excelFilePath)) { workbook.write(os); } } }Thực thi chương trình trên, một file books_large.xlsx được tạo ra trong thư mục C:/demo như sau:
So sánh hiệu xuất chương trình khi sử dụng SXSSF và XSSF
Để so sánh hiệu suất của chương trình khi sử dụng SXSSF và XSSf, tôi sử dụng lại ví dụ WriteExcelUsingSXSSF ở trên, và ví dụ WriteExcelExample ở bài viết trước.
Trong ví dụ bên dưới, tôi sử dụng lớp StopWatch của thư viện Apache Common Lang để đo thời gian thực thi của chương trình
Bây giờ, hãy tăng số lượng dữ liệu cần xuất ra khoảng 100.000 dòng (thay đổi trong phương thức getBooks). Xem kết quả thực thi của 2 chương trình như sau:
Bây giờ hãy thử xóa bỏ các đoạn code autoresize column ở 2 chương trình, vẫn kiểm tra 100.000 record.
WriteExcelUsingSXSSF:
Kết quả thực thi chương trình trên:
Kết quả thực thi chương trình trên:
Khi cần xuất dữ liệu lớn ra file .xlsx và không có yêu cầu về autoresize column thì nên sử dụng SXSSF để đạt được hiệu suất tốt hơn.
Cám ơn các bạn đã quan tâm và theo dõi bài viết, hẹn gặp lại ở các bài viết tiếp theo.
Hướng Dẫn Cách Tự Động Lọc Dữ Liệu Trong Excel Dùng Vba
Trong bài viết về Hướng dẫn cách lọc dữ liệu trong Excel có bài tập kèm theo, Gitiho đã hướng dẫn các bạn cách sử dụng bộ lọc Filter. Nhưng đó đều là các thao tác lọc hoàn toàn thủ công, bạn phải tác động trực tiếp vào bộ lọc. Việc đó có thể mất nhiều thời gian và kém hiệu quả nếu bạn phải sử dụng bộ lọc liên tục. Trong bài viết này Gitiho sẽ hướng dẫn các bạn cách tự động hóa cho bộ lọc Filter bằng cách sử dụng VBA.
Cách tự động hóa cho bộ lọc Filter bằng cách sử dụng VBA
Cách sử dụng VBA để tự động hóa cho các thao tác thường sử dụng trong Excel rất đơn giản. Bạn có thể áp dụng VBA với bộ lọc Filter như sau:
Bước 1: Mở thẻ View hoặc thẻ Developer trên thanh công cụ (nếu bạn chưa biết cách mở thẻ Developer thì xem hướng dẫn ở đây), sau đó bấm chọn mục Record Macro
Khi đó xuất hiện 1 cửa sổ thông tin về Macro được ghi, bạn có thể sửa lại tên macro tại mục Macro Name, hoặc để tên macro mặc định là Macro1 (số 1 có thể thay đổi sau mỗi lần bạn thực hiện record macro)
Bước 2: Thực hiện thao tác lọc dữ liệu trong cột Nhân viên với 1 tên nhân viên bất kỳ
Bước 3: sau khi lọc xong, bạn chọn lại tới vị trí Record Macro đã thực hiện ở bước 1, lúc này sẽ hiển thị là Stop recording. Bạn hãy bấm vào nút Stop recording đó.
Bước 4: mở cửa sổ VBA (phím tắt là Alt + F11 hoặc bấm vào mục Visual Basic trong thẻ Developer) để kiểm tra xem đã có đoạn code cho thao tác lọc chưa.
Trong cửa sổ Project, tại Workbook bài tập, kiểm tra xem có Module 1 không
Trong đoạn code này, chúng ta chú ý ở mục Criteria1:=”Mai”. Đây chính là điều kiện lọc mà chúng ta đã sử dụng ở bước 2.
Bước 5: Thay đổi điều kiện bằng giá trị tại ô E2
Bạn sửa lại chữ “Mai” bằng Range(“E2”).Value
Kết quả sau khi sửa như sau:
Bước 6: Tiếp tục thực hiện ghi Macro cho thao tác bỏ điều kiện lọc (Clear Filter):
Lúc này chúng ta thu được đoạn code cho thao tác bỏ lọc như sau:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range(“E2”)) Is Nothing Then If Range(“E2”).Value = “” Then Macro2 Else Macro1 End If End If End Sub
Ý nghĩa của đoạn code là:
Xét sự thay đổi trong Worksheet
Nếu có sự thay đổi trong ô E2 thì
Xét trường hợp ô E2 không có dữ liệu thì
Chạy Macro2 (là macro bỏ điều kiện lọc)
Nếu không thì (ô E2 có dữ liệu)
Chạy Macro1 (là macro lọc theo điều kiện giá trị tại ô E2)
kết thúc cấu trúc IF xét điều kiện ô E2 không có dữ liệu
Kết thúc cấu trúc IF xét sự thay đổi tại ô E2
Kết thúc Macro
Như vậy khi bạn thực hiện chọn 1 giá trị tại ô E2, ngay lập tức VBA sẽ chạy Macro1 – là macro lọc dữ liệu. Nếu bạn xóa nội dung trong ô E2, VBA sẽ chạy Macro2 – là macro bỏ điều kiện lọc. 2 việc này hoàn toàn tự động mỗi khi bạn có sự thay đổi tại ô E2.
Tải file kết quả theo địa chỉ: https://bit.ly/3e6Ahjx
Tổng kết
Ngoài ra nếu bạn muốn học VBA trong Excel một cách bài bản, có hướng dẫn cụ thể qua video, có giảng viên hỗ trợ… thì đừng bỏ lỡ khóa học VBAG01 – Tuyệt đỉnh VBA – Viết code trong tầm tay của Gitiho. Chắc chắn bạn sẽ hài lòng khi tham gia khóa học này.
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Sử Dụng Paste Trong Excel, Dùng Paste Special Di Chuyển Dữ Liệu Trong
Paste trong Excel không đơn thuần chỉ là copy từ một dữ liệu nào đó và sao chép sang mộ dữ liệu khác mà việc sử dụng Paste trong Excel đem lại rất nhiều chức năng năng khác nữa. Trong bài viết này sẽ hướng dẫn những ai đang sử dụng Excel cách dùng paste trong Excel và các chức năng đem lại.
Khi học excel, chắc chắn bạn đã trải qua bài học về cách sử dụng Paste trong Excel, và trong đó, chắc hẳn sẽ để ý có một dòng lệnh là Paste Special, vậy Paste Special có khác gì so với Paste thông thường trong Excel mà khi học Excel bạn chưa nắm rõ hay không?
Hướng dẫn sử dụng tính năng Paste trong Excel
Hơn thế nữa, liệu Paste Special liệu sẽ đem lại lợi ích gì cho người sử dụng hay việc sử dụng Excel của bạn có nhanh hơn không, tất cả sẽ được làm rõ với các tính năng mà chúng tôi liệt kê ra sau đây, giúp bạn có cái nhìn mới về việc sử dụng Paste trong Excel.
Bước 4: Bạn thấy toàn bộ bảng đã chuyển sang in đậm và có màu đỏ, như vậy bạn đã hoàn tất quá trình copy định dạng mà không làm mất dữ liệu.
2. Thực hiện phép tính
Khi bạn copy một ô kết quả đã bao gồm các công thức nhập vào, vậy làm thế nào để dòng tiếp theo Excel sẽ tự hiểu là bạn đang tính giống dòng trên mà ban nhập công thức. Tất nhiên chúng ta sẽ không sử dụng cách kéo thả kết quả để hệ thống tự tính toán rồi. Ở đây chúng ta sẽ sử dụng Paste Special.
Bước 1: Như trong đề bài, chúng ta sẽ Ctrl + C(Copy) ô F6 tức là tổng của giá trị đầu tiên
Bước 3: Ngay lập tức chúng ta sẽ thấy kết quả của ô phía dưới đã tự động tính tổng sau khi quy chiếu y hệt dòng đầu tiên.
Nhưng như đã nói, ví dụ trên rất đơn giản và giúp bạn hiểu cách sử dụng Paste trong Excel khi muốn Thực hiện phép tính. Bạn có thể áp dụng chúng cho những bảng tính, cột tính có đơn vị lớn hơn bảng ví dụ trên.
3. Copy chiều rộng của cột
Đây là một trong những cách dùng paste trong Excel khá phổ biến, với cách paste thông thường chúng ta sẽ không thể copy cả chiều dài, rộng của cột mới mà phải tuân theo chỉ số cột mới. Cách để giúp bạn có một bản sao mới y hệt với các bản sao cũ là copy chiều rộng sử dụng Paste Special.
Bước 3: ngay lập tức, bạn sẽ có một bản sao hoàn hảo vô cùng với tính năng trên Paste Special rồi đấy.
4. Hoán đổi dữ liệu
Hoán đổi dữ liệu là cách bạn thay đổi dữ liệu theo chiều khác nhau. Từ chiều ngang sang dọc và ngược lại. Việc hoán đổi được sử dụng với nhiều mục đích, tuy nhiên trong bài viết này chúng tôi sẽ hướng dẫn bạn cách trực quan nhất về công cụ này khi sử dụng Paste trong Excel.
Bước 3: ngay lập tức, bạn sẽ thấy dữ liệu hàng ngang đã được chuyển về dọc rồi đấy, đây quả thực là 1 trong những công dụng rất hay của Paste Special phải không.
Bước 1: Có 2 ảnh với 2 kiểu hiển thị biểu đồ khác nhau, dữ liệu khác nhau. Nhiệm vụ của chúng ta là copy định dạng bên trái sang cho bên phải mà không làm thay đổi giữ liệu bên phải.
Bước 2: Nếu chúng ta sử dụng Copy and Paste thông thường làm, bạn sẽ thấy ở bảng bên phải không thay đổi được định dạng, mà còn bị gộp thêm cả dữ liệu.
Bước 4: Kết quả sẽ như hình dưới, biểu đồ bên phải vẫn giữ nguyên được giữ liệu mà hình dạng đã thay đổi.
6. Thay thế các công thức bằng kết quả đã tính toán
Thật khó để mô tả chính xác cho bạn biết công dụng của cách làm này để làm gì, nhưng bạn hoàn toàn có thể hình dung cách sử dụng Paste trong Excel dưới vị dụ sau đây, liệu nó có gì khác so với thông thường.
Bước 2: Nếu sử dụng cách copy, paste thông thường. Sau khi thực hiện tpanf bộ giá trị trả về #Value! lỗi.
Bước 4: Kết quả chúng ta vừa thay thế một cột trong công thức bằng chính kết quả đã tính, như vậy loại bỏ hoàn toàn công thức trong đợt sao chép và tạo nên tổng mới với công thức vẫn giữ như cũ.
Tính năng này rất hay được sử dụng trong việc thay thế dãy hàm RAND() với giá trị của nó thay vì cho phép công thức này tự tính toán. Tất nhiên RAND() không phải là một hàm được sử dụng nhiều.
7. Copy data Validation
Tính năng Data Validation trong Excel giúp bạn quản lý các cột, bảng dữ liệu rất tốt. Bạn lập ra rất nhiều quy tắc cho chúng nhưng làm thế nào để áp dụng các quy tắc tương tự đó cho một bản tính mới hoặc một cột mới?
Tiếp theo đó là mộ cột có chức danh các phòng ban trong công ty. Đề bài đặt ra làm thế nào để đổi chức danh từ cột này sang cột kia ?
Lưu ý: khi được set Dât Validation, bạn sẽ không thể chỉnh sửa trực tiếp vào ô.
Bước 4: Ngay lâp tức chúng ta đã thấy mọi thiết lập của bảng bên trái đã có thể sử dụng cho bảng bên phải rồi đấy.
8. Bỏ qua một loạt các ô có giá trị trống
Tính năng Skip Blanks, cho phép bạn thay thế giá trị hiện tại trong khi vẫn bỏ qua các ô không có giá trị trong dữ liệu gốc. Như vậy bạn sẽ chẳng cần ô giá trị dữ liệu gốc mà vẫn hoàn thành phép tính, không gây ra lỗi. Mặc dù phương pháp này rất ít khi được áp dụng cũng như rất khó để triển khai trên mô hình lớn, tuy nhiên đây cũng là một trong những tính năng rất hay của Paste Special được bổ sung vào.
Bước 1: Hãy tưởng tượng chúng ta có một bảng dữ liệu với rất nhiều kiểu dữ liệu khác nhau, vậy làm thế nào để copy từ dữ liệu cột này sang cột khác mà không hề gây lỗi cho dù cùng định dạng hay khác định dạng.
Bước 1: Ở đây chúng ta có 1 bàng dữ liệu, yêu cầu ở đây là tham chiếu toàn bộ cột Họ và tên xuống phía dưới, vậy ta sẽ làm thế nào?
Bước 2: Trong qua trình copy bạn sẽ dính không ít lỗi định dạng, các loại kích thước.
Bước 4: Kết quả hiện ra ngay chính là cột họ và tên, mặc dù Paste quy chiếu được áp dụng nhiều hơn cho tính toán nhưng qua ví dụ trên chắc hẳn các bạn đã hiểu thêm các sử dụng công cụ này. Và với các dữ liệu không có sẽ trả về giá trị 0.
10. Xóa văn bản trên web, trên các chương trình khác
Sẽ rất khó chịu khi bạn copy dữ liệu từ web, hay từ các chương trình khác mà phải dữ nguyên định dạng, kích thước của chúng vào bảng Excel. Có một cách giúp bạn loại bỏ toàn bộ các điều trên, bắt dữ liệu copy phải theo định dạng bạn đang để, hoặc ít nhất là theo định dạng Excel.
Bước 1: Ở đây chúng ta có 1 bảng chia ra làm 2 kết quả copy với kiểu thường và kiểu Paste đặc biệt.
Bước 2: Ở kiểu bình thường, văn bản vẫn được giữ nguyên kích thước, Font UTM Avo mà chúng tôi sử dụng trước đó.
Bước 4: Font chữ đã được đặt mặc định trở về Calibri, khác hẳn với kiểu trên.
Vậy tính năng này có ích gì cho ban. Hãy tưởng tượng khi bạn copy một đoạn dài trang web, có thể lên đến vài nghìn chữ với vô số định dạng màu sắc. Rất khó để có thể cho chúng về một quy chuẩn trong Excel. Tính năng này trong Word thì lại rất dễ dàng hơn, do đó công cụ Paste Special sẽ giúp bạn giải quyết việc này.
Bạn đang đọc nội dung bài viết Export Dữ Liệu Ra Excel Dùng Poi trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!