Kursus Microsoft Excel 2013 Belajar Formula dan Fungsi.
Materi yang dipelajari meliputi :
Count
Count cells between dates
Count cells between two numbers
Count cells equal to
Count cells equal to case sensitive
Count cells equal to either x or y
Count cells equal to one of many things
Count cells greater than
Count cells less than
Count cells not equal to
Count cells not equal to x or y
Count cells that are blank
Count cells that are not blank
Count cells that begin with
Count cells that contain either x or y
Count cells that contain errors
Count cells that contain five characters
Count cells that contain negative numbers
Count cells that contain numbers
Count cells that contain odd numbers
Count cells that contain positive numbers
Count cells that contain specific text
Count cells that contain text
Count cells that do not contain
Count cells that do not contain errors
Count cells that end with
Count if row meets internal criteria
Count if row meets multiple internal criteria
Count if two criteria match
Count matches between two columns
Count sold and remaining
Count total matches in two ranges
Count unique numeric values in a range
Count unique numeric values with criteria
Count unique text values in a range
Count unique text values with criteria
Count unique values in a range with COUNTIF
Count visible rows in a filtered list
Count visible rows only with criteria
Countif by weekday
COUNTIFS with multiple criteria and OR logic
Group numbers into ranges with COUNTIFS
Running count of occurrence in list
Summary count by month with COUNTIFS
Summary count with COUNTIF
Summary count with percentage breakdown
Two-way summary count with COUNTIFS
Sum
3D SUMIF for multiple worksheets
Calculate running total
Subtotal by color
Subtotal invoices by age
Sum bottom n values
Sum by group
Sum by month
Sum columns based on adjacent criteria
Sum entire column
Sum every nth column
Sum if begins with
Sum if between
Sum if by year
Sum if cell contains text in another cell
Sum if cells are equal to
Sum if cells are not equal to
Sum if cells contain an asterisk
Sum if cells contain both x and y
Sum if cells contain either x or y
Sum if cells contain specific text
Sum if date is between
Sum if date is greater than
Sum if ends with
Sum if equal to either x or y
Sum if greater than
Sum if less than
Sum if multiple criteria
Sum if not blank
Sum top n values
Sum visible rows in a filtered list
Sumif by weekday
SUMIFS with multiple criteria and OR logic
Average
Average last 5 values
Average numbers
Average numbers ignore zero
Average top 3 scores
Weighted average
Min and Max
Maximum if multiple criteria
Maximum value
Maximum value if
Minimum if multiple criteria
Minimum value
Minimum value if
nth largest value
nth largest value with criteria
nth smallest value
nth smallest value with criteria
Conditional formatting
Conditional formatting dates overlap
Conditional formatting gantt chart
Conditional formatting gantt chart weekends
Highlight blank cells
Highlight cells that begin with
Highlight cells that contain
Highlight cells that end with
Highlight cells that equal
Highlight column differences
Highlight data by quartile
Highlight dates between
Highlight dates greater than
Highlight dates in same month and year
Highlight dates in the next N days
Highlight dates that are weekends
Highlight duplicate rows
Highlight duplicate values
Highlight entire rows
Highlight every other row
Highlight integers only
Highlight rows that contain
Highlight rows with dates between
Highlight unique values
Highlight values between
Highlight values greater than
Highlight values not between X and Y
Highlight values that don’t exist
Lookup
Approximate match with multiple criteria
Basic INDEX MATCH approximate
Basic INDEX MATCH exact
Calculate grades with VLOOKUP
Calculate shipping cost with VLOOKUP
Count missing values
Dynamic lookup table with INDIRECT
Exact match lookup with INDEX and MATCH
Exact match lookup with SUMPRODUCT
Extract multiple matches into separate columns
Faster VLOOKUP with 2 VLOOKUPS
Find missing values
Get address of lookup result
Get employee information with VLOOKUP
Get first match cell contains
Get first non-blank value in a list
Get first text value in a list
Get nth match with INDEX / MATCH
Get nth match with VLOOKUP
Group arbitrary text values
Group numbers with VLOOKUP
If cell contains one of many things
Look up data in separate worksheet
Lookup up cost for product or service
Lookup with variable sheet name
Map inputs to arbitrary values
Map text to numbers
Match first error
Max if criteria match
Merge tables with VLOOKUP
Partial match against numbers with wildcard
Partial match with VLOOKUP
Position of first partial match
Position of max value in list
Related info for max value in range
Self-contained VLOOKUP
Sum lookup values using SUMIF
Two-way lookup with INDEX and MATCH
Two-way lookup with VLOOKUP
VLOOKUP by date
VLOOKUP with 2 lookup tables
VLOOKUP with numbers and text
VLOOKUP with two or more criteria
VLOOKUP without #N/A error
If
If cell begins with x, y, or z
If cell contains
if cell contains this or that
If cell equals
If cell is blank
If cell is greater than
If cell is not blank
If cell is this OR that
If cell is x or y and z
If else
If NOT this or that
If this AND that
Invoice status with nested if
Nested IF function example
Round
Get decimal part of a number
Get integer part of a number
Get number at place value
Round a number
Round a number down
Round a number down to nearest multiple
Round a number to n significant digits
Round a number to nearest multiple
Round a number up
Round a number up to nearest multiple
Round a number up to next half
Round a price to end in .99
Round by bundle size
Round time to nearest 15 minutes
Round to nearest 1000
Round to nearest 5
Date and Time
Add business days to date
Add days exclude certain days of week
Add workdays to date custom weekends
Calculate date overlap in days
Calculate days remaining
Calculate elapsed work time
Calculate years between dates
Convert date string to date time
Convert date to Julian format
Convert date to month and year
Convert date to text
Convert hours to time
Convert minutes to time
Convert seconds to time
Convert text to date
Convert time to time zone
Count day of week between dates
Create date range from two dates
Custom weekday abbreviation
Date is same month
Date is same month and year
Date is workday
Days in month
Display the current date
Display the current date and time
Extract date from a date and time
Extract time from a date and time
Get age from birthday
Get date from day number
Get day from date
Get day name from date
Get days before a date
Get days between dates
Get days between dates ignoring years
Get first day of month
Get first day of previous month
Get first Monday before any date
Get fiscal quarter from date
Get last day of month
Get last working day in month
Get month from date
Get month name from date
Get months between dates
Get most recent day of week
Get next day of week
Get nth day of year
Get percent of year complete
Get quarter from date
Get same date next month
Get same date next year
Get start date based on target date
Get week number from date
Get work hours between dates
Get work hours between dates custom schedule
Get workdays between dates
Get year from date
Group times into 3 hour buckets
Group times into unequal buckets
Join date and text
Next business day 6 months in future
Next working day
Pad week numbers with zeros
Sum race time splits
Total hours that fall between two times
Workdays per month
Year is a leap year
Date series
Series of dates by day
Series of dates by month
Series of dates by weekends
Series of dates by workdays
Series of dates by year
Text
Add a line break with a formula
Add line break based on OS
Cell contains all of many things
Cell contains any numbers
Cell contains one of many things
Cell contains some words but not others
Cell contains specific text
Cell contains which things
Cell equals one of many things
Clean and reformat telephone numbers
Compare two strings
Convert numbers to text
Convert text to numbers
Count line breaks in cell
Count specific characters in a cell
Count specific characters in a range
Count specific words in a cell
Count specific words in a range
Count total characters in a cell
Count total characters in a range
Count total words in a cell
Count total words in a range
Double quotes inside a formula
Extract nth word from text string
Extract text between parentheses
Extract word that begins with specific character
Get first word
Get last line in cell
Get last word
Most frequent text
Position of 2nd 3rd etc instance of character
Remove last n characters from text
Remove leading and trailing spaces from text
Remove text by matching
Remove text by position
Remove text by variable position
Remove unwanted characters
Replace one character with another
Split dimensions into three parts
Split dimensions into two parts
Split numbers from units of measure
Split text and numbers
Split text string at specific character
Strip html from text or numbers
Financial
Calculate compound interest
Calculate simple interest
Workbook
Get full workbook name and path
Get sheet name only
Get workbook name and path without sheet
Get workbook name only
Get workbook path only
Internet
Get domain from email address
Get domain name from URL
Get name from email address
Get top level domain (TLD)
Names
Get first name from Last, First
Get first name from name
Get last name from Last, First
Get last name from name
Get middle name from full name
Join first and last name
Put names into proper case
Percentage
Calculate percent variance
Decrease by percentage
Get amount with percentage
Get original price from percentage discount
Get percent change
Get percentage discount
Get percentage of total
Get profit margin percentage
Get total from percentage
Increase by percentage
Percent of goal
Percent of students absent
Range
Address of first cell in range
Address of last cell in range
All cells in range are blank
First column number in range
First row number in range
Get relative column numbers in range
Get relative row numbers in range
Last column number in range
Last row number in range
Range contains a value not in another range
Range contains numbers
Total cells in a range
Total columns in range
Total rows in range
Miscellaneous
Build hyperlink with VLOOKUP
Calculate a ratio from two numbers
Change negative numbers to positive
Check register balance
Count with repeating values
Course completion status summary
Extract unique items from a list
Fixed value every N months
Flag first duplicate in a list
Flip table rows to columns
Formula with locked reference
Get date associated with last entry
Get value of last non-empty cell
Increment a calculation with ROW or COLUMN
List contains duplicates
Longest winning streak
Mixed reference for multiplication table
Pad a number with zeros
Random date between two dates
Random number between two numbers
Random text values
Randomly assign data to groups
Range contains one of many substrings
Range contains one of many values
Range contains specific text
Range contains specific value
Reverse a list or range
Transpose table without zeros
dan masih banyak yang lainnya…
JOGJA MULTIMEDIA
Jl. Magelang KM 7,5 Mlati Sleman Yogyakarta
HP : 081333691112
WA : 081333691112
Â
