Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

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