Thứ Tư, 27 tháng 8, 2014
Thứ Năm, 14 tháng 8, 2014
FUNNY ABOUT EXCEL
FUNNY ABOUT EXCEL
The 3 keys which plays an essential role in our life;
CTRL+ ALT + DEL, like if you agree
HOW TO USE "CONDITIONAL FORMATTING" IN EXCEL
Change background color based on cell value in Excel
When we deal with huge data in Excel, we may want to pick out some specific data and highlight them, such as change their background color. Supposing you have a range of data, and now you need to color the values between 80 and 100, as the following screenshot shows, how could you change background color based on cell value?
Microsoft Excel's Conditional Formatting command can change background color for specific cells based on their values with following steps:
1. Select the range that you will work with;
2. Click the Home > Conditional Formatting > New Rule….
3. In the New Formatting Rule dialog box, select and highlight the Format only cells that contains item in the Select a Rule Type: box.
4. In the Format Only Cells with section, specify the conditions that you need.
- In the first drop down box, select the Cell Value;
- In the second drop down box, select the between;
- In the third and fourth box, enter the filter conditions, such as 80, 100.
5. Click the Format button.
6. In the Format Cells dialog box, set the background color under Fill tab.
7. Click OK button to save the settings. Then the specific cells with value between 80 and 100 are filled with the new background color in the selection. See screenshot:
AUTONUMBER IN EXCEL
HOW TO AUTONUMBER IN EXCEL
TO CREATE AN AUTONUMBER FIELD IN EXCEL, YOU HAVE A FEW OPTIONS - EACH WITH ITS OWN SET OF LIMITATIONS
To create an autonumber field, you could try using the row() function. This function returns the current row number.
The limitations of this option is that if you insert a new row, your row() function will return a different value in the lines below the inserted line. So in this option, you will not be able to keep your primary key static if you are inserting new rows in the middle.
Also, you insert a blank row, you'll need a way to fill in the formula.
To create an autonumber field, you could try using the Offset() function.
In cell A1, enter the number 1.
Then in cell A2, enter the formula:
Then in cell A2, enter the formula:
=OFFSET(A2,-1,0)+1
Then copy the formula from cell A2, down as far as you need.
The limitations of this option is that if you insert a new row, you will need to readjust all of your formulas (below the row inserted). So there is more maintenance involved than option #1.
Also, this option does not allow you to keep your primary key static if you are inserting new rows in the middle.
THE COMMON FUNTION IN EXCEL (PART 1)
Common mistakes in excel:
-
Error#####:
+
Reasons:
Error occurs when the comlumn’s wildth is not enough to display the calculated
results.
+
Fix
by increasing the width of the column or
shrink the font size.
-
Error#VALUE:
+
Reasons:
The users use data or operator, that join the fomula not right with the requires of fomula.
+
Example:
The mathematical formula calculated on the cell containing the text data type.
Fix errors by entering the address or
format the cell containing the data.
-
Error#DIV/0!: Error occurs when a number divided by 0 or the denominator of the calculation is a cell
with no data. Fix errors by Entering a other fomula or other functions.
-
Error#NAME: Error occurs when the excel file is not defined the character in the
fomula.
-
Errors#N/A: Error occurs when the cell doesn’t have data to calculate.
-
Errors#NUM!: Error occurs when using the data not right number type. You need to
reformat the data involved in formula.
Add-in A-Tools
Add-in A-Tools
Add- in A-Tools allows sharing and managing Excel file on LAN, internet. Users can open Excel file in server at the same time for editing and saving. Clients connect to server by TCP/IP protocol (not full share folder - Excel's way), server manage connections with restrict users, setting rights for each user group: read only, edit, limit how many people can open a worksheet,....,When data in spreadsheet is changed then other computers on LAN, internet are updated immediately (after about ~ 1 millisecond).
How can I do with Add-in A-Tools?
1) Share an Excel Workbook over the Network among users. Client connect to server by TCP/IP protocol, login with IP address, Domain
2) Restrict Users to sheets, range address, workbook
3) Restrict Users to edit only certain Cells in that sheet
4) Restrict Users to print, copy sheet
5) Query data on sheet by SQL language. Connect to any database in your computer and link data to sheet
1) Share an Excel Workbook over the Network among users. Client connect to server by TCP/IP protocol, login with IP address, Domain
2) Restrict Users to sheets, range address, workbook
3) Restrict Users to edit only certain Cells in that sheet
4) Restrict Users to print, copy sheet
5) Query data on sheet by SQL language. Connect to any database in your computer and link data to sheet
Add-in A-Tools can create dynamic reports by using the functions to query the database (DB) with T-SQL language. It also allowers users to connect to the internal and external database such as Excel, Access, Foxpro, MS SQL, MySQL andFirebird,... for instance. The "SQL Builder" helps users to create a query formula (with BS_SQL function) easily and efficiently. Users are able to create highly sophisticated worksheets which are filtered according to various conditions and associated with different data sources. In that way, accuracy, stability and high speed are ensured.
A-Tools is an add-in software which runs on Microsoft Excel. A-Tools has the following functions :
Add- in A-Tools allows sharing and managing Excel workbook on LAN, internet. Users to manage Excel workbook powerful on the network, allow connect to the spreadsheet over TCP/IP (not full share folder - Excel's way), manage connections to the data ranges, setting rights for each user group: read only, edit, limit how many people are connected ,....,When data is changed on worksheet then other computers on LAN, internet are updated immediately (after about ~ 1 millisecond).
Add-in A-Tools can create dynamic reports by using the functions to query the database (DB) with T-SQL language. It also allowers users to connect to the internal and external database such as Excel, Access, Foxpro, MS SQL, MySQL andFirebird,... for instance. The "SQL Builder" helps users to create a query formula (with BS_SQL function) easily and efficiently. Users are able to create highly sophisticated worksheets which are filtered according to various conditions and associated with different data sources. In that way, accuracy, stability and high speed are ensured.
The main functions:
- Select a computer with good hardware to Create Server - Excel Server. This machine used for sharing Excel files to clients in LAN, internet. Call it as "Server" or "Host"
- Add Excel files to server for sharing them to clients in LAN, internet
- Share workbook, worksheet/spreadsheet, regions/range in spreadsheet for sharing on the network
- Restrict Groups and Users to access/connect to server
- Restrict rights of users to open sheet, regions/range; modify or read-only; format; copy; print;...
- Manage list of users connected (connections): build black list or connection list, manage user online
- View list of users connected and chatting
- Manage Excel reports on the network (LAN), support commands: Check; Approve; Dis-Approve. Under each command the user is not allowed to copy data, printing, modify report.
- Manage the history Excel files in the server, users can open them again if they have the right.
- Query data on worksheet by SQL language with function BS_SQL() or tool SQL Builder
- Connect to the external database: Excel, Access, MySQL, Firebird,…. Link data to sheet (with function BS_SQL )
- Create dynamic report in Excel with data link to any database in client or server (with function BS_SQL )
- Create statistical formulas with multiple conditions
- Convert text, number to words
- Developers can program VBA for their applications with Add-in A-Tools by reference addinatools.dll. Add-in A-Tools provides APIs function and COM objects as BSNetwork, BSFormulaArray.
Add-in A-Tools running on versions of Excel 2000/XP/2003/2007/2010/2013 or higher (32-bit or 64-bit)
DATA UNTEGRITY BY LIMITING DATE ENTRY ERRORS
Minimize date entry error by using a calendar drop down list
Ensuring data integrity by limiting date entry errors in a Microsoft® Excel® worksheet can easily be achieved by adding a calendar drop down list, so that instead of entering dates manually you can rather select them from the list. With below simple steps, you can add a calendar drop down list using only control buttons and no VBA code.
Applies to: Microsoft Excel 2007, 2010 (32-bit) and 2013 (32-bit)
The screen shot below will be used for this example.
1. Activate the developer tab.
- Select the File tab.
- Select Options.
- Select Customize Ribbon.
- Tick the Developer box and select OK as per the screenshot below.
- The Developer tab will now appear on the ribbon.
- Select the Office button
- Select Excel Options
- Select the Popular tab
- Tick the Show Developer tab in the ribbon checkbox
- The Developer tab will now appear on the ribbon.
2. Select the Developer tab.
3. Select Insert. A drop down list with icons for Form Controls and ActiveX Controls will appear.
4. Select the icon on the bottom right hand corner under the ActiveX Controls.
5. More Controls will appear. Select Microsoft Date and Time Picker Control 6.0 (SP4)
6. Select OK.
7. Your cursor will turn into a crosshair. Click and hold, draw a rectangle in cell C3.
8. Right-click on the Date Control and select Properties.
9. Then type the cell where the date will be displayed next to linked cell as below.
- Ignore any error that you might get by clicking OK on the error message dialog box.
- Close the properties window.
- Select Design Mode to turn it off as per the screenshot below.
10. When you select the date from the drop down menu the date in cell C4 will change.
Ensuring data integrity by limiting date entry errors in a Microsoft® Excel® worksheet can easily be achieved by adding a calendar drop down list, so that instead of entering dates manually you can rather select them from the list. With below simple steps, you can add a calendar drop down list using only control buttons and no VBA code.
Applies to: Microsoft Excel 2007, 2010 (32-bit) and 2013 (32-bit)
The screen shot below will be used for this example.
1. Activate the developer tab.
- Select the File tab.
- Select Options.
- Select Customize Ribbon.
- Tick the Developer box and select OK as per the screenshot below.
- The Developer tab will now appear on the ribbon.
- Select the Office button
- Select Excel Options
- Select the Popular tab
- Tick the Show Developer tab in the ribbon checkbox
- The Developer tab will now appear on the ribbon.
2. Select the Developer tab.
3. Select Insert. A drop down list with icons for Form Controls and ActiveX Controls will appear.
4. Select the icon on the bottom right hand corner under the ActiveX Controls.
5. More Controls will appear. Select Microsoft Date and Time Picker Control 6.0 (SP4)
6. Select OK.
7. Your cursor will turn into a crosshair. Click and hold, draw a rectangle in cell C3.
8. Right-click on the Date Control and select Properties.
9. Then type the cell where the date will be displayed next to linked cell as below.
- Ignore any error that you might get by clicking OK on the error message dialog box.
- Close the properties window.
- Select Design Mode to turn it off as per the screenshot below.
10. When you select the date from the drop down menu the date in cell C4 will change.
Đăng ký:
Bài đăng (Atom)
.jpg)
