[Summary view]

Differences Between: [Versions 400 and 403] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Database\DAverage;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Database\DCount;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Database\DMax;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Database\DMin;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Database\DSum;
  10  use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
  11  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
  12  
  13  class Conditional
  14  {
  15      private const CONDITION_COLUMN_NAME = 'CONDITION';
  16      private const VALUE_COLUMN_NAME = 'VALUE';
  17      private const CONDITIONAL_COLUMN_NAME = 'CONDITIONAL %d';
  18  
  19      /**
  20       * AVERAGEIF.
  21       *
  22       * Returns the average value from a range of cells that contain numbers within the list of arguments
  23       *
  24       * Excel Function:
  25       *        AVERAGEIF(range,condition[, average_range])
  26       *
  27       * @param mixed $range Data values
  28       * @param string $condition the criteria that defines which cells will be checked
  29       * @param mixed $averageRange Data values
  30       *
  31       * @return null|float|string
  32       */
  33      public static function AVERAGEIF($range, $condition, $averageRange = [])
  34      {
  35          if (!is_array($range) || !is_array($averageRange) || array_key_exists(0, $range) || array_key_exists(0, $averageRange)) {
  36              throw new CalcException('Must specify range of cells, not any kind of literal');
  37          }
  38          $database = self::databaseFromRangeAndValue($range, $averageRange);
  39          $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
  40  
  41          return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
  42      }
  43  
  44      /**
  45       * AVERAGEIFS.
  46       *
  47       * Counts the number of cells that contain numbers within the list of arguments
  48       *
  49       * Excel Function:
  50       *        AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
  51       *
  52       * @param mixed $args Pairs of Ranges and Criteria
  53       *
  54       * @return null|float|string
  55       */
  56      public static function AVERAGEIFS(...$args)
  57      {
  58          if (empty($args)) {
  59              return 0.0;
  60          } elseif (count($args) === 3) {
  61              return self::AVERAGEIF($args[1], $args[2], $args[0]);
  62          }
  63          foreach ($args as $arg) {
  64              if (is_array($arg) && array_key_exists(0, $arg)) {
  65                  throw new CalcException('Must specify range of cells, not any kind of literal');
  66              }
  67          }
  68  
  69          $conditions = self::buildConditionSetForValueRange(...$args);
  70          $database = self::buildDatabaseWithValueRange(...$args);
  71  
  72          return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
  73      }
  74  
  75      /**
  76       * COUNTIF.
  77       *
  78       * Counts the number of cells that contain numbers within the list of arguments
  79       *
  80       * Excel Function:
  81       *        COUNTIF(range,condition)
  82       *
  83       * @param mixed[] $range Data values
  84       * @param string $condition the criteria that defines which cells will be counted
  85       *
  86       * @return int|string
  87       */
  88      public static function COUNTIF($range, $condition)
  89      {
  90          // Filter out any empty values that shouldn't be included in a COUNT
  91          $range = array_filter(
  92              Functions::flattenArray($range),
  93              function ($value) {
  94                  return $value !== null && $value !== '';
  95              }
  96          );
  97  
  98          $range = array_merge([[self::CONDITION_COLUMN_NAME]], array_chunk($range, 1));
  99          $condition = array_merge([[self::CONDITION_COLUMN_NAME]], [[$condition]]);
 100  
 101          return DCount::evaluate($range, null, $condition, false);
 102      }
 103  
 104      /**
 105       * COUNTIFS.
 106       *
 107       * Counts the number of cells that contain numbers within the list of arguments
 108       *
 109       * Excel Function:
 110       *        COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
 111       *
 112       * @param mixed $args Pairs of Ranges and Criteria
 113       *
 114       * @return int|string
 115       */
 116      public static function COUNTIFS(...$args)
 117      {
 118          if (empty($args)) {
 119              return 0;
 120          } elseif (count($args) === 2) {
 121              return self::COUNTIF(...$args);
 122          }
 123  
 124          $database = self::buildDatabase(...$args);
 125          $conditions = self::buildConditionSet(...$args);
 126  
 127          return DCount::evaluate($database, null, $conditions, false);
 128      }
 129  
 130      /**
 131       * MAXIFS.
 132       *
 133       * Returns the maximum value within a range of cells that contain numbers within the list of arguments
 134       *
 135       * Excel Function:
 136       *        MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
 137       *
 138       * @param mixed $args Pairs of Ranges and Criteria
 139       *
 140       * @return null|float|string
 141       */
 142      public static function MAXIFS(...$args)
 143      {
 144          if (empty($args)) {
 145              return 0.0;
 146          }
 147  
 148          $conditions = self::buildConditionSetForValueRange(...$args);
 149          $database = self::buildDatabaseWithValueRange(...$args);
 150  
 151          return DMax::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
 152      }
 153  
 154      /**
 155       * MINIFS.
 156       *
 157       * Returns the minimum value within a range of cells that contain numbers within the list of arguments
 158       *
 159       * Excel Function:
 160       *        MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
 161       *
 162       * @param mixed $args Pairs of Ranges and Criteria
 163       *
 164       * @return null|float|string
 165       */
 166      public static function MINIFS(...$args)
 167      {
 168          if (empty($args)) {
 169              return 0.0;
 170          }
 171  
 172          $conditions = self::buildConditionSetForValueRange(...$args);
 173          $database = self::buildDatabaseWithValueRange(...$args);
 174  
 175          return DMin::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
 176      }
 177  
 178      /**
 179       * SUMIF.
 180       *
 181       * Totals the values of cells that contain numbers within the list of arguments
 182       *
 183       * Excel Function:
 184       *        SUMIF(range, criteria, [sum_range])
 185       *
 186       * @param mixed $range Data values
 187       * @param mixed $sumRange
 188       * @param mixed $condition
 189       *
 190       * @return null|float|string
 191       */
 192      public static function SUMIF($range, $condition, $sumRange = [])
 193      {
 194          $database = self::databaseFromRangeAndValue($range, $sumRange);
 195          $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
 196  
 197          return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
 198      }
 199  
 200      /**
 201       * SUMIFS.
 202       *
 203       * Counts the number of cells that contain numbers within the list of arguments
 204       *
 205       * Excel Function:
 206       *        SUMIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
 207       *
 208       * @param mixed $args Pairs of Ranges and Criteria
 209       *
 210       * @return null|float|string
 211       */
 212      public static function SUMIFS(...$args)
 213      {
 214          if (empty($args)) {
 215              return 0.0;
 216          } elseif (count($args) === 3) {
 217              return self::SUMIF($args[1], $args[2], $args[0]);
 218          }
 219  
 220          $conditions = self::buildConditionSetForValueRange(...$args);
 221          $database = self::buildDatabaseWithValueRange(...$args);
 222  
 223          return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
 224      }
 225  
 226      /** @param array $args */
 227      private static function buildConditionSet(...$args): array
 228      {
 229          $conditions = self::buildConditions(1, ...$args);
 230  
 231          // Scrutinizer thinks first parameter of array_map can't be null. It is wrong.
 232          return array_map(/** @scrutinizer ignore-type */ null, ...$conditions);
 233      }
 234  
 235      /** @param array $args */
 236      private static function buildConditionSetForValueRange(...$args): array
 237      {
 238          $conditions = self::buildConditions(2, ...$args);
 239  
 240          if (count($conditions) === 1) {
 241              return array_map(
 242                  function ($value) {
 243                      return [$value];
 244                  },
 245                  $conditions[0]
 246              );
 247          }
 248  
 249          return array_map(/** @scrutinizer ignore-type */ null, ...$conditions);
 250      }
 251  
 252      /** @param array $args */
 253      private static function buildConditions(int $startOffset, ...$args): array
 254      {
 255          $conditions = [];
 256  
 257          $pairCount = 1;
 258          $argumentCount = count($args);
 259          for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
 260              $conditions[] = array_merge([sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)], [$args[$argument]]);
 261              ++$pairCount;
 262          }
 263  
 264          return $conditions;
 265      }
 266  
 267      /** @param array $args */
 268      private static function buildDatabase(...$args): array
 269      {
 270          $database = [];
 271  
 272          return self::buildDataSet(0, $database, ...$args);
 273      }
 274  
 275      /** @param array $args */
 276      private static function buildDatabaseWithValueRange(...$args): array
 277      {
 278          $database = [];
 279          $database[] = array_merge(
 280              [self::VALUE_COLUMN_NAME],
 281              Functions::flattenArray($args[0])
 282          );
 283  
 284          return self::buildDataSet(1, $database, ...$args);
 285      }
 286  
 287      /** @param array $args */
 288      private static function buildDataSet(int $startOffset, array $database, ...$args): array
 289      {
 290          $pairCount = 1;
 291          $argumentCount = count($args);
 292          for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
 293              $database[] = array_merge(
 294                  [sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)],
 295                  Functions::flattenArray($args[$argument])
 296              );
 297              ++$pairCount;
 298          }
 299  
 300          return array_map(/** @scrutinizer ignore-type */ null, ...$database);
 301      }
 302  
 303      private static function databaseFromRangeAndValue(array $range, array $valueRange = []): array
 304      {
 305          $range = Functions::flattenArray($range);
 306  
 307          $valueRange = Functions::flattenArray($valueRange);
 308          if (empty($valueRange)) {
 309              $valueRange = $range;
 310          }
 311  
 312          $database = array_map(/** @scrutinizer ignore-type */ null, array_merge([self::CONDITION_COLUMN_NAME], $range), array_merge([self::VALUE_COLUMN_NAME], $valueRange));
 313  
 314          return $database;
 315      }
 316  }