微型电子表格，求和平均数和 vlookup
跨单元格求和等于 常数系数*乘积元素/除元素
use strict; use warnings;
my @papera = ();#-1, 1,3,5,2,6,3,4,5,7,3, 5,1,2,5,7,3,5,8,5,1 ,4,3,7,4,8,4,3,3,2,2, 1,29,2,3,5,7,1,9,8,6);
my @expga = ();
my @pfxa = ();
my @mupa = ();
my @diva = ();
# duoxiangshi jisuan { {Prefix, {MP1,MP2,MP3..}, {DIV1,DIV2,DIV3..} }, {..}, {..} }
for (my $xjj = 0; $xjj < 5000; $xjj += 1){
push(@papera, 0);
push(@expga, 0);
push(@pfxa, 0);
push(@mupa, 0);
push(@diva, 0);
}
my $uinp = undef;
my @ucmd = ();
my $hya = undef;
my $hda = undef;
my $hdb = undef;
my $hdm = undef;
#my @bb = ("sum", "`2`4`5`9"); #`2`5`2`3
#$expga[2] = \@bb;
sub str2int {
my $ss = shift;
my $num = shift;
my $smode = shift;
my $ys = "";
my $sj = 0;
while ($sj < length($ss)){
my $sc = substr($ss, $sj, 1);
if ( $smode == 1) {
if ($sc eq "i"){
$ys = $ys.$num;
} else {$ys = $ys.$sc;}
} elsif ($smode == 2){
if ($sc eq "j"){
$ys = $ys.$num;
} else {$ys = $ys.$sc;}
}
$sj += 1;
}
#printf($ys." xx\n");
return $ys;
}
sub squr {
my $fp = shift;
my $da = index($fp, "_", 1);
my $db = index($fp, "_", $da+1);
my $dc = index($fp, "_", $db+1);
my $xa = int(substr($fp, 1, $da-1));
my $xb = int(substr($fp, $da+1, $db-$da-1)); #heng
my $xc = int(substr($fp, $db+1, $dc-$db-1));
my $xd = int(substr($fp, $dc+1)); #shu
my @fdr = ($xa, $xb, $xc, $xd);
my $fm = \@fdr;
return $fm;
}
sub gb {
my $yf = 0;
my $fn = shift;
my $fp = shift;
my $fp2 = shift;
my $wa = undef;
my $rf = 0;
if ($fn == 1){ #sum
my $fm = squr($fp);
my @fdr = @$fm;
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
$rf += $papera[$wa];
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
} elsif ($fn == 11){ #sumifge
my $fm = squr($fp);
my @fdr = @$fm;
$yf = scalar($fp2);
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] > $yf){
$rf += $papera[$wa];
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
}elsif ($fn == 21){ #sumif
my $fm = squr($fp);
my @fdr = @$fm;
$yf = scalar($fp2);
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] == $yf){
$rf += $papera[$wa];
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
} elsif ($fn == 2){ #avrg
my $fm = squr($fp);
my @fdr = @$fm;
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
$rf += $papera[$wa];
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
$rf = $rf / ($fdr[1]-$fdr[0]+1)/($fdr[3]-$fdr[2]+1);
return $rf;
} elsif ($fn == 12){ #avrgge
my $fm = squr($fp);
my @fdr = @$fm;
$yf = scalar($fp2);
my $ym = 0;
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] > $yf){
$rf += $papera[$wa];
$ym += 1;
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
$rf = $rf / $ym;
return $rf;
}elsif ($fn == 22){ #avrgeq
my $fm = squr($fp);
my @fdr = @$fm;
$yf = scalar($fp2);
my $ym = 0;
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] == $yf){
$rf += $papera[$wa];
$ym += 1;
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
$rf = $rf / $ym;
return $rf;
} elsif ($fn == 3){ #count
my $fm = squr($fp);
my @fdr = @$fm;
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] == $fp2*1.0 ){
$rf += 1;
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
} elsif ($fn == 13){ #countge
my $fm = squr($fp);
my @fdr = @$fm;
$yf = scalar($fp2);
$rf = 0;
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] > $yf ){
$rf += 1;
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
} elsif ($fn == 4){ #max
my $fm = squr($fp);
my @fdr = @$fm;
$rf = $papera[($fdr[2]-1)*10+$fdr[0]];
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] > $rf ){
$rf = $papera[$wa];
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
} elsif ($fn == 5){ #min
my $fm = squr($fp);
my @fdr = @$fm;
$rf = $papera[($fdr[2]-1)*10+$fdr[0]];
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
my $pb = $fdr[0];
while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$pb;
if ($papera[$wa] < $rf ){
$rf = $papera[$wa];
}
$pb += 1;
}
$pb = $fdr[0];
$pa += 1;
}
return $rf;
} elsif ($fn == 6){ #vlookup
my $fm = squr($fp);
my @fdr = @$fm;
$rf = $papera[($fdr[2]-1)*10+$fdr[0]];
my $pa = $fdr[2];
while ($pa <= $fdr[3]){
#while ($pb <= $fdr[1]){
$wa = ($pa-1)*10+$fdr[0];
if ($papera[$wa] == scalar($fp2) ){
my $fp3 = shift;
$hya = index($fp3, "_", 1);
$hda = int(substr($fp3, 1, $hya-1));
$hdb = int(substr($fp3, $hya+1));
$hdm = $hda+($hdb+$pa-$fdr[2])*10;
return $papera[$hdm];
} else {
$pa += 1;
}
#}
#$pb = $fdr[0];
}
return $rf;
}
}
sub oneline {
my $yf = 0;
my $rf = 0;
my $uinp = shift;
my $upj = index($uinp, "@"); # pre formatted command after '@'
my $prefc = undef;
if ($upj > -1 ){
$prefc = substr($uinp, $upj+1);
$uinp = substr($uinp, 0, $upj);
}
@ucmd = split(/,/, $uinp);
if ($ucmd[0] eq "vw"){ # see cell's true value
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
printf("%f\n", $papera[$hdm]);
} elsif ($ucmd[0] eq "vf"){ # see cell's expression
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
my $utmex = $expga[int($hdm)];
my $utss = "none";
if (defined($utmex)){
my @utmep = @$utmex;
$utss = $utmep[0]."(".$utmep[1].")";
}
printf("%s\n", $utss);
}elsif ($ucmd[0] eq "sw"){ # set cell's value, OR simple math exp
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
$yf = eval($ucmd[2]);
$papera[$hdm] = $yf;
$expga[$hdm] = ();
} elsif ($ucmd[0] eq "updf"){
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
if (defined($ucmd[2])){
$upj = 3;
my @uypr = ();
if ($ucmd[2] eq "sum"){
push(@uypr, 1);
} elsif ($ucmd[2] eq "avrg"){
push(@uypr, 2);
} elsif ($ucmd[2] eq "count"){
push(@uypr, 3);
} elsif ($ucmd[2] eq "max"){
push(@uypr, 4);
} elsif ($ucmd[2] eq "min"){
push(@uypr, 5);
}
while ($upj < scalar(@ucmd)){
push(@uypr, $ucmd[$upj]);
$upj += 1;
}
my $uyf = \@uypr;
$expga[$hdm] = $uyf;
my $rf = gb(@uypr);
$papera[$hdm] = $rf;
} else {
my $utcm = $expga[$hdm];
my $xxf = gb(@$utcm);
$papera[$hdm] = $xxf;
}
} elsif ($ucmd[0] eq "ifge"){ # bigger than a Num or a Cell's value
if (substr($ucmd[2], 0, 1) eq "_") {
# bigger than a Cell's value
$hya = index($ucmd[2], "_", 1);
$hda = int(substr($ucmd[2], 1, $hya-1));
$hdb = int(substr($ucmd[2], $hya+1));
$hdm = $hdb*10-10+$hda;
$yf = $papera[$hdm];
} else {
$yf = scalar($ucmd[2]);
}
if ($ucmd[3] eq "sum"){
$rf = gb(11, $ucmd[4], $yf );
} elsif ($ucmd[3] eq "avrg"){
$rf = gb(12, $ucmd[4], $yf );
} elsif ($ucmd[3] eq "count"){
$rf = gb(13, $ucmd[4], $yf );
} else {
printf("warn!");
}
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
$papera[$hdm] = $rf;
$expga[$hdm] = ();
} elsif ($ucmd[0] eq "ifeq"){ # equal to a Num or a Cell's value
if (substr($ucmd[2], 0, 1) eq "_") {
# bigger than a Cell's value
$hya = index($ucmd[2], "_", 1);
$hda = int(substr($ucmd[2], 1, $hya-1));
$hdb = int(substr($ucmd[2], $hya+1));
$hdm = $hdb*10-10+$hda;
$yf = $papera[$hdm];
} else {
$yf = scalar($ucmd[2]);
}
if ($ucmd[3] eq "sum"){
$rf = gb(21, $ucmd[4], $yf );
} elsif ($ucmd[3] eq "avrg"){
$rf = gb(22, $ucmd[4], $yf );
} elsif ($ucmd[3] eq "count"){
$rf = gb(23, $ucmd[4], $yf );
} else {
printf("warn!");
}
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
$papera[$hdm] = $rf;
$expga[$hdm] = ();
} elsif ($ucmd[0] eq "vlookup"){ #
my @uypr = (6, $ucmd[2], $ucmd[3], $ucmd[4] );
my $uypf = \@uypr;
$yf = gb(@uypr);
#printf($yf."\n");
$hya = index($ucmd[1], "_", 1);
$hda = int(substr($ucmd[1], 1, $hya-1));
$hdb = int(substr($ucmd[1], $hya+1));
$hdm = $hdb*10-10+$hda;
$papera[$hdm] = $yf;
$expga[$hdm] = $uypf;
} elsif ($ucmd[0] eq "autov"){ #
my $ka = int($ucmd[1]);
my $kaa = int($ucmd[2]);
my $kb = int($ucmd[3]);
my $kbb = int($ucmd[4]);
my $kca = $ka;
my $kcb = $kb;
my $kc = "";
#my @kad = ();
while ($kca <= $kaa){
$kc = str2int($prefc, $kca, 1);
while ($kcb <= $kbb){
my $kcc = str2int($kc, $kcb, 2);
oneline($kcc);
$kcb += 1;
}
$kcb = $kb;
$kca += 1;
}
}
}
while (1){
printf("...\n");
$uinp = <STDIN>;
chomp($uinp);
my $upj = index($uinp, ";"); # command end with a ';'
if ($upj > -1 ){
$uinp = substr($uinp, 0, $upj);
}
oneline($uinp);
}