Umwandlung / Umwandlung der Summe (A1: D1) in A1 + B1 + C1 + D1 in Excel

653
streamline

Die Situation ist also so, ich möchte wissen, ob es eine Möglichkeit gibt, die Summe eines Zellbereichs in die Summe umzuwandeln, die sich aus dem Hinzufügen jeder Zelle ergibt, wie im Titel gezeigt. Zum Beispiel, sagt man diese Berechnung in Zelle E1, E1 = SUM(A1:D1)und will ihn in Summe der einzelnen Komponentenzellen aufzuspalten, wie in E1 = A1 + B1 + C1 + D1 .

Wenn es großartig wäre, wenn jemand angeben könnte, wie dies für einen Zellbereich zu tun ist, wie in dem Fall: E1 = SUM (A1:D2)in E1 = A1 + B1 + C1 + D1 + A2 + B2 + C2 + D2 umgewandelt werden.

EDIT: Jemand schlug vor, VBA zu verwenden, und darüber habe ich auch nachgedacht. Wenn jemand Anregungen zu diesem Thema hat, wäre ich sehr dankbar (ich bin nicht sehr gut darin, in VBA zu programmieren, obwohl ich die Grundlagen kenne und ich es selbst ausprobieren werde.

2
Ich bin verwirrt, warum Sie das tun müssen ... Aurus Huang vor 7 Jahren 3
Kurze Antwort: Nein, Sie können es nicht tun, wenn Sie es manuell eingeben. Ich stimme dem vorherigen Kommentar zu: Warum brauchen Sie ihn? Das Ergebnis wird genau das gleiche sein. Máté Juhász vor 7 Jahren 0
Hallo, @AurusHuang & Mate, es gibt zwei Hauptgründe, erstens den Schülern zu zeigen, wie mit Formeln gearbeitet wird (etwas kompliziertes Eingeben in Details), und zweitens an ein Szenario denken, in dem Sie die Spalten umstellen müssen, und Sie haben eine Spalte mit Summen anderer (vorheriger) Spalten. streamline vor 7 Jahren 0
Vielen Dank, @Louis, für die Änderungen. Wenn die Frage beantwortet oder nicht ausreichend ist, schließen Sie sie bitte. streamline vor 7 Jahren 0
Ich denke, das geht mit VBA. Es wird ein bisschen schwierig und kompliziert sein. Sie haben jedoch Zugriff auf alle Elemente des Bereichs, Sie können über die Elemente iterieren und Sie können die einzelnen Elemente des Bereichs verwerfen. IQV vor 7 Jahren 0
Hallo @IQV, das waren genau meine Gedanken. Trotzdem habe ich mich gefragt, ob es in Excel Befehle oder Funktionen gibt, von denen ich nichts wusste. Ich kenne auch ein wenig Makro-Programmierung, aber ich fürchte, ich werde nicht den saubersten Code produzieren. Hast du irgendwelche Vorschläge? Danke für deinen Beitrag! streamline vor 7 Jahren 0
Ich habe lange Zeit VBA-Makros geschrieben. Aber die `Range`-Funktion, einfache` for`-Loops und die Funktionen `Cells` und` Offset` sollten dies für Ihr Problem tun. IQV vor 7 Jahren 1
Warum ist es wichtig, wenn Sie sauberen Code produzieren? Sie schreiben ein Makro, kein Programm! Zweitens können Sie Range ("A1") verwenden. Formula, um die Formel zu erhalten. Von hier aus führen Sie die Logik aus und ersetzen den Zelleninhalt Dave vor 7 Jahren 0
Ein einfaches Programm in einer beliebigen Programmiersprache könnte das tun. Sie könnten jemanden einstellen, der ein Webinterface schreibt, es kann Sie aber nicht viel kosten, so wie es jemand für 10 Dollar tun könnte barlop vor 7 Jahren 0
@ barlop, es ging nicht darum, jemanden dafür zu bezahlen, sondern um zu lernen, wie es geht. Ich habe viele Studenten, die Informatik studieren und das kostenlos machen können. das ist nicht das problem ... streamline vor 7 Jahren 0
@streamline Nun, Sie haben ein bisschen Glück, eine Antwort bekommen zu haben, die Ihnen eine programmierte Lösung gab. 'cos, die Website für das Programmieren ist stackoverflow, aber sie würden sagen, dass sie keine Hausaufgaben stellen, bei denen jemand sagt, dass sie wollen Ein Programm wurde geschrieben .. Sie haben vielleicht sogar gesagt, dass hier vielleicht nicht programmiert werden muss. Wenn Sie lernen wollen, dann ist der erwartete Wawy, ah zu sehen, ok, es erfordert VBA und dann und Machen Sie etwas einfacheres, wie ein Makro zu erstellen. Wenn Sie = A in eine Zelle eingeben, wird = Z angezeigt, und wenn Sie nicht weiterkommen, fragen Sie .. Und bauen Sie das auf, was Sie möchten. barlop vor 7 Jahren 0
@Barlop, ich freue mich über jede Art von Antworten, selbst wenn es konstruktiv ist. Wie gesagt, ich wollte meinen Schülern zeigen, dass sie über minimale Excel-Kenntnisse verfügen, um die Varianz für mehrere Stichproben zu berechnen, und dies berechtigt zum Umordnen von Spalten. Was das 'Geschenk' angeht, bat ich um Rat. Glück zu haben, ist eine Frage der Interpretation. Es liegt an den Moderatoren, eine unangemessene / unangemessene Frage zu schließen / zu blockieren, und die erfahrenen Benutzer müssen diese ggf. melden / ändern. Ich bin überzeugt, dass Daves Antwort zweifellos vielen Menschen helfen wird! streamline vor 7 Jahren 0
Konvertiert ist ein starkes Wort. SUMME (A1: D2) ist das gleiche wie SUMME (A1, A2, B2, ... D2) ist das gleiche wie = A1 + A2 .... + D2 ist das gleiche wie SUMME (A1: A2, B1: B2) , ..., D1: D2). Ich glaube, VBA ist nicht einmal ein Faktor, es sei denn, Sie möchten aus Programmiergründen programmieren. Für Spaß machen Programmierer weltliche Dinge wie diese, aber es ist, was Programmierer tun. ejbytes vor 7 Jahren 0

2 Antworten auf die Frage

2
Dave

Obwohl Sie dies mit einer Arbeitsblattfunktion versehen haben, sprechen Sie über die Verwendung von VBa in der Frage. Diese VBA führt beide Beispiele aus, die Sie angegeben haben

Option Explicit Sub EeekPirates()  Dim formula As String formula = Range("B4").formula  Dim split1() As String split1 = Split(formula, "(")  Dim temp As String temp = Replace(split1(1), ")", "")  Dim splitty() As String splitty = Split(temp, ":")  Dim firstCol As Integer firstCol = AscW(Left(splitty(0), 1))  Dim secondCol As Integer secondCol = AscW(Left(splitty(1), 1))  Dim firstRow As Integer firstRow = Right(splitty(0), 1)  Dim secondRow As Integer secondRow = Right(splitty(1), 1)  Range("B5").Value = "" ' this could be updated to `B4 = `  Dim i As Integer Dim j As Integer  For j = firstRow To secondRow For i = firstCol To secondCol Range("B5").Value = Range("B5").Value & Chr(i) & j & "+" Next i Next j  Dim length As Integer length = Len(Range("B5").Value) - 1 Range("B5").Value = Left(Range("B5").Value, length)  End Sub

Denken Sie daran, dass es kein Undo gibt, also nehmen Sie zuerst ein Backup auf.

Wie füge ich VBA in MS Office hinzu?

Beispiel mit A1: D1

Beispiel mit A1: D2

Laut den Kommentaren im Code, wenn Sie von aktualisieren

Range("B5").Value = ""

zu

Range("B5").Value = "B4 = "

Sie werden am Ende mit (in B5)

B4 = A1 + B1 + C1 + D1
Hallo Dave! Vielen Dank! Nur aus reiner Neugier, warum gibt es dafür kein Undo? Es ist nicht möglich, oder ist es nur für diese Lösung der Fall? Nur wenn Sie Zeit haben, es zu beantworten, ansonsten vielen Dank nochmal. Tut mir leid, dass ich meine Frage nicht in einem VBA-relevanten Abschnitt veröffentlicht habe. Ich habe erst nach meiner Bearbeitung darüber nachgedacht (nach dem Kommentar von IQV). streamline vor 7 Jahren 0
Nochmals vielen Dank, @Dave. Ich war nur neugierig. Ich kann das Arbeitsblatt immer kopieren, um eine Sicherungskopie zu erstellen, aber ich wusste nicht, dass dies mit VBA-Makros zusammenhängt. streamline vor 7 Jahren 0
2
g.kov

Minimal Arbeitsbeispiel mit VBA - Funktion unroll(), die eine Referenz auf eine Zelle, die mit einer einzigen Funktion verwendet (wie sum, count, min) und entrollt ihr Argument (eine Liste der Bereiche) als eine Liste von einzelnen Zellen.

Option Explicit  Function rangeText(s As String) As String Dim i As Integer, j As Integer i = Excel.WorksheetFunction.Find("(", s) j = Excel.WorksheetFunction.Find(")", s) rangeText = Mid(s, i + 1, j - i - 1) End Function  Function rangeToList(s As String) Dim rg As Range: Set rg = Range(s) Dim i, j As Integer: Dim c As String For j = 0 To rg.Rows.Count - 1 For i = 0 To rg.Columns.Count - 1 c = c + IIf(c <> "", ",", "") + Chr(64 + rg.Column() + i) + Format(rg.Row() + j) Next i Next j rangeToList = c End Function  Function unroll(x As Range) As String Dim s As String: Dim i, j As Integer: Dim list() As String If Not x.HasFormula Then s = "Not a formula" Else s = rangeText(x.Formula) list = Split(s, ",") s = "" For i = 0 To UBound(list) s = s + IIf(i > 0, ",", "") + rangeToList(list(i)) Next i End If unroll = s End Function

Hinweis: Als minimales Beispiel werden Spaltenverweise mit zwei Buchstaben nicht korrekt verarbeitet.

* Bearbeiten *

Hinzugefügt Function ColumnNoToName, um Zellverweise mit Spalten> 26 zu behandeln.

Option Explicit  Function rangeText(s As String) As String Dim i As Integer, j As Integer i = Excel.WorksheetFunction.Find("(", s) j = Excel.WorksheetFunction.Find(")", s) rangeText = Mid(s, i + 1, j - i - 1) End Function  Function ColumnNoToName(colNo As Integer) As String Dim lo, hi As Integer: Dim s As String lo = (colNo - 1) Mod 26 If colNo > 26 Then hi = (colNo - 1 - lo) \ 26 s = Chr(64 + hi) End If s = s + Chr(64 + lo + 1) ColumnNoToName = s End Function  Function rangeToList(s As String) Dim rg As Range: Set rg = Range(s) Dim i, j As Integer: Dim c As String For j = 0 To rg.Rows.Count - 1 For i = 0 To rg.Columns.Count - 1 c = c + IIf(c <> "", ",", "") _ + ColumnNoToName(rg.Column() + i) _ + Format(rg.Row() + j) Next i Next j rangeToList = c End Function  Function unroll(x As Range) As String Dim s As String: Dim i, j As Integer: Dim list() As String If Not x.HasFormula Then s = "Not a formula" Else s = rangeText(x.Formula) list = Split(s, ",") s = "" For i = 0 To UBound(list) s = s + IIf(i > 0, ",", "") + rangeToList(list(i)) Next i End If unroll = s End Function  Function cellFormula(x As Range) As String cellFormula = x.Formula End Function
Was macht `:`? Erlaubt dies nur mehrere Deklarationen in einer Zeile? Dave vor 7 Jahren 0
@Dave: Siehe beispielsweise [Verwenden von Doppelpunkten, um zwei Anweisungen in derselben Zeile in Visual Basic zu platzieren] (http://stackoverflow.com/a/1413425/3219646). g.kov vor 7 Jahren 0
Wow, @ g.kov (es bedeutet wahrscheinlich nicht viel, von einem Anfänger wie mir, aber wow), danke! Das ist auch toll! Vielen Dank für das Teilen und die Zeit, es zu posten. streamline vor 7 Jahren 0
Hi, @Dave, ich habe beide Antworten bestätigt, deins und g.kov. Wie sonst! Aber da ich neu bei Superuser bin, muss ich eine gewisse Mindestschwelle für die Reputation erreichen (15 denke ich), damit meine Stimme gezeigt werden kann. Das tut mir leid. Ich weiß jedoch nicht, was Sie unter Antworten verstehen. streamline vor 7 Jahren 0

Verwandte Probleme