/** * MINIF * * Returns the minimum value within a range of cells that contain numbers within the list of arguments * * Excel Function: * MINIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be checked. * @return float */ public static function MINIF($aArgs, $condition, $sumArgs = array()) { // Return value $returnValue = null; $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); if (count($sumArgs) == 0) { $sumArgs = $aArgs; } $condition = PHPExcel_Calculation_Functions::_ifCondition($condition); // Loop through arguments foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { if (is_null($returnValue) || $arg < $returnValue) { $returnValue = $arg; } } } // Return return $returnValue; }
/** * SUMIF * * Counts the number of cells that contain numbers within the list of arguments * * Excel Function: * SUMIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be summed. * @return float */ public static function SUMIF($aArgs, $condition, $sumArgs = array()) { // Return value $returnValue = 0; $aArgs = self::flattenArray($aArgs); $sumArgs = self::flattenArray($sumArgs); if (count($sumArgs) == 0) { $sumArgs = $aArgs; } if (!in_array($condition[0], array('>', '<', '='))) { if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); } $condition = '=' . $condition; } // Loop through arguments foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { // Is it a value within our criteria $returnValue += $sumArgs[$key]; } } // Return return $returnValue; }
/** * __filter * * Parses the selection criteria, extracts the database rows that match those criteria, and * returns that subset of rows. * * @access private * @param mixed[] $database The range of cells that makes up the list or database. * A database is a list of related data in which rows of related * information are records, and columns of data are fields. The * first row of the list contains labels for each column. * @param mixed[] $criteria The range of cells that contains the conditions you specify. * You can use any range for the criteria argument, as long as it * includes at least one column label and at least one cell below * the column label in which you specify a condition for the * column. * @return array of mixed * */ private static function __filter($database, $criteria) { $fieldNames = array_shift($database); $criteriaNames = array_shift($criteria); // Convert the criteria into a set of AND/OR conditions with [:placeholders] $testConditions = $testValues = array(); $testConditionsCount = 0; foreach ($criteriaNames as $key => $criteriaName) { $testCondition = array(); $testConditionCount = 0; foreach ($criteria as $row => $criterion) { if ($criterion[$key] > '') { $testCondition[] = '[:' . $criteriaName . ']' . PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]); $testConditionCount++; } } if ($testConditionCount > 1) { $testConditions[] = 'OR(' . implode(',', $testCondition) . ')'; $testConditionsCount++; } elseif ($testConditionCount == 1) { $testConditions[] = $testCondition[0]; $testConditionsCount++; } } if ($testConditionsCount > 1) { $testConditionSet = 'AND(' . implode(',', $testConditions) . ')'; } elseif ($testConditionsCount == 1) { $testConditionSet = $testConditions[0]; } // Loop through each row of the database foreach ($database as $dataRow => $dataValues) { // Substitute actual values from the database row for our [:placeholders] $testConditionList = $testConditionSet; foreach ($criteriaNames as $key => $criteriaName) { $k = array_search($criteriaName, $fieldNames); if (isset($dataValues[$k])) { $dataValue = $dataValues[$k]; $dataValue = is_string($dataValue) ? PHPExcel_Calculation::_wrapResult(strtoupper($dataValue)) : $dataValue; $testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList); } } // evaluate the criteria against the row data $result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('=' . $testConditionList); // If the row failed to meet the criteria, remove it from the database if (!$result) { unset($database[$dataRow]); } } return $database; }
/** * AVERAGEIF * * Returns the average value from a range of cells that contain numbers within the list of arguments * * Excel Function: * AVERAGEIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be checked. * @return float */ public static function AVERAGEIF($aArgs, $condition, $averageArgs = array()) { // Return value $returnValue = 0; $aArgs = self::flattenArray($aArgs); $averageArgs = self::flattenArray($averageArgs); if (count($averageArgs) == 0) { $averageArgs = $aArgs; } $condition = self::_ifCondition($condition); // Loop through arguments $aCount = 0; foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { if (is_null($returnValue) || $arg > $returnValue) { $returnValue += $arg; ++$aCount; } } } // Return if ($aCount > 0) { return $returnValue / $aCount; } else { return self::$_errorCodes['divisionbyzero']; } }
public static function _ifCondition($condition) { $condition = PHPExcel_Calculation_Functions::flattenSingleValue($condition); if (!in_array($condition[0], array('>', '<', '='))) { if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); } return '=' . $condition; } else { preg_match('/([<>=]+)(.*)/', $condition, $matches); list(, $operator, $operand) = $matches; if (!is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); } return $operator . $operand; } }
/** * SUMIF * * Counts the number of cells that contain numbers within the list of arguments * * Excel Function: * SUMIF(value1[,value2[, ...]],condition) * * @access public * @category Mathematical and Trigonometric Functions * @param mixed $arg,... Data values * @param string $condition The criteria that defines which cells will be summed. * @return float */ public static function SUMIF($aArgs, $condition, $sumArgs = array()) { // Return value $returnValue = 0; $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs); $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs); if (empty($sumArgs)) { $sumArgs = $aArgs; } $condition = PHPExcel_Calculation_Functions::_ifCondition($condition); // Loop through arguments foreach ($aArgs as $key => $arg) { if (!is_numeric($arg)) { $arg = str_replace('"', '""', $arg); $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); } $testCondition = '=' . $arg . $condition; if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) { // Is it a value within our criteria $returnValue += $sumArgs[$key]; } } // Return return $returnValue; }