蝸牛日記 1

為自己做個記錄，也為自己打打氣。當個每天學一點做一點的蝸牛。

08/05/2011	Microsoft Excel Tutorial for Beginners #1 - Overview
Tip:	To create a chart: Highlight main portion of data and upper/left titles(not including total rows/columns, then click on Insert Chart.
08/06/2011	Microsoft Excel Tutorial for Beginners #2 - Get Started
Tip:	To edit a excel field: use F2 key; To jump back from end of one row to the beginning of next row: click on "Enter" key.
	A Guide to the Business Analysis Body of Knowledge
Tip:	Business analysis planning and monitoring how business analysts plan the tasks and activities for business analysis. It covers stakeholder analysis, selecting an approach to managing issues, risks and requirements; deciding how to monitor and report on requirements activities; and negotiating how to manage change on a project. Elicitation(啟發式) how business analysts work with stakeholders to help them understand their requirements within the scope of a project. It covers eliciting requirements; brainstorming; analysing documents; running focus groups; analysing system interfaces; interviewing; observing; prototyping; facilitating requirements workshops; reverse engineering existing systems; and collecting input via surveys and questionnaires. Requirements management and communication how business analysts ensure that the project team and stakeholders stay in agreement on project scope. It covers communicating requirements; resolving conflicts; gaining formal approval; baselining and tracking requirements through to implementation. Enterprise analysis how business analysts take a business need: define that need; identify gaps in current capabilities that stop that need being met; then if change is required, to propose an approach and scope for finding a solution and building the case to justify the work. It explores assessing business architecture; undertaking capability gap analysis; feasibility studies; defining the solution scope; and developing a business case. Requirements analysis how business analysts work with the whole project team towards defining a solution that should meet the agreed requirements. It covers documenting and analysing business, stakeholder, functional, and non-functional (quality of service) requirements; modelling the business domain using process diagrams, flowcharts, data models; exploring behaviour models using use case, user experience design, storyboards, wireframes, user profiles and user stories; and finally verifying and validating requirements. Solution assessment and validation how business analysts assess proposed solutions to help the stakeholders select the solution which best fits their requirements, and once selected how the business should prove that the solution meets those requirements and ultimately whether the project has met its objectives. It covers evaluating alternate solutions, quality assurance processes, support through implementation, and post-implementation reviews. Underlying competencies covers the leadership, problem solving, and communication skills; business and technical knowledge that support effective business analysis. Techniques defines a range of specific skills, methods, and tools that enable the business analysis tasks in the six knowledge areas — there are 34 techniques listed in the Techniques section, with a further 15 defined within the knowledge areas.
08/07/2011	Microsoft Excel Tutorial for Beginners #3 - Calculations
Tip:	To set a field formula: use "="+(highlight the first field)+(key in operator)+(highlight the second field); To make the rest of rows use same formula: highlight the first cell that has formula settled, mouse on the right bottom corner of that cell, then double click. To make sum of a column: mouse on the sum cell ( assume other impact data are at same column above ), then click on "Σ".
	5 Steps to Becoming a Business Analyst
08/08/2011	Microsoft Excel Tutorial for Beginners #4 - Functions
Tip:	To set a field formula 2: use "="+(type in function name)+(enter"(")+(highlight the impacted fields)+(enter")"); To make the rest of columns use same formula: highlight the first cell that has formula settled, mouse on the right bottom corner of that cell, then drag to right side. To set a field formula 3: click on "fx"key at menu bar, then choose function name, then highlight impacted cells.
08/09/2011	Microsoft Excel Tutorial for Beginners #5 - Number Formats
Tip:	Accounting format cell: $    135.00; Currency format cell:        $135.00. To copy format from one to others: highlight the cell that has format settled,then click on "Format Painter" icon, then highlight the cells that you want to format, click on "Paste" icon. To increase/decrease decimal: highlight the cell you want to format, click on "Increase Decimal" or "Decrease Decimal" icon. To join the selected cells and center the label in new cell:  highlight the related cells, and click on "Merge & Center" icon.
08/10/2011	Microsoft Excel Tutorial for Beginners #6 - Formatting Pt.1
Tip:	To copy format from one to others 2: highlight the cell that has format settled,then double click on "Format Painter" icon, that way the format is locked. Then highlight the cells that you want to format, the new cells will automatically being formated. To set label center in both horizontal and vertical way: right click the cell you want to format, choose "Format Cells" icon, then go to "Alignment" tab, you may set label center in both horizontal and vertical way under "Text alignment".
08/11/2011	Microsoft Excel Tutorial for Beginners #7 - Formatting Pt.2
Tip:	For empty cells that's not suppose to have data: add a dash in that empty cell, and center it. To set cell with different format ( text/background color ) due to content: set via "Conditional Formating" icon. , choose operator, enter compare value, then choose selected color
08/12/2011	Microsoft Excel Tutorial for Beginners #8 - Formatting Pt.3
Tip:	To set cell with different format ( text/background color ) due to content 2: set via "Conditional Formating" icon., enter "=" + "(formula name, ie: min/max/count etc ) + "(highlight affected fields)", then choose selected color.
08/13/2011	Microsoft Excel Tutorial for Beginners #9 - Charts Pt.1
Tip:	To insert a chart: Highlight the involved columns of data(including titles, then click on Insert tab -> Columns -> ( select the chart you need ). To set background color/background image for chart cell: Highlight the portion you want to format, right click to choose "Format Data Label" or "Format Plot Area" or "Format Chart Area". Fill -> "Gradient Fill"/"Picture or texture fill" are the ones you may want to play with. You may also want to add column top data so user get an idea each column means. Always keep in mind that somebody will have to look at the chart and understand it, so the best thing you can do is to keep the chart nice and simple. Don't make it complicated, so when somebody sees the chart, they will say "Yes, I understand it."
08/15/2011	Microsoft Excel Tutorial for Beginners #10 - Charts Pt.2
Tip:	To copy the data from cell above: CTRL + apostrophe key ( ' ). To move your chart to different worksheet in the same Excel file:Right click the right top of the chart, choose "Move Chart" option, then choose where you want the chart to be placed. In this example, choose "New Sheet", then enter "Rental Column Chart" as new worksheet name, done.
08/15/2011	Microsoft Excel Tutorial for Beginners #11 - Charts Pt.3
Tip:	To highlight two columns that next to each other and make a Pie chart: First of all, highlight first cell of column A, press "Shift" key and drag down to highlight the rest cells of column A, release. Click on "CTRL" key, then highlight first cell of column B, and drag down to highlight the rest cells of column B. Then click on Insert -> Pie chart. To add Pie percentage:Right click on pie, choose "add data labels" option, it will show data inside the pie. Then right click on the pie again, choose "format data labels", uncheck "value", and choose "percentage" checkbox, then change label position to wherever you want. To enlarge Pie size: click on outside end of pie, it will show a square, then drag corn of square to enlarge pie size.
08/16/2011	Microsoft Excel Tutorial for Beginners #12 - Printing
Tip:	To print the chart: Simplely select the chart, and click on print. ( You may see from Print Preview that it's print only the chart, not the tables above ) To adjust print format:Click on Print -> Page Setup, there are a couple of things you may play with. 1. Page tab -> Scaling, adjust to 140%. 2. Margins tab -> Center on page, check both Horizontally and Vertically. 3. Header/Footer, create custom header and footer. To print part of the worksheet: select the parts you want to print, then click on "Print". At "Print What" option, choose "Selection", click on "OK". (You can confirm it via Print Preview to ensure that).
08/17/2011	Microsoft Excel Tutorial for Beginners #13 - Updating Calculations
Tip:	What if the formula cells didn't get updated properly? One of the possibilities is you have a "-" ( means "Data N/A") for one of the formula cells, and that prevent formula cells below from re-calculating. To fix that: 1. ( In Excel New version ) Top menu -> Formulas -> Calculation Options -> make sure "Automatic" option is checked. ( In Excel old version ) Top menu -> Tools -> Options -> Calculation -> make sure "Automatic" option is checked. 2. ( In Excel New version ) Do nothing. (Can't find related setting) ( In Excel old version ) Top menu -> Tools -> Options -> Edit -> make sure "Extend list formats and formulas" option is checked. 3. Remove "-" from all formula cells ( formula cell only, not table data), set proper formula to them, then add new row data, you will see now all formula data is properly re-calculated. Double click on each formula cell to confirm the blue box range. To mannually fix the "formula cell not re-calculate issue":Double click on the formula cell, it will show a blue box around the involved table data, drag the bottom right corner of the blue box to cover the missing new rows.
08/19/2011	Microsoft Excel Tutorial for Beginners #14 - Percentages and Absolute References
Tip:	What if the percentage cells didn't get updated properly? To set the percentage formula for whole column, you can't simply enter "="+(select a cell)+"/"+(select total value)+(click on % icon), then hold bottom right corner and drag down. It will create invalid value/formula. You need to set the first percentage cell like this: "="+(select a cell)+"/+(select total value)+(click on % icon)+(press F4 key), then hold bottom right corner and drag down, this time there we are. That's called Absolute References.
08/19/2011	Microsoft Excel Tutorial for Beginners #15 - Percentages - More Examples
Tip:	(Same as above)What if the percentage cells didn't get updated properly? To set the percentage formula for whole column, you can't simply enter "="+(select a cell)+"/"+(select total value)+(click on % icon), then hold bottom right corner and drag down. It will create invalid value/formula. You need to set the first percentage cell like this: "="+(select a cell)+"/+(select total value)+(click on % icon)+(press F4 key), then hold bottom right corner and drag down, this time there we are. That's called Absolute References.
08/20/2011	Microsoft Excel Tutorial for Beginners #16 - Using The 'IF' Function
Tip:	To create a if condition in Excel? Use formula "=" + "if (" +(select first cell) + "<"+(select second cell )+","+"(double quotes around value_if_true)"+","+"(double quotes around value_if_false)"+")". In another words, =If(logical_test,[value_if_true],[value_if_false]). Another if condition example is: =if("A5=100%,"SOLD OUT","Seats Available"). What if there is a emtpy cell between if-condition cells prevent me from drag and drop copying?select a cell that has your "if condition" settle down, then copy(Ctrl+C or right click, all works), and paste(Ctrl+V or right click) to new cells, then drag and drop.
08/21/2011	Microsoft Excel Tutorial for Beginners #17 - Using Nested 'IF' Functions
Tip:	To create a nested if condition in Excel? Use formula =if(logic_test,[value_if_true],If(logical_test,[value_if_true],[value_if_false])). Another if condition example is: =if("A5 =B5,"TIE", if(A5 < b5,"supplier A","supplier B")) In Excel 97, 2000 and XP, you can do up to 7 layers of If-Conditions. In Excel 7, you can do up to 64 layers of If-Conditions.
08/22/2011	Microsoft Excel Tutorial for Beginners #18 - Nested 'IF' Functions Pt.2
Tip:	Nested function doesn't work as you expected? Compare below two formula: =IF(C7=150,"Sold Out",IF(C7>99,"Last Few Seats","Promote")) =IF(C8>99,"Last Few Seats",IF(C8=150,"Sold Out","Promote")) The first formula works great, but the second formula failed when you enter 150 at C8 cell. The reason is: it falls into "Last Few Seats" and stop checking the rest of formula. Excel check formula from left to right, once it found something match, it stop execute the rest of formula check. Keep in mind, when you create the Nested function, when you're testing numeric values, to put them in correct order, otherwise they simply won't work. The best way is to shift on the highest value first, then move on to the lowest value.
08/23/2011	Microsoft Excel Tutorial for Beginners #19 - COUNTIF and SUMIF Functions
Tip:	CountIf function: =countif(range,criteria) Ex:  If you want to get "Total count of Movies above 12 rentals", then the formula is: =countif(C2:C7,">12") Note: C2:C7 is just a dumn number. It means you highlight the "movie rentals" column data. SumIf function: =sumif(range,criteria,[sum_range]) Ex: If you want to get "Total Revenue of Movies above 12 rentals", then the formula is: =sumif(C2:C7,">12",D2:D7) Note: C2:C7 is just a dumn number. It means you highlight the "movie rentals" column data; D2:D7 is also dumn number. It means you highlight the "movie income" column data. CountIf function can be found in "Insert" -> Function -> Statistical SumIf function can be found in "Insert" -> Function -> Math & Trig
08/25/2011	Microsoft Excel Tutorial for Beginners #20 - Date & Time Pt.1 - Custom Format
Tip:	Enter a date at current year: enter month and day, hit Enter, Excel automatically add year and change format to its setting. Enter a date at future or past year:enter month, day and last 2 digits of the year, hit Enter, Excel automatically add year and change format to its setting. No matter which format/way you enter the date, Excel will automatically change to it's preset format. To copy the date format to other cells, use format painter to copy and paste. Example of set custom date format: Check Excel "Help" for details by searching "custom date format". To set today's date(a fixed date): CTRL + semicolon key To set today's date(change everyday): Enter formula "=today()"
08/25/2011	Microsoft Excel Tutorial for Beginners #21 - Date & Time Pt.2 - Date Calculations
Tip:	Each data related to a number in Excel, that way we can do date Calculations: simply switch between "Date" and "Number" format. To count Days-to-Go:enter function: "="+(select first date)+"-"+(select second date)+(if the second date is a shared fixed cell, then hit F4 key to lock the formula), then hit Enter.
08/26/2011	Microsoft Excel Tutorial for Beginners #22 - Date & Time Pt.3 - IF & Conditional Format
Tip:	What if Days-to-Go is a passed date? There are a couple of ways to handle that. 1)(Not recommend) Set a conditional format, if date 1 later than date 2, then set result cell data to be WHITE color. 2) Set formula: "="+(select first date)+"-"+(select second date)+(if the second date is a shared fixed cell, then hit F4 key to lock the formula) +"<0","Passed",(select first date)+"-"+(select second date)+"if the second date is a shared fixed cell, then hit F4 key to lock the formula)+")" ex: =IF(A7-$E$3<0,"Passed",A7-$E$3) How to set a Birthday Planner/Reminder:Give out Name/Birthday/Days-to-Go column w/ data. 1) Select all table data (not including title) 2) Click on "Condition Format". 3) Add first rule: "Use a formula to determine which cells to format", then enter formula: =$C4<2, choose related text font color/bold/background color(red), hit OK. 4) Add second rule: "Use a formula to determine which cells to format", then enter formula: =$C4<8, choose related text font color/bold/background color(yellow), hit OK. 5) Add third rule:"Use a formula to determine which cells to format", then enter formula: =$C4<15, choose related text font color/bold/background color(green), hit OK. 6) Adjust the order of rules, make sure "red" rule was hit first, then "yellow" rule, then "green" rule. 7) Change birthday data to test resule. Note: $C4 is set by selecting first Days-to-Go cell (which is $C$4), then manually remove "$" in between. $C4 means the C column data( row data varies).
08/27/2011	Microsoft Excel Tutorial for Beginners #23 - Date & Time Pt.4 - Time Calculations & Formatting
Tip:	Why time calculation could be wrong? Excel store time(ex: hour) information as fraction of the day. Ex: 8:30 (hours) * 10 ( rate ), you might get a wrong value, because Excel store 8:30 hours as 0.354166667. You may change hour cell format to "General" to confirm this. To solve this: You need to times 24 to get correct result. Ex: 8:30 (hours) * 24 * 10 (rate) =  $85. How to copy time formula:Mouse on the right bottom corner of a settled cell, then drag down. If you don't want to coyy formula, but want to copy the data inside ( keep all data same ): Mouse on the right bottom corner of that example cell, hold CTRL together, then drag down.
08/29/2011	Microsoft Excel VLOOKUP Function Tutorial Part 1
Tip:	VLOOKUP function: =VLOOKUP(lookup_value, table_array,col_index_num, [range_lookup]) VLOOKUP function helps automatically pop up lookup table content by entering primary key. The rest of work is simply copy formula to rest of rows by click and drag. How:  "=VLOOKUP("+(select primary key, ex: 2001)+","+(select whole lookup table w/o header)+(press F4 key to lock the lookup table)+","+(the index of column you wish to pop up from lookup table, ex: 2) + ")". Note: The primary key has to be sorted as A-Z or 0-9, or the formula won't work. What if the primary key IS NOT SORTED as A-Z or 0-9? The solution is to set range_lookup as false at the end of the formula instead of leave it blank. How: "=VLOOKUP("+(select primary key, ex: 2001)+","+(select whole lookup table w/o header)+ (press F4 key to lock the lookup table)+","+(the index of column you wish to pop up from lookup table, ex: 2)+","+"false)". Set the range_lookup as false is to find the exact match only. That way, no matter how you sort lookup table, the main data won't get mess.
08/29/2011	Microsoft Excel VLOOKUP Function Tutorial Part 2
Tip:	Another way to set VLOOKUP function: 1. Select the lookup table w/o header. 2. Right click to "Name a range", ex: Hardware. 3. Select the cell you want to put VLOOKUP formula. 4. Top Menu "Formula" -> Lookup & Reference -> VLOOKUP. Function arguments window pops up. 5. Select primary key as lookup_value. 6. At table_arrage field, press F3 key to pop up all Paste Name, ex: select Hardware, click on OK. 7. Enter Col_index_num, ex: 2. 8. Enter Range_lookup, ex: false. 9. Click on OK.
08/30/2011	Microsoft Excel VLOOKUP Function Tutorial Part 3
Tip:	How to incorporate the IF and ISBLANK function to make your VLOOKUP result more user friendly: When you have blank cell in your main table, and there is no related match in lookup table. You will want to show "-" for those related columns. Ex: =IF(ISBLANK(B10),"-",VLOOKUP(B10,Grades,2)) Note: Leave the Range_lookup as default ( true ) there, it means - find within the range. If you set it as false, that means exact match.
08/31/2011	Microsoft Excel Repeat (REPT) Function Tutorial
Tip:	REPT Function: =REPT(text,number_times) ex: =REPT("I",B10)  -- If B10 value is 10, then the formula cell will repeat string "I" 10 times. REPT function can be found in Formula -> Text -> REPT.