[Summary view]

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