How expert know how to quickly and efficiently work in Excel is a big success "How do people who will result" take over Excel? "

"There is no need for comprehensive learning to master Excel.What is necessary is to manage" tips "practiced by those who give results at work," Eiichiro Misaki "who is" business expert " Efforts to eradicate mistakes & work that speeds up work is introduced with examples of actual use "How does "person who will give results" ride Excel?"is.

"How do" those who will give results "take over Excel?" Is a chic design of navy and gold.

All 271 pages, there is considerable thickness.

It is said that the usage of Excel is summarized focusing on practical documents at the business site such as "presentation material" and "monthly report".

The point of this book is not to cover all the functions of Excel, but to "be able to get over Excel" by limiting it to the Excel technique necessary for business.

The content of the book is like this, it is finished to make it easy to read with full color printing based on blue. First of all, as a result of questionnaire to 100 business people by saying "How business people use Excel?", It is clear that Excel is overwhelmingly used more than other software It is getting. As "External Application Materials Preparation" is ranked first in Excel, in this book, the first chapter summarizes the know-how of "Presentation Materials".

In the question "Failed to fail", such as a bitter failure that everyone has experienced at the time such as "I ended without saving" and "characters were protruding from the cell at the time of printing" , A mistake that occurred because it was not accustomed to the operation of Excel such as "I broke a macro and broke". In "How to use usually doing" introduces the points that are likely to be helpful when using Excel, such as "Table is not stretched horizontally and built vertically", "Color, letters, cells, sheets are color-coded".

From the result of the questionnaire "Small stacking produces big differences", know-how to grab the knack by eliminating failures in Excel and to quickly finish work is called "time-short technique to make Excel more efficient" in "Appendix at the end of the book" It is summarized. "Three shortcut keys you want to memorize for the first time""Overwrite Save: Ctrl + S" "Undo: Ctrl + Z" "Cancel: Escape"We introduce three kinds of basic shortcut commands. Because it can be used for other software made by Microsoft and PC general operation, it is something useful if you master it. In the old version, when you performed "Ctrl + S" operation the file state at that time was fixed and could not be restored, but from Excel 2007 you can use the undo function unless you close the file, so "Ctrl + S once every 5 seconds"Is recommended.

Functions frequently used in Excel are consolidated in the right-click menu, so you can work efficiently by "using tabs at the top of the screen and using the right-click menu".

If you copy the calculation result of the formula and paste it in another cell, you can not display the original numerical value correctly. In that case, display "Pressed by format" screen with "Ctrl + Alt + V" and display "value" You can choose OK.

As a method of saving operation with the mouse, a shortcut for selecting a range of cells with "Shift + Arrow Keys" ......

Remember to shorten the operation of the keyboard and efficiently perform character input Copy the upper cell with "date input" "time input" command, copy the upper cell with "Ctrl + D", copy the left cell with "Ctrl + R" And useful functions, such as shortcut keys that you can not do without knowing that within-a-cell linefeed with "Alt + Enter" peculiar to Excel, are full of functions to improve efficiency. Before using Excel, looking at the Appendix at the end of the book seems to dramatically increase the work speed.

In Chapter 1, "What is the point of easy-to-read materials? - presentation materials", formatting is important in order to create documents that are conveyed to the other party, and pay attention to the two points of "reducing elements" and "reducing the movement of the viewpoint" It is a point to create materials.

There are settings for decorating tables in Excel's functions, but keep the type of ruled lines used in the table within 2 or less ...

It is said that the table becomes easier to read if you use the bold effect for character decoration without using underlines and italics. Since the example of the table created by Excel is listed next to the explanation, there are plenty of tips that you can understand the effect at a glance, and unintentionally mumbling "I see, I see ....".

"Cell joining" that connects a plurality of cells means that when cells are copied, the cells to be pasted are joined and the shape of the table is disturbed or the cell address of the reference destination becomes difficult to understand, so " It is not profitable ". If you want to look like a cell join, select multiple cells, open the formatting screen of the cell with "Ctrl + 1", select "horizontal position" on the "Placement" tab and select "Center with selection" It is possible to display data across the center of the data.

When creating a format, "To automate calculations, automate inputs, and protect sheets" to make work more efficient "Chapter 2" Labor saving document preparation in format - estimate ".

For example, "Formula to automatically calculate the date two weeks after the issue date of the estimate" or ...

A number of practical functions without mistakes that are going to be an immediate fight are introduced, such as "How to calculate the shipping fee and the shipping fee automatically in the region" that seems to be available at Internet mail order.

In chapter 3, "basics of data management is a database format", two kinds of tables are exemplified, and the upper table seems to be easy to read at first glance, but when you manage a large amount of data It is a tabular format in which elements are separated and difficult to use. Therefore, you can see that the table written in "1 case = 1 line" on the lower side is easier to manage a large amount of information.

In addition, in Chapter 4 "What is data aggregation useful for business?", It is important that the task of "aggregating data by dividing data" rather than a simple total of data is important to utilize the collected data for business Description. For example, by separating the sales of products into product categories, you will be able to know which category of products is sold.

Based on the contents so far, in Chapter 5 which summarizes "How to make a monthly report", "In addition to collecting data and collecting it cleanly, in reporting work, adding new information and your own addition It is important to present value with value. "

"When you turn around the page while losing your eyes as if you can say how to add value, ..." To find out important data, you can compare "Determining the ratio (difference)" and "Comparing large and small We only need two approaches of ranking (ranking) ".

In general business, the ratio used to grasp the achievement is "composition ratio", "comparison with the previous", "budget ratio".

For example, using "conditional format" to highlight the percentage of budget relative to last year ... ...

Functions that I did not know, such as the "RANK.EQ" function that calculates the ranking of persons in charge from the sales amount data, as well as the functions that I knew but did not use, "We can use it like this It is easy to understand as "I wonder".

The "address book" of chapter 7 that seems to be usable not only at the business site but also in everyday life is a basic arrangement such as "to place items with frequent reference to the left", "fix heading", "leave update history" The ingenuity of it is introduced. For example, when creating a "member list", if you frequently contact members, place phone numbers and e-mail addresses to the left, examples where you put gender and age on the left side in the case of a marketing list It is shown.

It is a hassle to input kanji notation and phonetic expression separately when entering your name, but using "PHONETIC function" allows you to display the phonetic data that Excel saves in the background Possible.

Also, when the reading indicated by the PHONETIC function is incorrect, you can correct it from the "Edit Furigana" menu, and in addition there are other features which saves time and effort when entering a lot of address data I will.

Double check the items "Name" and "Date of birth" by using the "Delete Duplication" function, "Duplicate Address Book Printed 2 Postcards of the Same ... ..." Even if there is a person with the same name by the same family name, duplication can be confirmed safely without mistake.

In addition to Excel 's know - how, there are also columns in "books by top business people" who love Excel in books. When a column was reading, a heroic who saved and manages every password in a password-added Excel file appeared, it was a person in Trend Micro which is famous for OfficeScan when I read it well as I thought. No way that you had managed passwords with Excel ... ....

The table of contents of "How does" the person who will give results "take over Excel?" Is as follows.

◆ Introduction What do you want to do with Excel?

◆ Chapter 1 What is the point of easy-to-read materials - Presentation materials
· Why formatting is important
Changing the power that the appearance of the table conveys
· Avoid centering other than heading
The letters are on the left, the numbers on the right, and
· Text jams too impair the visibility
Increase the height of the line and set margins above and below the character
Make allowance for column width of one or more characters
· Visualize the structure of the table with ruled lines
Up to two kinds of ruled lines
Lacking vertical rulers and refreshing
· Parent-child relationships of items are indicated by changing columns
Insert new column and make parent-child relationship
Column cell binding is hundreds of dangerous and no interest
· As much as possible the format of the letters
Font type and font size are unified
Headline, subtotal, total boldface letters to guide gaze
Column Do not use underlines or italics
· Four rules to make digits easy to read
Rules Add a comma separating single digits
Rule 2 thousand units, put in one million units
Rule 3 Add a negative value to ▲
Rule 4 Do not attach units to individual data

◆ Chapter 2 Labor-saving document preparation in format - Estimate
· The one that controls the format excels Excel
Automate input and eliminate calculation errors
Three points of format creation
· Basics of mathematics: Subtotal is obtained by "unit × quantity"
Arithmetical operations using arithmetic operators
Copy formulas with AutoFill
Compute only input lines: IF function
Logical operator to create Column condition
· Calculate together by function: SUM function
Efficiently enter functions with autocomplete
The SUM function ignores non numerical values
· Two points to note in calculation of consumption tax
Do not include constants in mathematical formulas and calculate by cell reference
Round a number to 1 yen unit: ROUND function
Calculate tax-included amount by SUM function
· Eliminate input labor and error: Input rule
Preparation: Prepare a product list
Name the product name and cell range of the product list
Select and enter from the drop-down list
Make it possible to input only integers in "Quantity"
· Automatically enter commodity name and unit price: VLOOKUP function
How to specify the VLOOKUP function
Automatically display the product code
· Set the estimated amount field
Display the tax amount in another cell with link paste
Display circle mark in estimated amount
· Prevent omission of date entry
Automatically display today's date: TODAY function
Display date as Japanese calendar
How date display format and serial value works
· Calculate expiration date automatically: DATE / YEAR / MONTH / DAY function
Obtain a date two weeks after issue date
Variation of date calculation
· Finish the format
Apply print settings
Restrict cells that can be entered
· Automatically calculate shipping fee and regional shipping cost
Prepare a price list by prefecture
Prepare the destination field for the destination
Automatically enter shipping fee by prefecture with VLOOKUP function
Categorize case by IF function based on tax amount

◆ Chapter 3 Data Management to Eliminate Work Nukemore - Sales Control Table
· The basics of data management is database format
1 case = drop into one line format
· Easy-to-handle database rules
What are the items necessary for sales management?
Consider the arrangement of the columns considering the efficiency of input
Heading row / heading column is always displayed
Confirm with Tab key and enter smoothly
Reduce key type in input history
Column input history is not displayed?
Copy the above cell with Ctrl + D
· Do not format the last line every time.
Saving power by formatting the entire column
· Fix data without using automatic input
Automatic input leads to falsification of sales
Paste data from purchase order confirmation with value
Reduce the trouble of Copipe by devising Column purchase order confirmation
· Extract necessary data: Filter
Check items that have not been delivered
Column Clear filter extraction condition
Extract target data for invoice issuance
Check the progress with Column function

◆ Chapter 4 Calculation & Aggregation of Mono in Numbers - Data Calculation Table
· What is data aggregation useful for business?
Simple sum is useless for business
· Find the sum for each classification: SUMIF / SUMIFS function
Tally by product: SUMIF function
Column Specify the entire column in the cell range
By product · Monthly by month: SUMIFS function
Create a row of sales month from the delivery date
Perform crosstabulation with SUMIFS function
Display unit 'month' in heading cell
Column Relative reference · Absolute reference · Complex reference
· Specify a part of the product name as a condition with a wild card
"?" Represents one character, "*" represents an arbitrary character string
Tips for designating conditions using wildcards
Link wildcard and cell address with '&'
· Add a subtotal line to the schedule
Move the schedule to new sheet
Use the SUBTOTAL function to summarize subtotal rows
Add an outline and grab a figure
Column Cancel outline
· Find the number of cases by classification: COUNTIF / COUNTIFS function
Find the number of orders for each item: COUNTIF function
Find the number of orders by product / month: COUNTIFS function
· Calculate total from aggregation sheet for each viewpoint: 3D tally
Tally data of the same cell address of multiple sheets
Column Type Ctrl + Enter to type

◆ Chapter 5 Data Analysis for Beginners - Monthly Report
· How to quickly summarize report materials
Standardize the analysis and devote time to prepare the supporting data
· Two approaches to find important data
Emphasizing important data is reported
· Three ratios to see the magnitude of numbers
Examine business contribution by composition ratio
Examine business growth / decay compared with last year
Investigate the deviation from the plan based on the budget ratio
· Collect noteworthy data by sorting in one place
Leave serial numbers on the left end column
Arrange items in descending order of sales composition
Column Exclude total amount from sort
Find good customers by gender
· Ask sales ranking: RANK.EQ function
Ask sales person for sales ranking
· Make important data stand out in conditional form
Make a cell with a budget ratio less than 100%
Column Cancels conditional formatting
Emphasize top 20% good customers
Use Column conditional format limitedly
Visualize the amount of money in the data bar
Troubleshooting chart creation
Rebuild the table for graphing
Create stacked column charts by person in charge

◆ Chapter 6 Date Calculation to Automate Personnel Salary Payment - Work Management Table
· Grasp the time calculation tips on the work management table
Time and time can not be calculated simply
· Clarify the date by adding a day of the week
Automatically display day of week from date
Set display format with day of the week in the date
· Seek workday for one day
Calculate actual working time at "work-out - attendance - break"
Excel expresses time in decimal
Round up work hours by 10 minutes: CEILING function
Prevent the occurrence of calculation error: TIME / HOUR / MINUTE function
Prevent the display of errors: IFERROR function
· Seek total working hours
Total time: SUM function
Display number of hours longer than 24:00 in display format
· Ask for salary and withholding tax
Get salary with "total working hours × 24 × hourly wages"
Calculate withholding amount: ROUND function
· Request monthly transportation fee and payment amount
Calculate working days: COUNT function
Obtain monthly transportation expenses by "number of working days × transportation expenses"
Obtain monthly salary by "salary amount - withholding tax amount + monthly transportation expenses"
· Automatically create a monthly calendar
Generate the first day of the month from year and month: DATE function
Generate consecutive dates starting from the first day of the month
Hide the date of the next month
· Finish the format
Limit input value to time by input rule

◆ Chapter 7 Processing to Maximize Data - Address Book
· What is the basic of addressable address book?
Manage strings in database format
· Arrange arrangement of items
Position items with frequent reference to the left
Always display headlines fixed
Establish a row of update date to keep information freshness
Display month and day as two digits to eliminate unevenness
· Functions you want to use to save input
Convert address from zip code
Automatically display phonetic: PHONETIC function
Column How can I correct a wrong phonetic?
Extract postal code from address
Column Mystery of postal code not converted
Automatically switch IME on / off
Use list entry
· Separate prefectures and remaining addresses
Retrieve prefecture from address: IF / LEFT / MID function
Extract the remaining address to another cell: SUBTITUTE function
Fix the calculation result and delete unnecessary columns
Keep a copy of the Column sheet
· Unify notation by substitution function
Unify name delimiters in single-byte space
Use of replacement functions of Column SUBTITUTE function
· Delete duplicate data securely
Delete overlap by double check of name and birth date
· Automatically update your age
Find age from birth date: DATEDIF / TODAY function
Find age from age: FLOOR function

◆ Appendix Appendix Turning Excel Efficiently - Timely Techniques
· Three shortcut keys you want to remember first
Once every 5 seconds, Ctrl + S
If it fails Ctrl + Z
'Escape after all' is Escape
· Reduce switching of tabs as much as possible
Most commonly used functions can be executed from right click
· Select a format and copy and paste only necessary parts
Paste "value" to remove formatting, fix calculation result
Saving time and effort by pasting "Format"
Link cells with "link paste"
· Move and select cells without using mouse
Use Ctrl + arrow keys to move through the table
Shift + arrow keys to select a range of cells
Combine Ctrl + Arrow and Shift + Arrow
Select entire table / whole sheet with Ctrl + A
· Improve work efficiency by devising screen display
Ctrl + wheel scales the screen display
Display the windows side-by-side in a table you want to see at the same time
· Improve work efficiency by devising character input
Enter today's date / current time easily and accurately
Shortcut key to copy the next cell
Use a shortcut key to make a new line in a cell

◆ First-class business person's use of Excel
· The business people I learned from 100 questionnaires use Excel
· Eitaro Kono [Deloitte Tohmatsu Consulting Limited Liability Company] Determine the path to problem solution with risk management table
Masafumi Ota [One of Japan's leading IT companies] Write and manage the schedule of the project for each task
· Masaya Takahashi [Trend Micro Co., Ltd.] Operate smartly by consolidating to Excel

◆ Conclusion How was the ride on Excel?

For the price at Amazon, the Kindle version is 1530 yen. How do you survey Excel for "people who produce results"? Work textbook BOOKS e-book: Misaki Eiichirou: Kindle store

in Review,   Software, Posted by darkhorse_log