f54f67c654c39f4fe1aada15e63664fad588f170.svn-base 27 KB

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