Excel IS THE WORLDS MOST USED DATABASE
2013
NBS EUROPE
OUR RULESMobile phones
One speaks other listen
Here and now rule
Rule of confidentiality
Follow the rules
Full cup rule
Our goal:
Learn how to use effectively excel
Excel IS THE WORLDS MOST USED DATABASE
Excel IS THE WORLDS MOST USED DATABASE
Agenda
Rule 80/20RecommendationsExcel functionsTypes of operatorsDate & Time TextMost used and usefullPivot table
Excel IS THE WORLDS MOST USED DATABASE
Rule 80/20Spend 80% of your time for planning and 20% for implementation. Excel IS THE WORLDS MOST USED DATABASE
Excel IS THE WORLDS MOST USED DATABASE : 65 536 256 , : ,
Recommendations
Excel functions
Categories of functions
Types of operators
Types of operators
TODAYDate & Time The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook
Date & Time NOWNOW functionreturns the current system date and time. This function will refresh the date/time value whenever the worksheet recalculates.
Date & Time YEARWe can use other approach Data Text to columns
Date & Time MONTHWe can use other approach Data Text to columns
Date & Time DAYWe can use other approach Data Text to columns
Date & Time WEEKNUMReturn type-The default is 1.
Date & Time
NETWORKDAYSReturns the number of whole workdays between two dates
Date & Time DAYS360Can be used in accounting systems to calculate the number of days between two dates based on a 360-day year (twelve 30-day months).WE use straightforward way of calculationEnd_date - Start_date +1
Date & Time
DATEDIF within listThe DATEDIF function in Microsoft Excel calculates the difference, orinterval, between two dates. This difference can be expressed in a variety of ways. The function takes the form=DATEDIF(Date1, Date2, Interval) IfDate1is later thanDate2,DATEDIFwill return a#NUM!error. If eitherDate1orDate2is not a valid date,DATEDIFwill return a#VALUEerror. TheIntervalvalue should be one of
IntervalUsemMonths: the number of whole calendar months between the two datesdDays: the number of days between the datesyYears: the number of whole calendar years between the datesymMonths In Same Year: the number of months between the two dates if they were in the same yearydDays In Same Year: the number of days between the two dates if they were in the same yearmdDays In Same Month And Year: the number of days between the two dates if they were in the same month and year
Date & Time YEARFRACUsing the YEARFRAC function, you can calculate a precise difference between two dates because unlike other methods that return an integer result, this function returns a decimal result to indicate fractions of a year.0 US NASD 30 day months/360 day years1 Actual days in the months/Actual days in the years2 Actual days in the months/360 days in the years3 Actual days in the months/365 days in the years4 European 30 days in the months/360 days in the years
TEXT Part 2https://www.youtube.com/watch?v=cLHIwEqWVQs
ExactOr we can use formula A1=A2Also it is true for numbersTEXT
TEXT CONCATENATEOr we can use formula A1& A2, in case if we need to separate use Also it is true for numbers
TEXT ReptThe result of the REPT function cannot be longer than 32,767 characters, or REPT returns #VALUE!.
TEXT Left and Right
TEXT Len , . !
TEXT LOWER
TEXT UPPER
TEXT MID
TEXT PROPER
TEXT TRIMIMPORTANTSometimes text values contain leading, trailing, or multiple embedded space characters (Unicode character setvalues 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157) To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.
In one cell you can store 32 000 symbols ISTEXT within list
TEXT Using the IsText function will return True or False after checking the given cell. We can use it with IF function=IF(IsText(A1),This Cell Contains Text.,This Cell Does not have text.) ;this function would return This Cell Contains Text.=IF(IsText(B1),This Cell Contains Text.,This Cell Does not have text.) ;this function would return This Cell Does not have Text.
Most used and useful PART 3
https://www.youtube.com/watch?v=ox20aG8Y3Qg
Lookup&References VLOOKUP
Lookup&References HLOOKUP
ROUNDINT Rounds down a number to the nearest integer
ROUNDMROUND The MROUNDfunctioncan be used to round a number upwards or downwards to a specified multiple ( )
ODD functionrounds a number up to the nearest odd integer. . , .ROUND
=ODD(1,5) 1,5 .3=ODD(3) 3 .3=ODD(2) 2 .3=ODD(-1) 1 .1=ODD(-2) 2 ( 0) .3
EVEN Returns number rounded up to the nearest even integer. , . , . ROUND
AB1 ()2=EVEN(1,5) 1,5 (2)3=EVEN(3) 3 (4)4=EVEN(2) 2 (2)5=EVEN(-1) -1 ( ) (-2)
ROUNDROUNDTheROUNDfunction rounds a number to a specified number of digits. ROUND . RemarksIfnum_digitsis greater than 0 (zero), then number is rounded to the specified number of decimal places. _ 0 (), Ifnum_digitsis 0, the number is rounded to the nearest integer. _ 0, .Ifnum_digitsis less than 0, the number is rounded to the left of the decimal point. _ 0, .To always round up (away from zero), use theROUNDUPfunction. ( ), ROUNDUP.To always round down (toward zero), use theROUNDDOWNfunction. ( ), ROUNDDOWN.To round a number to a specific multiple (for example, to round to the nearest 0.5), use theMROUNDfunction. (, 0,5) MROUND.
ROUNDUP The ROUNDUPfunctionis used to round a number upwards towards the next highest number ROUNDUP ROUND, ,
ROUND
AB1 ()2=ROUNDUP(3,2;0) 3,2 (4)3=ROUNDUP(76,9;0) 76,9 (77)4=ROUNDUP(3,14159; 3) 3,14159 (3,142)5=ROUNDUP(-3,14159; 1) -3,14159 (-3,2)6=ROUNDUP(31415,92654; -2) 31415,92654 (31500)
ROUNDROUNDDOWN Rounds a number down, toward zero. .
AB1 ()2=ROUNDDOWN(3,2; 0) 3,2 (3)3=ROUNDDOWN(76,9;0) 76,9 (76)4=ROUNDDOWN(3,14159; 3) 3,14159 (3,141)5=ROUNDDOWN(-3,14159; 1) -3,14159 (-3,1)6=ROUNDDOWN(31415,92654; -2) 31415,92654 (31400)
ABSReturns the absolute value of a number. The absolute value of a number is the number without its sign.
SUBTOTAL - Math & Trig
SUM
MAX
Number1, number2, ... are 1 to 255 numbers for which you want to find the maximum value.
MINNumber1, number2, ... are 1 to 255 numbers for which you want to find the minimum value.
IFTheIFfunction returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.NOTE: Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions.
SUMIFYou use theSUMIFfunction to sum the values in arangethat meet criteria that you specify.
Pivot table
Excel 2007 Limitations:- Maximum number of unique items in a PivotTable - 1,048,576- Maximum number of pages in a PivotTable - 1,048,576- Maximum number of row or column fields in a PivotTable 65,536- Maximum number column fields in a PivotTable 16,385- Maximum number of page fields in a PivotTable 16,385 (may be limited by available memory)- Maximum number of data fields in a PivotTable - 16,385- Maximum number of calculated item formulas in a PivotTable - Limited by available memory- Maximum number of rows in a PivotTable - 1,048,576
Some rules during Pivot table preparation:The list cannot contain empty rows or columns within the list. The list cannot contain pre-existing totals. The raw list of worksheet data must contain a header (individual column headings) row and that row can only be one row deep
QUESTIONS?
https://www.facebook.com/photo.php?v=249206585244206&set=vb.205879816243550&type=2&theater
** ,
, ,
, , -
, (, , )
- ( )
* *
Top Related