清洁代码在Excel？ [英] Clean Code in Excel?

问题描述

作为一个前程序员，我喜欢清洁，可维护和记录的代码。

作为项目经理，我不时要做复杂的擅长，

1. （如何）我可以在多行




2. （如何）我可以命名一个（单元格相对）公式并重用它吗？ （例如，将其作为带参数的vb（或甚至f＃）函数写入）

Example1： >

  = IF（AND（L $ 11 +（L $ 13-1）* 7> = $ C15; L $ 11 + ）* 7 <$ D15）; VLOOKUP（$ A15; RessourcePlan; 12 + $ E15; WRONG）; 0）
  

$ b b

我想写：

  //检查此列（L）日期是否介于startdate C $; $ L $ 11 +（L $ 13-1）* 7 <$ D15）; 
 
 //然后从资源计划中选择相应团队的利用率（12 + E15）
 VLOOKUP（$ A15; RessourcePlan; 12 + $ E15; WRONG）; 
 
 // else default to 0 
 0 
 
）// ENDIF 
  

而不是example1我可以写一个用户定义的函数（UDF）

 函数使用Date，task As String）As Double 
 ... //清理VB或F＃代码
结束函数
  

然后写

  =利用（L11，A15）
  
 
 
解决方案
作为一个前功能程序员，我想出了用户定义
 
 
 
请注意以下几点：
 
 
 
 
 
只写纯数学函数，将参数映射到
结果，不涉及状态变化，输入/输出，for循环或类似的
。 （至少我有这个想法，让和VBA显然不是纯..）
 
 
单元格引用和自动完成是在表中完成strong in this）
 
 
常量在名为常量的特殊工作表中定义为命名范围
 
 
 
 
 
给定一个具有估计工作量和开始和结束日期的任务，我需要多少ppl？ => 100％表示有一个人需要在这个全职工作（假设她工作x daysPerWeek，固定在常数中）
 公共函数util（ByVal startDate As Date，ByVal endDate As Date，ByVal estimate As Double）As Double 
 
 Dim duration As Integer 
让duration = DateDiff（d，startDate ，endDate）
 
 Dim weeks As Double 
让weeks = duration / 7 
 
 Dim pdays As Integer 
让pdays = weeks * [daysPerWeek] 
 
 util = estimate / pdays 
 
结束函数
  
因为我有很多任务和许多团队在他们的工作，我想知道一个团队我需要一个任务多少ppl。以下功能从上面重复使用该功能。注意复杂的Vlookup调用的可读名称和对BaseLine1的引用，这是我的实际项目计划。 
 公共函数currentUtil（currentDate As Date，id As String， team as Integer）as Double 
 Dim result as Double 
 
让startDate = Application.WorksheetFunction.VLookup（id，[BaseLine1]，11，0）
让endDate = Application。 WorksheetFunction.VLookup（id，[BaseLine1]，12，0）
让估计= Application.WorksheetFunction.VLookup（id，[BaseLine1]，5 + team，0）
 
如果> = startDate And currentDate< endDate）then 
 result = util（startDate，endDate，estimate）
否则
结果= 0 
结束如果
 $ b b currentUtil = result 
 
结束函数
  
方式：我有一个主表，所有任务，包括他们的日期和每个团队的估计努力。在另一张表中，我有几个星期在水平和任务每个团队在垂直。在单元格中我使用函数currentUtil，并使用自动完成和条件格式化来获得计划的分析视图。
 
 
 
最后，我有相同的结果，以更方便和可维护的形式。
 
As a former programmer I like clean, maintainable and documented code.

As a project manager I have to do complex excels from time to time and want to write "clean" formulas in the same way I wrote programs.

(How) Can I add "comments" into a (multiline) formula?
(How) Can I "name" a (cell-relative) formula and reuse it? (e.g. write it as a vb (or even f#) function with parameters)
Example1: Instead of 
=IF(AND(L$11+(L$13-1)*7>=$C15;L$11+(L$13-1)*7<$D15);VLOOKUP($A15;RessourcePlan;12+$E15;WRONG);0)
I'd like to write:
// check if this columns (L) date is inbetween startdate (Col C) and enddate (Col D)
=IF (AND(L$11+(L$13-1)*7>=$C15;L$11+(L$13-1)*7<$D15);

    // then select the the utilisation (12+E15) for the resp. team from the resource plan
    VLOOKUP($A15;RessourcePlan;12+$E15;WRONG);

    // else default to 0
    0 

) //ENDIF
And instead of example1 I might write a user defined function (UDF)
Function Utilization(thisDate As Date, task As String) As Double
    ... // clean VB or F# code
End Function
And then write
=Utilization(L11,A15)

解决方案
Being a former functional programmer, I came up with the user defined functions given below for my problem.

Note the following points:


I only write "pure" mathematical functions that map in-params to
results, no state-changes, input/output, for-loops or similar
involved. (At least I have this in mind, "Let" and VBA is obviously not "pure"..)
cell referencing and auto-completion is to be done in the sheets (excel is strong here)
constants are defined as named ranges in a special sheet called "Constants"


Given a task with an estimated effort and start and end-date, how many ppl do I need? => 100% means one person needs to work on this full-time (assuming she is working x daysPerWeek, as fixed in the constants)
Public Function util(ByVal startDate As Date, ByVal endDate As Date, ByVal estimation As Double) As Double

 Dim duration As Integer
 Let duration = DateDiff("d", startDate, endDate)

 Dim weeks As Double
 Let weeks = duration / 7

 Dim pdays As Integer
 Let pdays = weeks * [daysPerWeek]

 util = estimation / pdays

End Function
Since I have many tasks and many teams working on them, I'd like to know how many ppl from a team I need for a task. The following function reuses the function from above. Note the readable names for the complex Vlookup-calls and the reference to "BaseLine1" which is my actual project plan. It will be very easy to extend this to get other scenarios etc.
Public Function currentUtil(currentDate As Date, id As String, team As Integer) As Double
  Dim result As Double

  Let startDate = Application.WorksheetFunction.VLookup(id, [BaseLine1], 11, 0)
  Let endDate = Application.WorksheetFunction.VLookup(id, [BaseLine1], 12, 0)
  Let estimation = Application.WorksheetFunction.VLookup(id, [BaseLine1], 5 + team, 0)

  If (currentDate >= startDate And currentDate < endDate) Then
    result = util(startDate, endDate, estimation)
  Else
    result = 0
  End If

  currentUtil = result

End Function
Finally I use it in the following way: I have a main table with all tasks including their dates and estimated efforts per team. In another sheet, I have the weeks on the horizontal and the tasks per team on the vertical. In the cells I use the function "currentUtil" and use autocompletion and conditional formatting to get a analytical view on the plan.

Finally I have the same result as before, but in a much more convenient and maintainable form.

                        
这篇关于清洁代码在Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！