ich habe eine Exceltabelle mit Bayerischen Bevölkerungszahlen nach Gemeinden . Diese sollen Größenklassen zugeordnet werden wie folgt:
Diese kommt einmal im Jahr mit neuen Zahlen, aber ohne Größenklassenzuordnung. Es sind über 2000 Datensätze.
Gibt es in excel Möglichkeit per Makro o. Formel diese Zuordnungslogik zu hinterlegen im neuen Arbeitsblatt, wo dann nur die neuen Daten reinkopiert werden ?
Sonst hat man manuell die Zahlen zu sortieren und in extra Spalte die Größenklasse zu hinterlegen.
Bevölkerungsanzahl u. Größenklasse
< 20000 1
20000 bis 100000 2
100000 bis 500000 3
> 500000 4
0 5 (gemeindefrei)
Zahlen Bsp:
Gebiet Insgesamt
Altötting, St 102389
Burghausen, St 9877
Burgkirchen a.d.Alz 4302
Emmerting 17470
Erlbach 3037
Feichten a.d.Alz 7597
Garching a.d.Alz 7527
Haiming 25936
Halsbach 6600
Ich habe mich versucht. In Spalte A findest Du die Orte und in B die Anzahl der Einwohner. Die 6 Größenklassen habe ich ab Spalte E2 hinterlegt und in F mit einer Nummer bezeichnet. In Spalte C wird jetzt über VERGLEICH jedem Ort eine Klasse zugewiesen: VERGLEICH(B1;$E$2:$E$7). Und in Spalte H werden die Anzahlen je Klasse gebildet ZÄHLENWENN(C$1:C$99;"=1") "2", "3; ETC: Entspricht das in etwas Deinen Erwartungen?
einen schönen Gruss in den Süden nd hier ein Makro zum auflisten. Ggf. die Spalte selbst anpassen!
Meine Daten stehen in "Tabelle1" in der Spalte A2:Axxx. In Spalte A steht der Gemeindename, Spalte B die Einwohnerzahl, Saplte C die Auswertung. Sollten es in deiner Datei andere Spalten sein bitte den Buchstaben aendern. Das ist alles. Die Nachbarspalte wird über Offset(0,1) für Spalte B und Offset(0,2) für C erreicht. In ein naormales Modul kopieren und am besten über einen normalen CommandButton starten. (kein AktiveX Element!)
mfg Gast 123
Code:
Sub Gemeinde_Auswertung()
Dim AC As Range, gZahl As Long, lz As Long
Sheets("Tabelle1").Select
'LastZell in Spalte "A" ermitteln
lz = Cells(Rows.Count, "A").End(xlUp).Row
'Schleife für alle Gemeinden prüfen
For Each AC In Range("A2:A" & lz)
'Gemeindzahl laden
gZahl = AC.Offset(0, 1).Value
'Auswertung nach Einwohner
If gZahl = 0 Then
AC.Offset(0, 2) = 5
ElseIf gZahl < 20000 Then
AC.Offset(0, 2) = 1
ElseIf gZahl >= 20000 And gZahl < 100000 Then
AC.Offset(0, 2) = 2
ElseIf gZahl >= 100000 And gZahl < 500000 Then
AC.Offset(0, 2) = 3
ElseIf gZahl >= 500000 Then
AC.Offset(0, 2) = 4
End If
Next AC
End Sub
ich habe eine weitere Bedingung zu lösen:
wie könnte eine Prüfung mit Z.B Wenn() aussehen, die als Bedingung hat Wenn im Text der Gemeinde Lkr oder Gemeindefreie Gebiete
steht soll immer die
Größenklasse 5 genommen werden
Und noch möglichst automatisch nachfolgend Nullen auffüllen, bis 8 Stellen bei dem Gemeindeschlüssel gefüllt sind.
Es können zwei bis 5 Zahlen belegt sein, aber das Zielsystem braucht immer 8 Stellen.
Dier 8-stellige Darstellung des Schlüssels erreichst Du mit "Zellen formatieren" über den kleine Pfeil bei "Zahl", "Benutzerdefiniert" und dem Wert 00000000 als Format für die Spalte.
@danke für die Formel,
die Anordnung ist bei mir im Sheet wie folgt:
Spalte A B C D (formel) E F
Gebiet Gemeinde Insgesamt Größenklasse Einwohner BIS Nr der Klasse
09000000 Bayern 12538696 5 00000000 5
09100000 Oberbayern 4382325 5 19999 1
09161000 Ingolstadt (Krfr.St) 125088 3 99999 2
09162000 München, Landeshauptstadt 1353186 4 500000 3
09163000 Rosenheim (Krfr.St) 61299 2 1500000 4
09171000 Altötting (Lkr) 107711 11444 2
09173444 Gemeindefreie Gebiete - 5 enthält die Formel =VERGLEICH(C8;$E$2:$E$7)
... und weitere
mit geänderte Zelle in Formel kommt #NW nur raus bei Zeile mit (Lkr)
=WENN(ODER(TEIL(B1;1;21)="Gemeindefreie Gebiete";WENNFEHLER(FINDEN("Lkr"; B1);FALSCH));5;VERGLEICH(C1;$E$2:$E$7))
