Excel Analysis tools

 

In order to be able to offer an affordable solution, e.g. for cost centre analysis, Janitza electronics® offers MS Excel solutions specific to the customer. An expensive pro cess control solution is, therefore, superfluous. The measurement data in Janitza electronics® products also available thrugh MS Excel macro or in the MS Excel functions. The costs per data point in MS Excel are just a fraction of the costs for a data point in process control technology.

Another advantage is the maintenance of the system. Simple changes can be made by the user at any time if the user has sufficient knowledge about MS Excel. Furthermore, the MS Excel macros can be installed on as many computers as required. There are no additional license costs. Obviously a licensed MS Excel version (2003 or 2007) with the most recent service packs is a prerequisite.

MS-Excel analysis

The analysis of measurement data can be carried out using the software for the instruments or additionally by using the MS Excel macros which are available as an option. The macros produce MS Excel functions similar to the familiar MS Excel function = sum (). The macros can be incorporated in MS Excel as an add-in. The macros use an ODBC driver to directly access the measuring instrument’s database. Complex analysis can be created even if the user only has moderate knowledge of MS Excel.

 

The customer specific MS Excel solutions, which are directly created by Janitza electronics®, can program progress charts and user interfaces which are specially produced for the application. For example, it is possible to create a complete cost analysis as a PDF document with the push of a button. The integration of the building plan or the entering of a password is also possible.

 

You will not recognise the standard MS Excel at all. The MS Excel solutions developed by us come very close to visualisation system but only cost a fraction of the price and do not have any complicated, expensive license management. You only require a licensed version of MS Excel on the computer on which the analysis is to be carried out. The actual macros it self can be used as required.

Main features

  • Affordable, just a fraction of the cost per data point in comparison to process control visualization solutions

  • View changes can be undertaken by the end customer at any time if the customer has sufficient MS Excel knowledge

  • The complete functional diversity of MS Excel is available

  • Many users can access the database via MS Exel at the same time

  • Many programs can directly access MS Excel

  • The MS Excel macros are consistently adapted to the database models of Janitza electronics®. This means that a rapid data flow rate is achieved.

  • The standard MS Excel terms are used for all Janitza® MS Excel functions such as sum = (..,...)

  • A standard file with the most important analysis is available free of charge

  • The software supports MS Excel 2003 and MS Excel 2007 with ribbon UI

  • A user interface in the look&feel function in Office 2007 (ribbon UI) can be created

Applications

The MS Excel functions which are especially programmed for the database model offer a wide range of selection options. For example, it is possible to let the software carry out a targeted search for peak values. It is even possible to create an average value over any period of time and to create the maximum value of a measurement parameter. The full loading of a transformer can also be identified at an early stage. Here is a small selection of the possible functions:

  • AverageValue: provides the average value of a measurement parameter in a month
  • AverageDates: provides the average value of a measurement parameter within the time range
  • Maximum: provides the maximum value of a measurement parameter in a month
  • MaximumDates: provides the maximum value of a measurement parameter within the time range
  • MaximumDate_time_month: provides the date and time of the maximum value
  • Minimum: provides the minimum value of a measurement parameter in a month
  • MinimumDates: provides the minimum value of a measurement parameter within the time range
  • MinimumDate_time_month: provides the date and time of the minimum value
  • ConsumedRealEnergy: provides the effective energy drawn in a month
  • ConsumedRealEnergyDates: provides the effective energy drawn within the time range
  • Get_AvgValues_oneMonth: this function produces a list with measurement values for a month.

A total of more than 50 functions are available !

MS-Excel VBA

VBA (Visual Basic for applications) is an independent, object-orientated programming language for MS Office applications. It serves the purpose of expanding the extent of functions of MS office products. Through the use of these programs (macros), MS Excel becomes even more powerful because MS Excel tables can be automated for example.

Who can profit from the MS Excel analysis tools?

 

Analysis tools are applied when functions of the PSW professional, PAS 510 or GridVis software systems are not sufficient. They, therefore, make sense for complex cost centre analysis, large-scale applications and when measurement data has to be compared with certain specifications. The MS Excel analysis tools can be an affordable introduction to the world of process control technology for medium-sized companies which do not have their own building automation and visualisation. But MS Excel analysis tools can also offer an option to make necessary data accessible to all members of staff in large companies in which all employees may not have access to building automation. This means that consumption levels and power peaks can easily be passed on to controlling in customer specific form or invoices can be issued directly from MS Excel if the MS Excel sheet already has an invoice template.

Function

The macros take the respective measurement data from the database created by PSW professional, PAS 510 or GridVis and enter it in an MS Excel document. The time periods, for example, are now simply entered and the respective measurement values, consumptions, costs or other values calculated by MS Excel are issued as a graph or table. All MS Excel instruments are available for creating the MS Excel documents. The MS Excel macro is incorporated in MS Excel as an add-in. This can be used to create graphics of the building complex or systems. Set function keys can be used to zoom in on individual buildings or building parts in order to be able to monitor the incurred costs, consumptions or any other measurement data.

Vorteile

The data are made available as measured in the databases of the various software systems of Janitza electronics® GmbH. However, in the MS Excel analysis tools any computing processes can be undertaken which are provided by MS Excel. The measurement data from the macros can be reprocessed in formulas. This means that the facility manager has almost unrestricted options for analysis e.g. for the comparison with reference data, for percentage distributions, for comparisons of various properties or any graphs etc. This means that the customer is no longer tied to the rigid functions of ready to use software and can either create MS Excel sheets himself, whereby Janitza electronics ® GmbH provides the macros, or he can allow Janitza electronics® GmbH to undertake extensive programming as a service against payment of a very reasonable charge.

Vorgehensweise

In order to be able to create MS Excel solutions specific to the customer, Janitza electronics® needs a specification in which the functions of the MS Excel analysis tools are described. The costs are exclusively based on the time needed for programming. These costs can be kept low with active cooperation e.g. the customer provides prepared tables or building plans in MS Excel. Our sales team is pleased to support you and work together with you on a suitable and affordable solution.

Screenshots