Network Data Processing (excel Basics) 6f475z

MS Excel & VBA

1. Network Data Processing - 2 days • Topics:

• MS Excel General

• Navigation, Fill Handle, Cell References, Range Names, Nested Functions

• Data Analysis

• Auto Numbering, Subtotal, Summary Function, Data Validation, Conditional Formatting, Filtering Tools, Lookups, Pivoting

• Plotting Data

• Type of Graphs, Data Source, Combining Graph, Axes, InterpolationUe-Network Interaction (idle & Dedicated Mode)

• Excel VBA • • • • • •

Cell, range, worksheet and Workbook Variables, Arrays, Constants and Data Types Modules, Functions and Subroutines Operator, Decisions and Looping String, Function, Message Boxes & Dialogs VBA Project: Parsing Database

General What is Microsoft Excel?

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, MacOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA).

General - Navigation (i) Arrray

An array is a systematic arrangement of data or objects. Multidimensional Arrray

An array can be declared in various dimensions, in a case that we have 2 dimensions array we can merge all to form a table with rows and columns. A multidimensional array can have up to 64 dimensions depending on the system architecture.

General - Navigation (ii) Cells and Ranges

MS Excel uses the array concept to navigate through the data that is contained in a single cell or a conjunction of cells called range. We can refer to any of them by calling the column and row position of the desired data.

Selection : Cell E5

Selection : Range C5:E10

General - Navigation (iii) Worksheets and Workbooks

MS Excel data can be expanded from 2 dimensions array to 3 dimensions by calling the worksheet name of the target cell. By default MS Excel will open a workbook containing 3 worksheets. In the picture at the right we can see that a different data is located in the same cell address (D5), but the sheet is pointing to a different sheet. The same case applies if we are working between workbooks so the cell reference will have a workbook name as prefix. (ex: [Book1]Sheet2!F5)

Selection : Cell Sheet1!E5

Selection : Cell Sheet2!E5

General – Fill Handle Autofill

MS Excel provides a feature called ‘Fill Handle’. It’s a small square located at the right bottom of the selected cell or range which allows us to easily fill the data by copying the selection or using a fill pattern acquired by the existing data.

General – Cell Reference (i) Arrray

An array is a systematic arrangement of data or objects. Multidimensional Arrray

An array can be declared in various dimensions, in a case that we have 2 dimensions array we can merge all to form a table with rows and columns. A multidimensional array can have up to 64 dimensions depending on the system architecture.

General - Cell Reference (ii) Cells and Ranges

MS Excel uses the array concept to navigate through the data that is contained in a single cell or a conjunction of cells called range. We can refer to any of them by calling the column and row position of the desired data.

Selection : Cell E5

Selection : Range C5:E10

General - Cell Reference (iii) Worksheets and Workbooks

MS Excel data can be expanded to use more than one table in a workbook called worksheet. By default MS Excel will open a workbook containing 3 worksheets. In the picture at the right we can see that a different data is located in the same cell address (D5), but the sheet is pointing to a different sheet. The same case applies if we are working between workbooks so the cell reference will have a workbook name as prefix. (ex: [Book1]Sheet2!F5)

Selection : Cell Sheet1!E5

Selection : Cell Sheet2!E5

General - Cell Reference (iv) Absolute Cell Reference

When working with cell references and also fill handle we will frequently change our cursor position, copy-paste data, etc. When these actions are performed, our cell reference will also move following the offset position of our cursor. To avoid the cell reference to change, we need to make our references absolute by adding an absolute symbol ($) before our cell address.

D2 = B2, D3 = B3

D2 = $B$2, D3 = $B$3

General – Range Names Defining The Range Name

When in a worksheet we have more than one table or range that we usually use, it will be very handy to define a name to a range so we can call it by its tag name than to use its range address.

Selection : Range A2:C10

Selection : Range MyDataRange

General – Functions (i) Definition Of Functions

A function is a block of organized reusable code that is used to perform a single related action. Functions provide the ability of data computation in an efficient way. A function requires data as input to be computed to generate the desired output. The number of data that the functions need what we also call argument depends on the function requirements itself. =x(arg1) =x(arg1, arg2, arg3, …)

General – Functions (ii) Working With Functions

To easily use functions in MS Excel, we can just click the function wizard icon in the formula bar above the data area in our worksheet so a list of functions will popup. In this wizard the functions are categorized by its use like statistical, financial, date & time, etc. Wherever we want to insert a function we need the equal (=) sign to initiate the function.

Selection : Range A2:C10

General – Functions (iii) Nested Functions

A function can contain another function inside its arguments to fulfill its requirements. In nested functions we have inner and outer functions which is defined by the position of the function. =x(arg1, y(arg1))

We have 2 functions (x & y) where x requires 2 arguments and y requires 1. The output of the inner function or “y” will be used as input for the second argument in “x” function.

Data Analysis What is Data Analysis? Analysis of data is a process of inspecting, cleansing, transforming, and modeling data with the goal of discovering useful information, suggesting conclusions, and ing decisionmaking. Data analysis has multiple facets and approaches, encoming diverse techniques under a variety of names, in different business, science, and social science domains. MS Excel provides features to data analysis like ‘conditional formatting’, ‘data filters’, ‘data pivot’, etc.

Data Analysis – Auto Numbering Numbering Functions

MS Excel provides functions to give numbers to our data in the order that we need. Some of these functions are rank(), row(), column(), etc.

Rank function to number the value position

Data Analysis – Subtotal Subtotal Function

sum() function can be used to aggregate the values of defined cells, an entire range, or also multiple ranges. Sum function to aggregate values in a range

Data Analysis – Summary Functions Summarizing Data

Same as the sum() function, there are various math functions available in MS Excel to aggregate and summarize the data that we want to present. These kind of functions can be found in the ‘Math & Trig’ category in the function wizard. Some of these functions are count(), sum(), average(), max(), min(), power(), sqrt(), etc.

Data Analysis – Data Validation Restricting Allowed Data

Data validation can be used when we know the data that a cell or range may contain. We use can use rules or a value range to validate our data. Data validation menu is located under the Data ribbon on the menu. An error will show if an invalid value is entered on the restricted cells. Value restricted for anything not between 0 and 100

Data Analysis – Conditional Formatting Formatting Cells Based on a Condition

Conditional formatting can be used to give colors, arrows, and other symbols to cells which content values are meeting a specified criteria. Conditional Formatting menu is located under the Home ribbon on the menu.

Data Analysis – Lookup Lookup Functions

When we work in MS Excel, frequently the data that we need to process doesn’t contain all the information that we need but is divided in different tables. To merge these tables based on a data that we use as key we can use the lookup function horizontally (hlookup) or vertically (vlookup).

Table A

Table B

Data Analysis – Pivot Summarizing Data With Pivot Tables

As we can summarize data with aggregation functions we can also summarize data in a more simple way with pivot tables. The pivot table is used to aggregate data based on a group specified formed with rows and columns from a data range. Pivot Table menu is located under the Insert ribbon on the menu.

Plotting Data Why to plot the data? When we are analyzing data sometimes it is more simple when we have the data on a graphical view so we can easily identify problems and root causes. MS Excel has the ability of creating charts so we can analyze our reports in a simple way.

Plotting Data – Types of graphs Inserting Charts In the insert menu on the ribbon we have the option to insert a chart into our worksheet. When clicked a popup menu will show providing us many chart options like column, line, pie, bar, etc. When to use each kind of chart depends on the data that we want to plot, for example column and bar charts are suitable for volume or quantity data while line charts are suitable for trending data and pie charts are suitable for portion data.

Plotting Data – Data Source How to select data to plot into charts? A chart by default consists of 2 axes (x & y). The most common use of those axes are the x axe will represent the data labels while the y axe will represent the data series that we want to plot. As example we have a daily basis data of call setup success rate (CSSR) and drop call rate (DCR) for JABO area, and we want to plot the CSSR data into a line chart so we select the DATE column as x axe and CSSR column as y column then we select the chart we want to insert.

Plotting Data – Combined Graphs How to combine chart types Sometimes we need to plot data with different units into the same chart. In this case we need to add an additional axe so we are able to see all the data in the same chart so the chart will have these axes x, y1, y2). With the same data as the previous section, now we want to plot the DCR, and DropCallNumber data into the same chart but the data has different units; DCR = percent, DropCallNumber = count.