Hallo liebes Community,
leider bin nicht so fit in Excel und brauche eure Unterstützung. Ich habe folgende Frage zu lösen:
So seht ungefähr meine Tabelle aus:
Datum | Filiale1| Filieale 2
-----------------|------------|--------------
01.01.2014 | 1200 | 1350
01.01.2014 | 1150 | 1400
02.01.2014 | 900 | 500
01.01.2014 | 790 | 900
05.01.2014 | 905 | 700
…...............................
Es gibt mehrere Einträge pro Tag. Ich muss pro Filiale pro Tag die Summen berechnen. Genauso pro Filiale pro Monat und die Gesamtsummen.
Wie kann ich mit Excel diese berechnen?
Mit wenn und Summenprodukt
Mit wenn und Summenprodukt kannst du die Tagessummen in einer Spalte anzeigen lassen. Hier ist Formel dazu:
=WENN(A2>A3;SUMMENPRODUKT((A$2:A$1001=A2)*(B$2:D$1001));"")
wenn deine Spalten anders aussehen, muss d die Spalten und Zeilenbezeichnungen überarbeiten. Hier ist noch Screenshot dazu:
excel-summenprodukt-wenn-tagessumme.jpg
Comments
Hallo Di, 04/25/2017 - 08:54
import java.math.BigInteger;
import java.util.HashSet;
import java.util.Locale;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Serviceroutinen um Banknummer wie z.B. IBAN zu checken.
*
*/
public class BankingNumberValidation
{
final static private Set ISO_COUNTRY_CODES = new HashSet();
final static public int NOT_AN_IBAN = 0;
final static public int BAD_IBAN_CHECKNUM = 1;
final static public int GOOD_IBAN = 2;
final static public int EU_IBAN = 3;
final static public int NOT_A_BIC = 0;
final static public int GOOD_BIC = 1;
final static public int EU_BIC = 2;
final static private BigInteger BIG_INT_97 = new BigInteger("97");
final static private BigInteger BIG_INT_1 = new BigInteger("1");
final static private Pattern IBAN_PAT = Pattern.compile(("([A-Z]{2})([0-9]{2})([\\w]{1,30})"));
static
{
String[] cCodes = Locale.getISOCountries();
for (int i=0; i<cCodes.length; i++)
{
ISO_COUNTRY_CODES.add(cCodes[i]);
}
}
/**
* checks if the given IBAN seems to be an iban (without countrycode-check)
* and validates the checknumber
* this routine is a bit cheap but also fast since no country-codes are checked
*
* @param iban
* @return (int) NOT_AN_IBAN , if given String does not matche basic IBAN-format
* BAD_IBAN_CHECKNUM , if basic IBAN-format is OK but not the checknumber
* GOOD_IBAN , if basic IBAN-format and hecknumber are OK
*/
static public int isIBANCheckNumberOK(String iban)
{
int rc;
Matcher matcher = IBAN_PAT.matcher(iban);
if (matcher.matches())
{
StringBuffer ibanBuff = new StringBuffer(34);
StringBuffer valueBuff = new StringBuffer(34);
ibanBuff.append(matcher.group(3));
ibanBuff.append(matcher.group(1));
ibanBuff.append(matcher.group(2));
for(int i=0; i<ibanBuff.length(); i++)
{
int v;
char c =ibanBuff.charAt(i);
if ((c>='A' && c<='Z'))
{
v = c - 'A' + 10;
valueBuff.append(v);
}
else if ((c>='a' && c<='z'))
{
v = c - 'a' + 10;
valueBuff.append(v);
}
else
{
valueBuff.append(c);
}
}
BigInteger val = new BigInteger(valueBuff.toString());
rc = val.divideAndRemainder(BIG_INT_97)[1].equals(BIG_INT_1)? GOOD_IBAN : BAD_IBAN_CHECKNUM;
}
else
{
rc = NOT_AN_IBAN;
}
return rc;
}
/**
* checks if the given IBAN seems to be an iban with countrycode-check
* and validates the checknumber
* if this validation is allright it is checked whether the country-code is
* sorts to european regulations payment area or not
*
* the higher the returned value the better the IBAN so if you just want to know
* if the IBAN is allright but don't care about the european-area you just
* do something like this:
* <code>
* if (BankingNumberValidation.isIBAN(iban) >= GOOD_IBAN)
* {
* ... do something
* }
* </code>
*
* @param iban
* @return (int) NOT_AN_IBAN , if given String does not matche basic IBAN-format or countrycod is unkown
* BAD_IBAN_CHECKNUM , if basic IBAN-format and countrycode are OK but not the checknumber
* GOOD_IBAN , if basic IBAN-format, countrycode and checknumber are OK
* EU_IBAN , if basic IBAN-format, countrycode, checknumber are OK and the countrycode is european
*
* @see de.cmk.util.banking.EUCountryCodes.isIbanEUCountry
*/
static public int isIBAN(String iban)
{
int rc;
Matcher matcher = IBAN_PAT.matcher(iban);
if (matcher.matches() && ISO_COUNTRY_CODES.contains(iban.substring(0,2)))
{
StringBuffer ibanBuff = new StringBuffer(34);
StringBuffer valueBuff = new StringBuffer(34);
ibanBuff.append(matcher.group(3));
ibanBuff.append(matcher.group(1));
ibanBuff.append(matcher.group(2));
for(int i=0; i<ibanBuff.length(); i++)
{
int v;
char c =ibanBuff.charAt(i);
if ((c>='A' && c<='Z'))
{
v = c - 'A' + 10;
valueBuff.append(v);
}
else if ((c>='a' && c<='z'))
{
v = c - 'a' + 10;
valueBuff.append(v);
}
else
{
valueBuff.append(c);
}
}
BigInteger val = new BigInteger(valueBuff.toString());
if (val.divideAndRemainder(BIG_INT_97)[1].equals(BIG_INT_1))
{
if (EUCountryCodes.isIbanEUCountry(iban))
{
rc = EU_IBAN;
}
else
{
rc = GOOD_IBAN;
}
}
else
{
rc = BAD_IBAN_CHECKNUM;
}
}
else
{
rc = NOT_AN_IBAN;
}
return rc;
}
/**
* very simple formal check on given bic
* cheks if the countrycode is correct or even european
* assumes thta a bic is 11 or 8 characters long and
* an that the 4. and 5. offset is the country-code
* @param bic
* @return
*/
static public int isBIC(String bic)
{
int rc ;
if (bic.length()==11 || bic.length()==8)
{
if (EUCountryCodes.isBicEUCountry(bic))
{
rc = EU_BIC;
}
else if (ISO_COUNTRY_CODES.contains(bic))
{
rc = GOOD_BIC;
}
else
{
rc = NOT_A_BIC;
}
}
else
{
rc = NOT_A_BIC;
}
return rc;
}
public static void main(String argv[])
{
System.out.println(argv[0]+" isBIC:"+ isBIC(argv[0]));
System.out.println(argv[0]+" isIBAN:"+ isIBAN(argv[0]));
}
}
import java.util.HashSet;
import java.util.Set;
public class EUCountryCodes
{
static private Set m_ibanCountryCodes = new HashSet();
static private Set m_bicCountryCodes = new HashSet();
static
{
m_ibanCountryCodes.add("AT");
m_ibanCountryCodes.add("BE");
m_ibanCountryCodes.add("CY");
m_ibanCountryCodes.add("CZ");
m_ibanCountryCodes.add("DE");
m_ibanCountryCodes.add("DK");
m_ibanCountryCodes.add("EE");
m_ibanCountryCodes.add("ES");
m_ibanCountryCodes.add("FI");
m_ibanCountryCodes.add("FR");
m_ibanCountryCodes.add("GB");
m_ibanCountryCodes.add("GI");
m_ibanCountryCodes.add("GR");
m_ibanCountryCodes.add("HU");
m_ibanCountryCodes.add("IE");
m_ibanCountryCodes.add("IS");
m_ibanCountryCodes.add("IT");
m_ibanCountryCodes.add("LI");
m_ibanCountryCodes.add("LT");
m_ibanCountryCodes.add("LU");
m_ibanCountryCodes.add("LV");
m_ibanCountryCodes.add("MT");
m_ibanCountryCodes.add("NL");
m_ibanCountryCodes.add("NO");
m_ibanCountryCodes.add("PL");
m_ibanCountryCodes.add("PT");
m_ibanCountryCodes.add("SE");
m_ibanCountryCodes.add("SI");
m_ibanCountryCodes.add("SK");
m_bicCountryCodes.add("AT");
m_bicCountryCodes.add("BE");
m_bicCountryCodes.add("CY");
m_bicCountryCodes.add("CZ");
m_bicCountryCodes.add("DE");
m_bicCountryCodes.add("DK");
m_bicCountryCodes.add("EE");
m_bicCountryCodes.add("ES");
m_bicCountryCodes.add("FI");
m_bicCountryCodes.add("FR");
m_bicCountryCodes.add("GB");
m_bicCountryCodes.add("IE");
m_bicCountryCodes.add("GF");
m_bicCountryCodes.add("GI");
m_bicCountryCodes.add("GP");
m_bicCountryCodes.add("GR");
m_bicCountryCodes.add("HU");
m_bicCountryCodes.add("IE");
m_bicCountryCodes.add("IS");
m_bicCountryCodes.add("IT");
m_bicCountryCodes.add("LI");
m_bicCountryCodes.add("LT");
m_bicCountryCodes.add("LU");
m_bicCountryCodes.add("LV");
m_bicCountryCodes.add("MQ");
m_bicCountryCodes.add("MT");
m_bicCountryCodes.add("NL");
m_bicCountryCodes.add("NO");
m_bicCountryCodes.add("PL");
m_bicCountryCodes.add("PT");
m_bicCountryCodes.add("RE");
m_bicCountryCodes.add("SE");
m_bicCountryCodes.add("SI");
m_bicCountryCodes.add("SK");
}
static public boolean isBicEUCountry(String bic)
{
return m_bicCountryCodes.contains(bic.substring(4,6));
}
static public boolean isIbanEUCountry(String iban)
{
return m_ibanCountryCodes.contains(iban.substring(0,2));
}
}
/**
*
* Beschreibung
*
* @version 1.0 vom 25.04.2017
* @author
*/
public class main {
public static void main(String[] args) {
new BankingNumberValidation();
} // end of main
} // end of class main
Neuen Kommentar schreiben