Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5 class Database 6 { 7 /** 8 * fieldExtract. 9 * 10 * Extracts the column ID to use for the data field. 11 * 12 * @param mixed[] $database The range of cells that makes up the list or database. 13 * A database is a list of related data in which rows of related 14 * information are records, and columns of data are fields. The 15 * first row of the list contains labels for each column. 16 * @param mixed $field Indicates which column is used in the function. Enter the 17 * column label enclosed between double quotation marks, such as 18 * "Age" or "Yield," or a number (without quotation marks) that 19 * represents the position of the column within the list: 1 for 20 * the first column, 2 for the second column, and so on. 21 * 22 * @return null|string 23 */ 24 private static function fieldExtract($database, $field) 25 { 26 $field = strtoupper(Functions::flattenSingleValue($field)); 27 $fieldNames = array_map('strtoupper', array_shift($database)); 28 29 if (is_numeric($field)) { 30 $keys = array_keys($fieldNames); 31 32 return $keys[$field - 1]; 33 } 34 $key = array_search($field, $fieldNames); 35 36 return ($key) ? $key : null; 37 } 38 39 /** 40 * filter. 41 * 42 * Parses the selection criteria, extracts the database rows that match those criteria, and 43 * returns that subset of rows. 44 * 45 * @param mixed[] $database The range of cells that makes up the list or database. 46 * A database is a list of related data in which rows of related 47 * information are records, and columns of data are fields. The 48 * first row of the list contains labels for each column. 49 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 50 * You can use any range for the criteria argument, as long as it 51 * includes at least one column label and at least one cell below 52 * the column label in which you specify a condition for the 53 * column. 54 * 55 * @return array of mixed 56 */ 57 private static function filter($database, $criteria) 58 { 59 $fieldNames = array_shift($database); 60 $criteriaNames = array_shift($criteria); 61 62 // Convert the criteria into a set of AND/OR conditions with [:placeholders] 63 $testConditions = $testValues = []; 64 $testConditionsCount = 0; 65 foreach ($criteriaNames as $key => $criteriaName) { 66 $testCondition = []; 67 $testConditionCount = 0; 68 foreach ($criteria as $row => $criterion) { 69 if ($criterion[$key] > '') { 70 $testCondition[] = '[:' . $criteriaName . ']' . Functions::ifCondition($criterion[$key]); 71 ++$testConditionCount; 72 } 73 } 74 if ($testConditionCount > 1) { 75 $testConditions[] = 'OR(' . implode(',', $testCondition) . ')'; 76 ++$testConditionsCount; 77 } elseif ($testConditionCount == 1) { 78 $testConditions[] = $testCondition[0]; 79 ++$testConditionsCount; 80 } 81 } 82 83 if ($testConditionsCount > 1) { 84 $testConditionSet = 'AND(' . implode(',', $testConditions) . ')'; 85 } elseif ($testConditionsCount == 1) { 86 $testConditionSet = $testConditions[0]; 87 } 88 89 // Loop through each row of the database 90 foreach ($database as $dataRow => $dataValues) { 91 // Substitute actual values from the database row for our [:placeholders] 92 $testConditionList = $testConditionSet; 93 foreach ($criteriaNames as $key => $criteriaName) { 94 $k = array_search($criteriaName, $fieldNames); 95 if (isset($dataValues[$k])) { 96 $dataValue = $dataValues[$k]; 97 $dataValue = (is_string($dataValue)) ? Calculation::wrapResult(strtoupper($dataValue)) : $dataValue; 98 $testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList); 99 } 100 } 101 // evaluate the criteria against the row data 102 $result = Calculation::getInstance()->_calculateFormulaValue('=' . $testConditionList); 103 // If the row failed to meet the criteria, remove it from the database 104 if (!$result) { 105 unset($database[$dataRow]); 106 } 107 } 108 109 return $database; 110 } 111 112 private static function getFilteredColumn($database, $field, $criteria) 113 { 114 // reduce the database to a set of rows that match all the criteria 115 $database = self::filter($database, $criteria); 116 // extract an array of values for the requested column 117 $colData = []; 118 foreach ($database as $row) { 119 $colData[] = $row[$field]; 120 } 121 122 return $colData; 123 } 124 125 /** 126 * DAVERAGE. 127 * 128 * Averages the values in a column of a list or database that match conditions you specify. 129 * 130 * Excel Function: 131 * DAVERAGE(database,field,criteria) 132 * 133 * @param mixed[] $database The range of cells that makes up the list or database. 134 * A database is a list of related data in which rows of related 135 * information are records, and columns of data are fields. The 136 * first row of the list contains labels for each column. 137 * @param int|string $field Indicates which column is used in the function. Enter the 138 * column label enclosed between double quotation marks, such as 139 * "Age" or "Yield," or a number (without quotation marks) that 140 * represents the position of the column within the list: 1 for 141 * the first column, 2 for the second column, and so on. 142 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 143 * You can use any range for the criteria argument, as long as it 144 * includes at least one column label and at least one cell below 145 * the column label in which you specify a condition for the 146 * column. 147 * 148 * @return float|string 149 */ 150 public static function DAVERAGE($database, $field, $criteria) 151 { 152 $field = self::fieldExtract($database, $field); 153 if ($field === null) { 154 return null; 155 } 156 157 // Return 158 return Statistical::AVERAGE( 159 self::getFilteredColumn($database, $field, $criteria) 160 ); 161 } 162 163 /** 164 * DCOUNT. 165 * 166 * Counts the cells that contain numbers in a column of a list or database that match conditions 167 * that you specify. 168 * 169 * Excel Function: 170 * DCOUNT(database,[field],criteria) 171 * 172 * Excel Function: 173 * DAVERAGE(database,field,criteria) 174 * 175 * @param mixed[] $database The range of cells that makes up the list or database. 176 * A database is a list of related data in which rows of related 177 * information are records, and columns of data are fields. The 178 * first row of the list contains labels for each column. 179 * @param int|string $field Indicates which column is used in the function. Enter the 180 * column label enclosed between double quotation marks, such as 181 * "Age" or "Yield," or a number (without quotation marks) that 182 * represents the position of the column within the list: 1 for 183 * the first column, 2 for the second column, and so on. 184 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 185 * You can use any range for the criteria argument, as long as it 186 * includes at least one column label and at least one cell below 187 * the column label in which you specify a condition for the 188 * column. 189 * 190 * @return int 191 * 192 * @TODO The field argument is optional. If field is omitted, DCOUNT counts all records in the 193 * database that match the criteria. 194 */ 195 public static function DCOUNT($database, $field, $criteria) 196 { 197 $field = self::fieldExtract($database, $field); 198 if ($field === null) { 199 return null; 200 } 201 202 // Return 203 return Statistical::COUNT( 204 self::getFilteredColumn($database, $field, $criteria) 205 ); 206 } 207 208 /** 209 * DCOUNTA. 210 * 211 * Counts the nonblank cells in a column of a list or database that match conditions that you specify. 212 * 213 * Excel Function: 214 * DCOUNTA(database,[field],criteria) 215 * 216 * @param mixed[] $database The range of cells that makes up the list or database. 217 * A database is a list of related data in which rows of related 218 * information are records, and columns of data are fields. The 219 * first row of the list contains labels for each column. 220 * @param int|string $field Indicates which column is used in the function. Enter the 221 * column label enclosed between double quotation marks, such as 222 * "Age" or "Yield," or a number (without quotation marks) that 223 * represents the position of the column within the list: 1 for 224 * the first column, 2 for the second column, and so on. 225 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 226 * You can use any range for the criteria argument, as long as it 227 * includes at least one column label and at least one cell below 228 * the column label in which you specify a condition for the 229 * column. 230 * 231 * @return int 232 * 233 * @TODO The field argument is optional. If field is omitted, DCOUNTA counts all records in the 234 * database that match the criteria. 235 */ 236 public static function DCOUNTA($database, $field, $criteria) 237 { 238 $field = self::fieldExtract($database, $field); 239 if ($field === null) { 240 return null; 241 } 242 243 // reduce the database to a set of rows that match all the criteria 244 $database = self::filter($database, $criteria); 245 // extract an array of values for the requested column 246 $colData = []; 247 foreach ($database as $row) { 248 $colData[] = $row[$field]; 249 } 250 251 // Return 252 return Statistical::COUNTA( 253 self::getFilteredColumn($database, $field, $criteria) 254 ); 255 } 256 257 /** 258 * DGET. 259 * 260 * Extracts a single value from a column of a list or database that matches conditions that you 261 * specify. 262 * 263 * Excel Function: 264 * DGET(database,field,criteria) 265 * 266 * @param mixed[] $database The range of cells that makes up the list or database. 267 * A database is a list of related data in which rows of related 268 * information are records, and columns of data are fields. The 269 * first row of the list contains labels for each column. 270 * @param int|string $field Indicates which column is used in the function. Enter the 271 * column label enclosed between double quotation marks, such as 272 * "Age" or "Yield," or a number (without quotation marks) that 273 * represents the position of the column within the list: 1 for 274 * the first column, 2 for the second column, and so on. 275 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 276 * You can use any range for the criteria argument, as long as it 277 * includes at least one column label and at least one cell below 278 * the column label in which you specify a condition for the 279 * column. 280 * 281 * @return mixed 282 */ 283 public static function DGET($database, $field, $criteria) 284 { 285 $field = self::fieldExtract($database, $field); 286 if ($field === null) { 287 return null; 288 } 289 290 // Return 291 $colData = self::getFilteredColumn($database, $field, $criteria); 292 if (count($colData) > 1) { 293 return Functions::NAN(); 294 } 295 296 return $colData[0]; 297 } 298 299 /** 300 * DMAX. 301 * 302 * Returns the largest number in a column of a list or database that matches conditions you that 303 * specify. 304 * 305 * Excel Function: 306 * DMAX(database,field,criteria) 307 * 308 * @param mixed[] $database The range of cells that makes up the list or database. 309 * A database is a list of related data in which rows of related 310 * information are records, and columns of data are fields. The 311 * first row of the list contains labels for each column. 312 * @param int|string $field Indicates which column is used in the function. Enter the 313 * column label enclosed between double quotation marks, such as 314 * "Age" or "Yield," or a number (without quotation marks) that 315 * represents the position of the column within the list: 1 for 316 * the first column, 2 for the second column, and so on. 317 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 318 * You can use any range for the criteria argument, as long as it 319 * includes at least one column label and at least one cell below 320 * the column label in which you specify a condition for the 321 * column. 322 * 323 * @return float 324 */ 325 public static function DMAX($database, $field, $criteria) 326 { 327 $field = self::fieldExtract($database, $field); 328 if ($field === null) { 329 return null; 330 } 331 332 // Return 333 return Statistical::MAX( 334 self::getFilteredColumn($database, $field, $criteria) 335 ); 336 } 337 338 /** 339 * DMIN. 340 * 341 * Returns the smallest number in a column of a list or database that matches conditions you that 342 * specify. 343 * 344 * Excel Function: 345 * DMIN(database,field,criteria) 346 * 347 * @param mixed[] $database The range of cells that makes up the list or database. 348 * A database is a list of related data in which rows of related 349 * information are records, and columns of data are fields. The 350 * first row of the list contains labels for each column. 351 * @param int|string $field Indicates which column is used in the function. Enter the 352 * column label enclosed between double quotation marks, such as 353 * "Age" or "Yield," or a number (without quotation marks) that 354 * represents the position of the column within the list: 1 for 355 * the first column, 2 for the second column, and so on. 356 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 357 * You can use any range for the criteria argument, as long as it 358 * includes at least one column label and at least one cell below 359 * the column label in which you specify a condition for the 360 * column. 361 * 362 * @return float 363 */ 364 public static function DMIN($database, $field, $criteria) 365 { 366 $field = self::fieldExtract($database, $field); 367 if ($field === null) { 368 return null; 369 } 370 371 // Return 372 return Statistical::MIN( 373 self::getFilteredColumn($database, $field, $criteria) 374 ); 375 } 376 377 /** 378 * DPRODUCT. 379 * 380 * Multiplies the values in a column of a list or database that match conditions that you specify. 381 * 382 * Excel Function: 383 * DPRODUCT(database,field,criteria) 384 * 385 * @param mixed[] $database The range of cells that makes up the list or database. 386 * A database is a list of related data in which rows of related 387 * information are records, and columns of data are fields. The 388 * first row of the list contains labels for each column. 389 * @param int|string $field Indicates which column is used in the function. Enter the 390 * column label enclosed between double quotation marks, such as 391 * "Age" or "Yield," or a number (without quotation marks) that 392 * represents the position of the column within the list: 1 for 393 * the first column, 2 for the second column, and so on. 394 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 395 * You can use any range for the criteria argument, as long as it 396 * includes at least one column label and at least one cell below 397 * the column label in which you specify a condition for the 398 * column. 399 * 400 * @return float 401 */ 402 public static function DPRODUCT($database, $field, $criteria) 403 { 404 $field = self::fieldExtract($database, $field); 405 if ($field === null) { 406 return null; 407 } 408 409 // Return 410 return MathTrig::PRODUCT( 411 self::getFilteredColumn($database, $field, $criteria) 412 ); 413 } 414 415 /** 416 * DSTDEV. 417 * 418 * Estimates the standard deviation of a population based on a sample by using the numbers in a 419 * column of a list or database that match conditions that you specify. 420 * 421 * Excel Function: 422 * DSTDEV(database,field,criteria) 423 * 424 * @param mixed[] $database The range of cells that makes up the list or database. 425 * A database is a list of related data in which rows of related 426 * information are records, and columns of data are fields. The 427 * first row of the list contains labels for each column. 428 * @param int|string $field Indicates which column is used in the function. Enter the 429 * column label enclosed between double quotation marks, such as 430 * "Age" or "Yield," or a number (without quotation marks) that 431 * represents the position of the column within the list: 1 for 432 * the first column, 2 for the second column, and so on. 433 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 434 * You can use any range for the criteria argument, as long as it 435 * includes at least one column label and at least one cell below 436 * the column label in which you specify a condition for the 437 * column. 438 * 439 * @return float|string 440 */ 441 public static function DSTDEV($database, $field, $criteria) 442 { 443 $field = self::fieldExtract($database, $field); 444 if ($field === null) { 445 return null; 446 } 447 448 // Return 449 return Statistical::STDEV( 450 self::getFilteredColumn($database, $field, $criteria) 451 ); 452 } 453 454 /** 455 * DSTDEVP. 456 * 457 * Calculates the standard deviation of a population based on the entire population by using the 458 * numbers in a column of a list or database that match conditions that you specify. 459 * 460 * Excel Function: 461 * DSTDEVP(database,field,criteria) 462 * 463 * @param mixed[] $database The range of cells that makes up the list or database. 464 * A database is a list of related data in which rows of related 465 * information are records, and columns of data are fields. The 466 * first row of the list contains labels for each column. 467 * @param int|string $field Indicates which column is used in the function. Enter the 468 * column label enclosed between double quotation marks, such as 469 * "Age" or "Yield," or a number (without quotation marks) that 470 * represents the position of the column within the list: 1 for 471 * the first column, 2 for the second column, and so on. 472 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 473 * You can use any range for the criteria argument, as long as it 474 * includes at least one column label and at least one cell below 475 * the column label in which you specify a condition for the 476 * column. 477 * 478 * @return float|string 479 */ 480 public static function DSTDEVP($database, $field, $criteria) 481 { 482 $field = self::fieldExtract($database, $field); 483 if ($field === null) { 484 return null; 485 } 486 487 // Return 488 return Statistical::STDEVP( 489 self::getFilteredColumn($database, $field, $criteria) 490 ); 491 } 492 493 /** 494 * DSUM. 495 * 496 * Adds the numbers in a column of a list or database that match conditions that you specify. 497 * 498 * Excel Function: 499 * DSUM(database,field,criteria) 500 * 501 * @param mixed[] $database The range of cells that makes up the list or database. 502 * A database is a list of related data in which rows of related 503 * information are records, and columns of data are fields. The 504 * first row of the list contains labels for each column. 505 * @param int|string $field Indicates which column is used in the function. Enter the 506 * column label enclosed between double quotation marks, such as 507 * "Age" or "Yield," or a number (without quotation marks) that 508 * represents the position of the column within the list: 1 for 509 * the first column, 2 for the second column, and so on. 510 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 511 * You can use any range for the criteria argument, as long as it 512 * includes at least one column label and at least one cell below 513 * the column label in which you specify a condition for the 514 * column. 515 * 516 * @return float 517 */ 518 public static function DSUM($database, $field, $criteria) 519 { 520 $field = self::fieldExtract($database, $field); 521 if ($field === null) { 522 return null; 523 } 524 525 // Return 526 return MathTrig::SUM( 527 self::getFilteredColumn($database, $field, $criteria) 528 ); 529 } 530 531 /** 532 * DVAR. 533 * 534 * Estimates the variance of a population based on a sample by using the numbers in a column 535 * of a list or database that match conditions that you specify. 536 * 537 * Excel Function: 538 * DVAR(database,field,criteria) 539 * 540 * @param mixed[] $database The range of cells that makes up the list or database. 541 * A database is a list of related data in which rows of related 542 * information are records, and columns of data are fields. The 543 * first row of the list contains labels for each column. 544 * @param int|string $field Indicates which column is used in the function. Enter the 545 * column label enclosed between double quotation marks, such as 546 * "Age" or "Yield," or a number (without quotation marks) that 547 * represents the position of the column within the list: 1 for 548 * the first column, 2 for the second column, and so on. 549 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 550 * You can use any range for the criteria argument, as long as it 551 * includes at least one column label and at least one cell below 552 * the column label in which you specify a condition for the 553 * column. 554 * 555 * @return float|string (string if result is an error) 556 */ 557 public static function DVAR($database, $field, $criteria) 558 { 559 $field = self::fieldExtract($database, $field); 560 if ($field === null) { 561 return null; 562 } 563 564 // Return 565 return Statistical::VARFunc( 566 self::getFilteredColumn($database, $field, $criteria) 567 ); 568 } 569 570 /** 571 * DVARP. 572 * 573 * Calculates the variance of a population based on the entire population by using the numbers 574 * in a column of a list or database that match conditions that you specify. 575 * 576 * Excel Function: 577 * DVARP(database,field,criteria) 578 * 579 * @param mixed[] $database The range of cells that makes up the list or database. 580 * A database is a list of related data in which rows of related 581 * information are records, and columns of data are fields. The 582 * first row of the list contains labels for each column. 583 * @param int|string $field Indicates which column is used in the function. Enter the 584 * column label enclosed between double quotation marks, such as 585 * "Age" or "Yield," or a number (without quotation marks) that 586 * represents the position of the column within the list: 1 for 587 * the first column, 2 for the second column, and so on. 588 * @param mixed[] $criteria The range of cells that contains the conditions you specify. 589 * You can use any range for the criteria argument, as long as it 590 * includes at least one column label and at least one cell below 591 * the column label in which you specify a condition for the 592 * column. 593 * 594 * @return float|string (string if result is an error) 595 */ 596 public static function DVARP($database, $field, $criteria) 597 { 598 $field = self::fieldExtract($database, $field); 599 if ($field === null) { 600 return null; 601 } 602 603 // Return 604 return Statistical::VARP( 605 self::getFilteredColumn($database, $field, $criteria) 606 ); 607 } 608 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body