PHP PHPExcel_Calculation_Functions::NA方法代码示例

 /**
  * LOOKUP
  * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
  * @param	lookup_value	The value that you want to match in lookup_array
  * @param	lookup_vector	The range of cells being searched
  * @param	result_vector	The column from which the matching value must be returned
  * @return	mixed			The value of the found cell
  */
 public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
 {
     $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
     if (!is_array($lookup_vector)) {
         return PHPExcel_Calculation_Functions::NA();
     }
     $lookupRows = count($lookup_vector);
     $l = array_keys($lookup_vector);
     $l = array_shift($l);
     $lookupColumns = count($lookup_vector[$l]);
     if ($lookupRows == 1 && $lookupColumns > 1 || $lookupRows == 2 && $lookupColumns != 2) {
         $lookup_vector = self::TRANSPOSE($lookup_vector);
         $lookupRows = count($lookup_vector);
         $l = array_keys($lookup_vector);
         $lookupColumns = count($lookup_vector[array_shift($l)]);
     }
     if (is_null($result_vector)) {
         $result_vector = $lookup_vector;
     }
     $resultRows = count($result_vector);
     $l = array_keys($result_vector);
     $l = array_shift($l);
     $resultColumns = count($result_vector[$l]);
     if ($resultRows == 1 && $resultColumns > 1 || $resultRows == 2 && $resultColumns != 2) {
         $result_vector = self::TRANSPOSE($result_vector);
         $resultRows = count($result_vector);
         $r = array_keys($result_vector);
         $resultColumns = count($result_vector[array_shift($r)]);
     }
     if ($lookupRows == 2) {
         $result_vector = array_pop($lookup_vector);
         $lookup_vector = array_shift($lookup_vector);
     }
     if ($lookupColumns != 2) {
         foreach ($lookup_vector as &$value) {
             if (is_array($value)) {
                 $k = array_keys($value);
                 $key1 = $key2 = array_shift($k);
                 $key2++;
                 $dataValue1 = $value[$key1];
             } else {
                 $key1 = 0;
                 $key2 = 1;
                 $dataValue1 = $value;
             }
             $dataValue2 = array_shift($result_vector);
             if (is_array($dataValue2)) {
                 $dataValue2 = array_shift($dataValue2);
             }
             $value = array($key1 => $dataValue1, $key2 => $dataValue2);
         }
         unset($value);
     }
     return self::VLOOKUP($lookup_value, $lookup_vector, 2);
 }

 /**
  * TINV
  *
  * Returns the one-tailed probability of the chi-squared distribution.
  *
  * @param	float		$probability	Probability for the function
  * @param	float		$degrees		degrees of freedom
  * @return	float
  */
 public static function TINV($probability, $degrees)
 {
     $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
     $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
     if (is_numeric($probability) && is_numeric($degrees)) {
         $xLo = 100;
         $xHi = 0;
         $x = $xNew = 1;
         $dx = 1;
         $i = 0;
         while (abs($dx) > PRECISION && $i++ < MAX_ITERATIONS) {
             // Apply Newton-Raphson step
             $result = self::TDIST($x, $degrees, 2);
             $error = $result - $probability;
             if ($error == 0.0) {
                 $dx = 0;
             } elseif ($error < 0.0) {
                 $xLo = $x;
             } else {
                 $xHi = $x;
             }
             // Avoid division by zero
             if ($result != 0.0) {
                 $dx = $error / $result;
                 $xNew = $x - $dx;
             }
             // If the NR fails to converge (which for example may be the
             // case if the initial guess is too rough) we apply a bisection
             // step to determine a more narrow interval around the root.
             if ($xNew < $xLo || $xNew > $xHi || $result == 0.0) {
                 $xNew = ($xLo + $xHi) / 2;
                 $dx = $xNew - $x;
             }
             $x = $xNew;
         }
         if ($i == MAX_ITERATIONS) {
             return PHPExcel_Calculation_Functions::NA();
         }
         return round($x, 12);
     }
     return PHPExcel_Calculation_Functions::VALUE();
 }

 public function testNA()
 {
     $result = PHPExcel_Calculation_Functions::NA();
     $this->assertEquals('#N/A', $result);
 }

 /**
  *	CONVERTUOM
  *
  *	Converts a number from one measurement system to another.
  *	For example, CONVERT can translate a table of distances in miles to a table of distances
  *	in kilometers.
  *
  *	Excel Function:
  *		CONVERT(value,fromUOM,toUOM)
  *
  *	@param	float		$value		The value in fromUOM to convert.
  *	@param	string		$fromUOM	The units for value.
  *	@param	string		$toUOM		The units for the result.
  *
  *	@return	float
  */
 public static function CONVERTUOM($value, $fromUOM, $toUOM)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue($fromUOM);
     $toUOM = PHPExcel_Calculation_Functions::flattenSingleValue($toUOM);
     if (!is_numeric($value)) {
         return PHPExcel_Calculation_Functions::VALUE();
     }
     $fromMultiplier = 1.0;
     if (isset(self::$_conversionUnits[$fromUOM])) {
         $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group'];
     } else {
         $fromMultiplier = substr($fromUOM, 0, 1);
         $fromUOM = substr($fromUOM, 1);
         if (isset(self::$_conversionMultipliers[$fromMultiplier])) {
             $fromMultiplier = self::$_conversionMultipliers[$fromMultiplier]['multiplier'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
         if (isset(self::$_conversionUnits[$fromUOM]) && self::$_conversionUnits[$fromUOM]['AllowPrefix']) {
             $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
     }
     $value *= $fromMultiplier;
     $toMultiplier = 1.0;
     if (isset(self::$_conversionUnits[$toUOM])) {
         $unitGroup2 = self::$_conversionUnits[$toUOM]['Group'];
     } else {
         $toMultiplier = substr($toUOM, 0, 1);
         $toUOM = substr($toUOM, 1);
         if (isset(self::$_conversionMultipliers[$toMultiplier])) {
             $toMultiplier = self::$_conversionMultipliers[$toMultiplier]['multiplier'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
         if (isset(self::$_conversionUnits[$toUOM]) && self::$_conversionUnits[$toUOM]['AllowPrefix']) {
             $unitGroup2 = self::$_conversionUnits[$toUOM]['Group'];
         } else {
             return PHPExcel_Calculation_Functions::NA();
         }
     }
     if ($unitGroup1 != $unitGroup2) {
         return PHPExcel_Calculation_Functions::NA();
     }
     if ($fromUOM == $toUOM && $fromMultiplier == $toMultiplier) {
         //	We've already factored $fromMultiplier into the value, so we need
         //		to reverse it again
         return $value / $fromMultiplier;
     } elseif ($unitGroup1 == 'Temperature') {
         if ($fromUOM == 'F' || $fromUOM == 'fah') {
             if ($toUOM == 'F' || $toUOM == 'fah') {
                 return $value;
             } else {
                 $value = ($value - 32) / 1.8;
                 if ($toUOM == 'K' || $toUOM == 'kel') {
                     $value += 273.15;
                 }
                 return $value;
             }
         } elseif (($fromUOM == 'K' || $fromUOM == 'kel') && ($toUOM == 'K' || $toUOM == 'kel')) {
             return $value;
         } elseif (($fromUOM == 'C' || $fromUOM == 'cel') && ($toUOM == 'C' || $toUOM == 'cel')) {
             return $value;
         }
         if ($toUOM == 'F' || $toUOM == 'fah') {
             if ($fromUOM == 'K' || $fromUOM == 'kel') {
                 $value -= 273.15;
             }
             return $value * 1.8 + 32;
         }
         if ($toUOM == 'C' || $toUOM == 'cel') {
             return $value - 273.15;
         }
         return $value + 273.15;
     }
     return $value * self::$_unitConversions[$unitGroup1][$fromUOM][$toUOM] / $toMultiplier;
 }

 /**
  * VLOOKUP
  * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.
  *
  * @param    lookup_value       The value that you want to match in lookup_array
  * @param    lookup_array       The range of cells being searched
  * @param    index_number       The column number in table_array from which the matching value must be returned. The first column is 1.
  * @param    not_exact_match    Determines if you are looking for an exact match based on lookup_value.
  *
  * @return    mixed            The value of the found cell
  */
 public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
 {
     $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
     $index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
     $not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
     // index_number must be greater than or equal to 1
     if ($index_number < 1) {
         return PHPExcel_Calculation_Functions::VALUE();
     }
     // index_number must be less than or equal to the number of columns in lookup_array
     if (!is_array($lookup_array) || empty($lookup_array)) {
         return PHPExcel_Calculation_Functions::REF();
     } else {
         $f = array_keys($lookup_array);
         $firstRow = array_pop($f);
         if (!is_array($lookup_array[$firstRow]) || $index_number > count($lookup_array[$firstRow])) {
             return PHPExcel_Calculation_Functions::REF();
         } else {
             $columnKeys = array_keys($lookup_array[$firstRow]);
             $returnColumn = $columnKeys[--$index_number];
             $firstColumn = array_shift($columnKeys);
         }
     }
     if (!$not_exact_match) {
         uasort($lookup_array, array('self', '_vlookupSort'));
     }
     $rowNumber = $rowValue = false;
     foreach ($lookup_array as $rowKey => $rowData) {
         if (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && $rowData[$firstColumn] > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) {
             break;
         }
         $rowNumber = $rowKey;
         $rowValue = $rowData[$firstColumn];
     }
     if ($rowNumber !== false) {
         if (!$not_exact_match && $rowValue != $lookup_value) {
             //	if an exact match is required, we have what we need to return an appropriate response
             return PHPExcel_Calculation_Functions::NA();
         } else {
             //	otherwise return the appropriate value
             $result = $lookup_array[$rowNumber][$returnColumn];
             if (is_numeric($lookup_value) && is_numeric($result) || !is_numeric($lookup_value) && !is_numeric($result)) {
                 return $result;
             }
         }
     }
     return PHPExcel_Calculation_Functions::NA();
 }

 /**
  * MODE
  *
  * Returns the most frequently occurring, or repetitive, value in an array or range of data
  *
  * @param	array of mixed		Data Series
  * @return  float
  */
 public static function MODE()
 {
     // Return value
     $returnValue = PHPExcel_Calculation_Functions::NA();
     // Loop through arguments
     $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
     $mArgs = array();
     foreach ($aArgs as $arg) {
         // Is it a numeric value?
         if (is_numeric($arg) && !is_string($arg)) {
             $mArgs[] = $arg;
         }
     }
     if (count($mArgs) > 0) {
         return self::modeCalc($mArgs);
     }
     // Return
     return $returnValue;
 }

	/**
	 * CONVERTUOM
	 *
	 * @param float $value        	
	 * @param string $fromUOM        	
	 * @param string $toUOM        	
	 * @return float
	 */
	public static function CONVERTUOM($value, $fromUOM, $toUOM) {
		$value = PHPExcel_Calculation_Functions::flattenSingleValue ( $value );
		$fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $fromUOM );
		$toUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $toUOM );
		
		if (! is_numeric ( $value )) {
			return PHPExcel_Calculation_Functions::VALUE ();
		}
		$fromMultiplier = 1;
		if (isset ( self::$_conversionUnits [$fromUOM] )) {
			$unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group'];
		} else {
			$fromMultiplier = substr ( $fromUOM, 0, 1 );
			$fromUOM = substr ( $fromUOM, 1 );
			if (isset ( self::$_conversionMultipliers [$fromMultiplier] )) {
				$fromMultiplier = self::$_conversionMultipliers [$fromMultiplier] ['multiplier'];
			} else {
				return PHPExcel_Calculation_Functions::NA ();
			}
			if ((isset ( self::$_conversionUnits [$fromUOM] )) && (self::$_conversionUnits [$fromUOM] ['AllowPrefix'])) {
				$unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group'];
			} else {
				return PHPExcel_Calculation_Functions::NA ();
			}
		}
		$value *= $fromMultiplier;
		
		$toMultiplier = 1;
		if (isset ( self::$_conversionUnits [$toUOM] )) {
			$unitGroup2 = self::$_conversionUnits [$toUOM] ['Group'];
		} else {
			$toMultiplier = substr ( $toUOM, 0, 1 );
			$toUOM = substr ( $toUOM, 1 );
			if (isset ( self::$_conversionMultipliers [$toMultiplier] )) {
				$toMultiplier = self::$_conversionMultipliers [$toMultiplier] ['multiplier'];
			} else {
				return PHPExcel_Calculation_Functions::NA ();
			}
			if ((isset ( self::$_conversionUnits [$toUOM] )) && (self::$_conversionUnits [$toUOM] ['AllowPrefix'])) {
				$unitGroup2 = self::$_conversionUnits [$toUOM] ['Group'];
			} else {
				return PHPExcel_Calculation_Functions::NA ();
			}
		}
		if ($unitGroup1 != $unitGroup2) {
			return PHPExcel_Calculation_Functions::NA ();
		}
		
		if ($fromUOM == $toUOM) {
			return 1.0;
		} elseif ($unitGroup1 == 'Temperature') {
			if (($fromUOM == 'F') || ($fromUOM == 'fah')) {
				if (($toUOM == 'F') || ($toUOM == 'fah')) {
					return 1.0;
				} else {
					$value = (($value - 32) / 1.8);
					if (($toUOM == 'K') || ($toUOM == 'kel')) {
						$value += 273.15;
					}
					return $value;
				}
			} elseif ((($fromUOM == 'K') || ($fromUOM == 'kel')) && (($toUOM == 'K') || ($toUOM == 'kel'))) {
				return 1.0;
			} elseif ((($fromUOM == 'C') || ($fromUOM == 'cel')) && (($toUOM == 'C') || ($toUOM == 'cel'))) {
				return 1.0;
			}
			if (($toUOM == 'F') || ($toUOM == 'fah')) {
				if (($fromUOM == 'K') || ($fromUOM == 'kel')) {
					$value -= 273.15;
				}
				return ($value * 1.8) + 32;
			}
			if (($toUOM == 'C') || ($toUOM == 'cel')) {
				return $value - 273.15;
			}
			return $value + 273.15;
		}
		return ($value * self::$_unitConversions [$unitGroup1] [$fromUOM] [$toUOM]) / $toMultiplier;
	} // function CONVERTUOM()