次の方法で共有

Using formulas in custom fields

  • [アーティクル]

 

Using formulas within custom fields has been a part of Microsoft Project for a long time, but typically they are used by the more advanced project managers and by developers of Project applications and add-ons.

 

That doesn't mean that you shouldn't learn how to use them. After all, of all the ways you can use to customize and automate Project, formulas within custom fields remain one of the simpler ones.

 

The first question that arises to those new to Project programming is, Why use a formula within a custom field in the first place?

 

The primary reason is that too often Project's default fields aren't displaying information precisely as you want. The second reason is that using formulas within custom fields is a much quicker and easier way to customize and automate Project than using VBA, VSTO, PowerShell, or C#.

 

Some Examples

  • For reporting purposes, if you'd rather have a text field and not a number field to display on the Gantt chart how much work resources are assigned to, you could create a custom text field, and then attach this formula to it:

 

    [work]/60/8 & "hours"

 

  1. To add the above formula, click Customize on the Tools menu, and then click Fields.
  2. Select a text field to customize, such as "Text1", and click Rename to give it a meaningful name, like "Work Time".
  3. Once you've renamed the field, click Formula.
  4. Copy and paste the above formula into the Edit Formula box.
  5. You're not done yet. After the formula has been added, go back to the Gantt chart and add the "Work Time" field.

 

 

  • Here's another example, When added to a custom number field, the following formula returns a numerical value that indicates the number of days between the current date and the finish date of the tasks in your schedule:

 

   DateDiff("d",NOW(),[Finish])

 

This example uses another method to customize Project fields by using functions. (don't confuse formulas with functions). Learn more about Project funcitons here.

 

 

  • A more complicated example: When added to a custom text field, the following formula (with functions included) returns a value of "No baseline," "Overbudget by 20% or more," or "Under budget":

Switch(Len(CStr([Baseline Finish]))<3, "No baseline", ([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more", ([Cost]+1)/([Baseline Cost]+1)>1,
"Overbudget",True,"Under budget")

 

An Example using VBA

 

Here is VBA code that does the same as the first example above. You might do this if you have numerous reports that need columns set in a specific way. The code could be added as a toolbar button. Adding custom fields using formulas within VBA is a four-step process. Here is an example that adds a "Work Value" column to the Gantt chart, with work values that are calculated by a formula.

 

  1. First, set up the custom formula that the custom field should use. In this example, the constant, pjCustomTaskText1, is being used to specify that a Text1 field is being customized to contain a formula.

 

   CustomFieldSetFormula FieldID:=pjCustomTaskText1, _

    Formula:="[Work]& "" hours"""

 

  1. Now calculate the formula.

 

   CustomFieldProperties FieldID:=pjCustomTaskText1, _

    Attribute:=pjFieldAttributeFormula, _

    SummaryCalc:=pjCalcFormula

 

  1. Now add the field to a view. "NewFieldName" indicates that Text1, will be used, which is equivalent to the pjCustomTaskText1 used in the CustomFieldProperties method above.

 

TableEdit name:="Entry", TaskTable:=True, NewName:="", fieldName:="", _ 

NewFieldName:="Text1", Title:="Work Table", ColumnPosition:=1

 

   

  1. Now apply the table to a view. I know, it sounds odd to apply the table when all you want to do is add a field, but that's the way it is with VBA code.

 

   TableApply name:="Entry"

 

There you have it. Not so difficult. And you saved the weekend.

Comments

  • Anonymous
    July 21, 2010
    hi i have create custom field name(project schedule indicator)-and i put this FORMULA -- Switch(ProjDateConv([Baseline Finish])="NA","No Variance",[Project Schedule Variance %]>10,"Critical",[Project Schedule Variance %]>=5,"Over",True,"Ok")WHY THE CALCULATION IS INCORRECT.Please advise me (ERROR) "project schedule indicator" calculation that requires a small development fix since it calculates the schedule indicator incorrectly. The calculation should exclude un baselined tasks.

  • Anonymous
    May 07, 2012
    Hi, i have an query. i will explain this with an example. now let's say i load the resources for construction of bridge. these resources are for the total qty of work. the resources loaded are combined. after loading i wish to seperately diefine it like PCC, RCC etc. can i do it with IIF statement. please clarify

  • Anonymous
    May 10, 2012
    Hi Virendra, For questions like this, please try the Project Customization and Programming forum: social.msdn.microsoft.com/.../threads Thanks.

  • Anonymous
    July 29, 2012
    Hi, How do I check if the column is already in the table? I tried your example and I can keep on adding in the same column, I don't want to add in the column if it's already in the table.

  • Anonymous
    January 17, 2014
    I wants to Know about the Plan % Complete vS. Actual % complete by Cost Not By duration