LAMBDA FUNCTION IN EXCEL IN RESPONSE TO CHALLENGES OF NONPROGRAMMERS IN MODERN CORPORATE PRACTICE
| Authors | |
|---|---|
| Year of publication | 2023 |
| Type | Article in Proceedings |
| Conference | INTED2023 Proceedings |
| MU Faculty or unit | |
| Citation | |
| Web | https://library.iated.org/view/LASAK2023LAM |
| Doi | http://dx.doi.org/10.21125/inted.2023.0288 |
| Keywords | LAMBDA function; User Defined Functions; Business Analytics |
| Description | Increasing number of companies are becoming aware of competitive advantage of business intelligence implementation in business processes and decision-making. However, due to a shortage of data scientists and experts in business analytics in the labour market, small and medium companies face difficulties developing their analytical departments. Very often, the employees who are not programmers, though skilled in Excel, are asked to focus on analytical data-related tasks. Then the need to develop customized functions for efficient routine data processing becomes necessary. Within Excel, the VBA (Visual Basic for Application) programming language can be used to program customized functions. However, there are three difficulties to cope with. First, VBA expects macros allowed in excel, which is even in Microsoft's default setting blocked. Second, many companies and institutions block macros from running for security reasons. Finally, developing and testing own function may be time-consuming and challenging for nonprogrammers. Using the LAMBDA function is an elegant way to develop new customized functions quickly and effectively without any programming experience. The article describes utilizing the LAMBDA function to create and test a new function. Last but not least, it will be presented how to insert this function into a defined name so that the result will be a standard function that, at first glance, is unrecognizable from a standard Microsoft function or a function created via VBA. In a learning process, there are other advantages. Understanding of the role of parameters and easy debugging in the workbook sheet is easily accessible for nonprogrammers and, in addition, allows a smooth transition to VBA. Practical examples of the development of own functions will be provided to demonstrate the utilization of the LAMBDA function. The examples will cover a broader range of applications, from, e.g., mimicking LPAD and RPAD functions known from SQL to computational routines. The Lambda function represents an easily accessible tool for nonprogrammers allowing its fast implementation in modern corporate business analytics practice. |