Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Excel evtl. VB; Zeichen X zählen und Summe in eine extra Tabelle eintragen

Frage Entwicklung

Mitglied: lrt088

lrt088 (Level 1) - Jetzt verbinden

29.01.2007, aktualisiert 01.02.2007, 13935 Aufrufe, 9 Kommentare

Mehrere Excel Dateien, die jeweils ein oder mehrere X-Zeichen enthalten kann, auf 72 Zellen verteilt.

Hallo,

Datei1 hat eine Tabelle mit 72 Zellen
Datei2 hat eine Tabelle mit 72 Zellen
...

Es soll gezählt und aufgelistet werden, wie viele X bei allen Dateien in der Zelle 1, Zelle 2, ... enthalten sind. Ergebnis in einer neuen Tabelle ausgeben.
neue Tabelle:
Zelle 1 = 12
Zelle 2 = 44
...

Ich bin da noch recht unerfahren. Wäre froh, wenn man mir helfen könnte. Tipps, Anregungen...
Freundlich grüßt
lrt0088
Mitglied: Biber
29.01.2007 um 16:55 Uhr
Moin lrt088,

willkommen im Forum.

Vermutlich hat so lange keine/r auf Deine Frage geantwortet, weil noch eine wesentliche Detailangabe fehlt.
[Oder weil noch alle Lachtränen in den Augen haben wegen der ersten M$-Vi$ta-Feedbacks...]

JHowever, was mir fehlt, um eine sinnvolle Strategie vorzuschlagen, ist die Info, ob es denn um namentlich bekannte (handverlesene) Dateien Datei1.xls, Datei2.xls etc handelt

-oder-
ob es denn täglich/stündlich wechselnde Dateien mit "zufälligen" Dateinamen z.B. aus einem Log-Verzeichnis handelt.

Im ersten Fall (jede Datei ist einzeln anzugeben und angegeben), dann würde ich eine einfache neue Summentabelle aufmachen, in der von Hand jeweils die Formel eingetragen wird:
=ZÄHLENWENN([Datei1.xls]!Mappe1.A:Datei1.xls]!Mappe1.A;"*X*")
-bzw- für Sparsame
=ZÄHLENWENN([Datei1.xls]!Mappe1.A1:Datei1.xls]!Mappe1.A72;"*X*")

...diese Formel in die erste Zeile (da sollte dann in Deinem Beispiel 12 herauskommen) usw.

Im zweiten Fall (namentlich nicht bekannte Dateien) wäre der Weg über ein kleines Makro sinnvoller... geleiches Schema, nur dass das ZÄHLENWENN() auf neudeutsch COUNTIF() heißt.

Gruß
Biber
[Edit]...nach bastla's Kommentar..
Sorry, ich hatte es so interpretiert, dass pro Zeile in Spalte A ein oder eben kein Zeichen "x" vorhanden ist...
Die ZÄHLENWENN()-Funktion mit obiger Syntax zählt entsprechend nur die Anzahl Zeilen, die ein "X" enthalten.
[/Edit]
Bitte warten ..
Mitglied: bastla
29.01.2007 um 17:01 Uhr
Hallo lrt0088 und willkommen im Forum!

Damit ich Dich richtig verstehe: In den Zellen A1:A72 jeder Datei befindet sich eine jeweils unterschiedliche Anzahl von "X" (aber keine anderen Zeichen). In einer anderen Datei soll eine Tabelle für jede der Dateien in einer Spalte (also A1:A72 für Datei1, B1:B72 für Datei2) die Anzahl der "X" angeben - richtig?

Sofern diese Annahmen zutreffen, könntest Du folgende Formel verwenden (für alle Zeilen kopieren; analog für "Datei2"):
01.
=LÄNGE([Datei1.xls]Tabelle1!A1)
HTH
bastla

[Edit] @Biber - gehöre wohl auch schon zu den "älteren Leuten" ... [/Edit]
Bitte warten ..
Mitglied: lrt088
30.01.2007 um 08:57 Uhr
Hallo biber,
hallo bastla,

vielen Dank für das schnelle Antworten. Die X Zeichen werden von Hand eingetragen, Bewertungsbogen. (1. Hat dir die Freizeit gefallen? sehr gut/gut/befriedigend/... // 2. dfghj? sehr gut/gut/befriedigend/...)

Der erste Teil:
Es gibt insgesamt 72 Zellen, die in jeder Datei die gleiche Position haben und entweder leer sind oder ein X enthalten. (die Fragen stehen in anderen Zelle)

Die Ergebnistabelle:
In einer neuen Datei soll eine Tabelle erstellt werden, die mir sagen kann, wie viele X Zeichen in der Zelle A1 in allen Dateien stehen. Wie viele X Zeichen stehen in der Zelle B1 in allen Dateien? D.h. in der neuen Tabelle soll die Summe angezeigt werden. z.B. zur ersten Frage haben 15 Personen bei "sehr gut" ein X eingetragen.

Hoffentlich habe ich es jetzt besser erklärt

Grüße
lrt088
Bitte warten ..
Mitglied: bastla
30.01.2007 um 17:05 Uhr
Hallo lrt088!

Vielleicht vorweg eine Vorgangsweise, die ich in dieser Situation wählen würde (obwohl ich versucht hätte, gar nicht erst in diese Situation zu kommen ):

Als ersten und wichtigsten Schritt würde ich alle Bewertungen in einem Blatt zusammenzufassen, wobei ich aus den Spalten Zeilen (Datensätze) erzeugen und jedes "X" durch die Zahl 1 ersetzen würde.
Durch das Zusammenfassen vereinfacht sich eine ev weitere Auswertung (zB: sind genug Fragen beantwortet, um diesen "Fragebogen" überhaupt als valide anzuerkennen, oder sollte er besser aussortiert werden; gibt es Zusammenhänge zwischen den Antworten auf Frage 2 und 7; ...), und ich könnte auch eine große Menge an "Fragebögen" erfassen (bis Office 2003 hat Excel ja nur 256 Spalten pro Tabellenblatt). Außerdem könnte dann auch ein Export in eine Datenbank Deiner Wahl erfolgen.

Das Umwandeln von "X" in 1 ermöglicht das Berechnen einfacher Anzahlen (wie von Dir ursprünglich gewünscht) sogar per "SUMME" (ansonsten gäbe es auch "SUMMENPRODUKT" bzw "Array"-Formeln).

Ein entsprechendes VBA-Programm (Makro) könnte so aussehen:
01.
Option Explicit 
02.
 
03.
Sub Zusammenfassen() 
04.
Const sSourcePath As String = "Z:\Fragebogendateien" '######## anpassen ######## 
05.
Dim wbGes As Workbook, wbTeil As Workbook 
06.
Dim fso As Object, oFile As Object 
07.
Dim rNext As Integer 
08.
 
09.
Set wbGes = ActiveWorkbook 
10.
Set fso = CreateObject("Scripting.FileSystemObject") 
11.
 
12.
For Each oFile In fso.GetFolder(sSourcePath).Files 
13.
    If LCase(Right(oFile.Name, 4)) = ".xls" Then 
14.
        Application.Workbooks.Open (oFile.Path) 
15.
        Set wbTeil = ActiveWorkbook 
16.
        wbTeil.Worksheets(1).Activate 
17.
        Range("A1:A72").Copy '##################### anpassen #################### 
18.
        wbGes.Worksheets(1).Activate 
19.
        rNext = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 
20.
        ActiveSheet.Cells(rNext, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
21.
        :=False, Transpose:=True 
22.
        Application.CutCopyMode = False 
23.
    End If 
24.
    wbTeil.Close 
25.
Next 
26.
 
27.
wbGes.Worksheets(1).Activate 
28.
Cells.Replace What:="X", Replacement:="1", LookAt:=xlPart, _ 
29.
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
30.
        ReplaceFormat:=False 
31.
Cells.Replace What:="x", Replacement:="1", LookAt:=xlPart, _ 
32.
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
33.
        ReplaceFormat:=False 
34.
wbGes.Save 
35.
MsgBox "Fertig." 
36.
End Sub
Vorgangsweise:

Sammle alle Einzeldateien in einem gemeinsamen Ordner (im Beispiel oben "Z:\Fragebogendateien").

Erstelle eine neue Excel-Datei "Zusammenfassung.xls" und speichere diese nicht im gleichen Ordner wie die Einzeldateien.

Gehe mit Alt-F11 in den VBA-Editor, doppelklicke im Projekt-Explorer (links oben) auf "Diese Arbeitsmappe" und füge den oben stehenden Code ein.

Passe die beiden markierten Positionen an Deine Gegebenheiten an.

Setze den Cursor auf die Zeile "Const ..." und drücke die blaue "Play"-Taste in der Symbolleiste (oder die Taste F5).

Nachdem alle Dateien bearbeitet wurden, erhältst Du den Hinweis "Fertig."

Danach findest Du in der Zusammenfassungsdatei Deine einzelnen Datenspalten als Zeilen wieder (Zeile 1 sollte frei sein - dort kannst Du eine lfd Nummer von 1 - 72 unterbringen; vielleicht fügst Du nach dieser "Überschrift" noch eine Leerzeile ein (damit beim n#chsten Schritt nicht irrtümlich die Nummer mitgerechnet wird).

Jetzt kannst Du durch Summierung die Anzahl der ursprünglichen "X" in den einzelnen Spalten bestimmen.

Falls Dir diese Vorgangsweise nicht zusagt, bitte um Rückmeldung.

Grüße
bastla
Bitte warten ..
Mitglied: lrt088
31.01.2007 um 13:54 Uhr
Hallo bastla,

ich bin noch mit dem Verstehen Deiner Antwort beschäftigt . Jedenfalls sieht die Struktur des Fragebogens so aus:

Zelle A10 = Frage 1
Zelle A11 = Sehr gut
Zelle B11 = X oder leer
Zelle C11 = Gut
Zelle D11 = X oder leer
Zelle E11 = Befriedigend
Zelle F11 = X oder leer
usw.

Zelle A13 = Frage 2
Zelle A14 = Sehr gut
Zelle B14 = X oder leer
Zelle C14 = Gut
Zelle D14 = X oder leer
Zelle E14 = Befriedigend
Zelle F14 = X oder leer
usw.

- die Summe, wieviele z.B. auf die Frage 1 ein X bei "gut" gesetzt haben, ist das Hauptziel

- wieviele Fragen und welche Fragen unbeantwortet blieben wäre schön, wenn dies in der Ergebnis-Datei auch mit angezeigt werden könnte

- das Ergebnis braucht in keine Datenbank

- Zusammenhänge zwischen den Antworten der Fragen braucht nicht berücksichtigt werden

Grüße
lrt088
Bitte warten ..
Mitglied: bastla
31.01.2007 um 14:29 Uhr
Hallo lrt088!

Da hatte ich Dich falsch interpretiert, da ich angenommen hatte, alle "Antwort"-Zellen stünden untereinander und würden nur ein "X" (oder vielleicht auch "x") für "ja" enthalten oder bei einem "nein" leer geblieben sein ...

Außerdem war ich von nur einem "Fragebogen" je Datei ausgegangen (so als hätte jede/r Befragte eine eigene Datei erhalten und diese "ausgefüllt" retourniert).
Jetzt sieht es für mich so aus, dass also je Datei die Zellen B11, B14, B17, ... , B224 und analog die Spalten D, F, H, J, L die Ergebnisse enthalten - richtig? Ist sicher gestellt, dass diese Zellen nur entweder leer oder mit "X" gefüllt sind, oder soll angenommen werden, dass jede nicht leere Zelle als "angekreuzt "gilt?

Falls ich das richtig erfasst habe, als neuer Vorschlag:
Ergebnisdatei mit je einer Zeile pro Antwortdatei, in Spalte A (als Rückbezug) der Dateiname, ab Spalte B 72 Zellen mit den Antworten (auf 72 Fragen) in numerischer Form, also 0 für "nicht beantwortet", 1 für "Sehr gut", ... Bei Mehrfachantworten wäre dann der Inhalt zB 34 (diese Zellen ließen sich danach per "Bedingter Formatierung" sehr leicht farblich kennzeichnen - die Zellen mit Inhalt 0 natürlich ebenso).

Die Auswertung könnte danach mit "ZÄHLENWENN" erfolgen, um spaltenweise die Häufigkeiten feststellen zu können.

Soll ich auf dieser Basis einen Versuch machen?

Grüße
bastla

[Edit] Zelladressen B11, B14, ... korrigiert [/Edit]
Bitte warten ..
Mitglied: lrt088
31.01.2007 um 16:17 Uhr
Hallo bastla,

sorry, ich hätte das gleich genau sagen müssen.

Zitat:
"Außerdem war ich von nur einem "Fragebogen" je Datei ausgegangen (so als hätte jede/r Befragte eine eigene Datei erhalten und diese "ausgefüllt" retourniert)."

- das ist richtig

Zitat:
"Jetzt sieht es für mich so aus, dass also je Datei die Zellen B11, B14, B17, ... , B224 und analog die Spalten D, F, H, J, L die Ergebnisse enthalten - richtig?"

- ja, pro Frage gibt es 6 Antworten zur Auswahl. Nur eine darf man wählen.

Zitat:
"Ist sicher gestellt, dass diese Zellen nur entweder leer oder mit "X" gefüllt sind, oder soll angenommen werden, dass jede nicht leere Zelle als "angekreuzt "gilt?"

- Falsches auswählen ist möglich. wenn man die falsch ausgefüllten Fragen markieren kann, wäre das natürlich noch einen Schritt edler. Bevor es dir zuviel wird, lass es weg.

Zuerst waren es 12 Fragen, jetzt sind es 20 Fragen. Mehr wird es nicht. Pro Fragen 6 Antwortmöglichkeiten = 120 Zellen, die entweder X oder leer sind.

Ich bin wirkliche gespannt, wie so etwas in VBA oder Excel Formel aussieht.

Grüße
lrt088
Bitte warten ..
Mitglied: bastla
31.01.2007 um 16:33 Uhr
Hallo lrt088!

Ich hatte schon auf Verdacht ein wenig mit folgendem Ergebnis experimentiert:
01.
Option Explicit 
02.
 
03.
Sub Zusammenfassen() 
04.
Const sSourcePath As String = "Z:\Fragebogendateien" '######## anpassen ######## 
05.
Const intErsteZeile = 3 'der Tabelle mit der Zusammenfassung 
06.
Const intAnzahlFragen = 20 
07.
Const intAnzahlAuspr = 6 
08.
 
09.
Dim wbGes As Workbook, wbTeil As Workbook 
10.
Dim fso As Object, oFile As Object 
11.
Dim intZeile As Integer, i As Integer, j As Integer 
12.
Dim strVon As String, strBis As String 
13.
Dim astrAntwort(intAnzahlFragen) As String 
14.
 
15.
Set wbGes = ActiveWorkbook 
16.
Set fso = CreateObject("Scripting.FileSystemObject") 
17.
 
18.
For i = 1 To intAnzahlFragen 
19.
    With wbGes.Worksheets(1).Cells(intErsteZeile, i + 1) 
20.
        .FormulaR1C1 = "Frage" & Chr(10) & CStr(i) 
21.
        .HorizontalAlignment = xlCenter 
22.
        .VerticalAlignment = xlTop 
23.
        .ColumnWidth = 5 
24.
    End With 
25.
Next 
26.
 
27.
intZeile = intErsteZeile + 2 
28.
For Each oFile In fso.GetFolder(sSourcePath).Files 
29.
    If LCase(Right(oFile.Name, 4)) = ".xls" Then 
30.
        Application.Workbooks.Open (oFile.Path) 
31.
        Set wbTeil = ActiveWorkbook 
32.
        For i = 1 To intAnzahlFragen 
33.
            astrAntwort(i) = "" 
34.
            For j = 1 To intAnzahlAuspr 
35.
            If wbTeil.Worksheets(1).Cells(i * 3 + 8, j * 2).Value <> "" Then 
36.
                astrAntwort(i) = astrAntwort(i) & CStr(j) 
37.
            End If 
38.
            Next 
39.
            If astrAntwort(i) = "" Then astrAntwort(i) = "0" 
40.
        Next 
41.
        wbGes.Worksheets(1).Cells(intZeile, 1).Value = oFile.Name 
42.
        For i = 1 To intAnzahlFragen 
43.
            wbGes.Worksheets(1).Cells(intZeile, i + 1).Value = astrAntwort(i) 
44.
        Next 
45.
    End If 
46.
    wbTeil.Close 
47.
    intZeile = intZeile + 1 
48.
Next 
49.
 
50.
wbGes.Worksheets(1).Activate 
51.
With Range(Cells(intErsteZeile + 2, 2), Cells(intZeile - 1, intAnzahlFragen + 1)) 
52.
    .FormatConditions.Delete 
53.
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="0" 
54.
    .FormatConditions(1).Interior.ColorIndex = 19 
55.
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="6" 
56.
    .FormatConditions(2).Interior.ColorIndex = 34 
57.
End With 
58.
 
59.
strVon = "R" & CStr(intErsteZeile + 2) & "C" 
60.
strBis = "R" & CStr(intZeile - 1) & "C" 
61.
 
62.
For j = 0 To intAnzahlAuspr + 1 
63.
    With wbGes.Worksheets(1).Cells(intZeile + 1 + j, 1) 
64.
        .Value = j 
65.
        .HorizontalAlignment = xlLeft 
66.
    End With 
67.
    For i = 1 To intAnzahlFragen 
68.
        If j <= intAnzahlAuspr Then 
69.
            wbGes.Worksheets(1).Cells(intZeile + 1 + j, i + 1).FormulaR1C1 = _ 
70.
                "=COUNTIF(" & strVon & ":" & strBis & ",RC1)" 
71.
        Else 
72.
            wbGes.Worksheets(1).Cells(intZeile + 1 + j, i + 1).FormulaR1C1 = _ 
73.
                "=COUNTIF(" & strVon & ":" & strBis & ","">=""&RC1)" 
74.
            wbGes.Worksheets(1).Cells(intZeile + 1 + j + 2, i + 1).FormulaR1C1 = _ 
75.
                "=SUM(R" & CStr(intZeile + 1) & "C:R[-2]C)" 
76.
        End If 
77.
    Next 
78.
Next 
79.
 
80.
wbGes.Save 
81.
MsgBox "Fertig." 
82.
End Sub
In dieser Fassung werden alle Bewertungszellen, die nicht leer sind, gezählt (auch wenn Dir jemand ein "U" für ein "X" vorgemacht haben sollte ).

Die jeweiligen Anzahlen (von "0" für nicht beantwortet bis "7" für Mehrfachantwort) werden eingetragen und zur Kontrolle werden alle Anzahlen addiert - diese Summe müsste in jeder Spalte mit der Zahl der Fragebögen übereinstimmen.

Die Beschreibung der Vorgangsweise (siehe oben) zur Verwendung des Programms (bis "Fertig.") gilt weiterhin.

Grüße
bastla
Bitte warten ..
Mitglied: lrt088
01.02.2007 um 13:10 Uhr
Hallo bastla,

ich bin noch dabei das auszuprobieren. Das dauert noch

Vielen Dank!!

Grüße
lrt088
Bitte warten ..
Neuester Wissensbeitrag
CPU, RAM, Mainboards

Angetestet: PC Engines APU 3a2 im Rack-Gehäuse

(1)

Erfahrungsbericht von ashnod zum Thema CPU, RAM, Mainboards ...

Ähnliche Inhalte
Microsoft Office
gelöst Excel Formeln - Zählen der Anzahl eindeutiger Zellwerte mit einer Nebenbedingung (4)

Frage von Booster07 zum Thema Microsoft Office ...

Microsoft Office
Excel 2007 jedes Fenster in extra Instanz (7)

Frage von xbast1x zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
Switche und Hubs
Trunk für 2xCisco Switch. Wo liegt der Fehler? (13)

Frage von JayyyH zum Thema Switche und Hubs ...

DSL, VDSL
DSL-Signal bewerten (13)

Frage von SarekHL zum Thema DSL, VDSL ...

Backup
Clients als Server missbrauchen? (9)

Frage von 1410640014 zum Thema Backup ...

Windows Server
Mailserver auf Windows Server 2012 (9)

Frage von StefanT81 zum Thema Windows Server ...