Excel Formel

Excel Formel (39 Beiträge, 520 Mal gelesen)

Du bist nicht angemeldet. [ Login/Registrieren ]

Folgende Frage:

Ich habe zwei Spalten

A                 B
1                 frei
2                 Markus Maier
3                 Huber Clemens
4                 frei
5                 frei
6                 Max Mustermann

Ich will jetzt folgendes Resultat:

Wenn in der Spalte b "frei" steht dann möchte ich die zugehörige Nummer aus Spalte a irgendwo angedruckt haben!

GEHT DAS? Ich will wenn es geht keine PIVOT Tabelle haben....

DANKE FÜR EURE HILFE!

Hilf auch Du! http://forum.geizhals.at/t261360.html

hi,

ich sag mal programmiertechnisch müsste es funken, nur ich glaub in excel machst das mit visual basic und da kenn ich zu wenig aus. aber ich sag mal es sollte mit einer einfachen if abfrage d.h. wenn in der zelle B frei steht dann soll einfach der inhalt der Zelle A irgendwo gespeichert bzw. hingeschrieben werden.

mal schauen ob ein paar spezialisten hier sind wie es in vb genau funkt.

=WENN(B1="frei";A1;WENN(B1="Markus Maier";A2))

usw.

meinst du das?

ja genau so in der richtung, versuch das mal sputum ob das so funktioniert?

das ist ja schon ganz gut, aber:

Ich habe 6000 Zeilen, wenn ich diese Methode verwende muss ich das 6000 mal kopieren....

Hilf auch Du! http://forum.geizhals.at/t261360.html

also ich weiss ja nicht wie du das machst, aber:
Schreibe die Formel in die erste Zelle
gehe in die erste zelle in der was steht in der Spalte in der durchgehend Werte stehen.
druecke gleichzeitig: "shift"+"ende"+pfeil nach oben --> voila, der ganze bereich ist markiert, dann drueckst du gleichzeitig: "strg"+"u" und die Formel wird kopiert
(Bitte das ganze natuerlich ohne die ganzen "")

mfg

Penguin

Das Ergebnis ist das gleiche: Ich habe statt 600 Zeilen 12000 Zeilen.... Ich möchte halt lediglich die Zellen wo "frei" steht und das möglichst untereinander......

Hilf auch Du! http://forum.geizhals.at/t261360.html

Daten markieren (alle)
dann Extras/Filter/Autofilter

in der richtigen Spalte dann "frei" auswaehlen.

Hoffe ich hab dich richtig verstanden

mfg

Penguin

=WENN(B1="frei";A1;"nicht frei")

wenn Du das einmal einträgst und dann mit der Maus runterziehst, kopiert er das und ändert die Formel entsprechend für jede Zeile

MfG

MJFox

ich möchte eine liste der freien, nicht die gleiche Liste mit frei und nicht frei...

Es soll auf einen Blick ersichtlich sein, welche Zellen frei sind, die anderen interessierne mich nicht...

Hilf auch Du! http://forum.geizhals.at/t261360.html

=WENN($B1="frei";A1)
Das in der 1er Zeile hinschreiben und am rechten unteren rand der Zelle bei dem kleinem schwarzen viereck runterziehen. wennst das gemacht hast, steht bei allen die frei sind frei und bei den andern nichts. wennst es sortieren willst: Daten -> Sortieren...

aber überleg mal, da kann ich doch gleich die originale Liste nach "frei" sortieren, das ist doch keine erleichterung sondern lediglich eine 2. Liste....

Hilf auch Du! http://forum.geizhals.at/t261360.html

anders wirst du es mit den exel-funktionen nicht schaffen, oder du machst eine MSQeuery abfrage
_______________________________________________

Mit Computer lösen wir die Probleme die wir ohne sie nicht hätten!!!

Woher soll ich wissen was ich schreibe, bevor ich lese was ich denke?!

Wer Rechtschreibfehler findet darf sie behalten !

ist das leicht zu realisieren für einen dau?

Hilf auch Du! http://forum.geizhals.at/t261360.html

hast du schonmal dran gedacht einfach einen autofilter zu machen und dann nach frei filtern? irgendwie check ich überhaupt net was du willst

Es checkt irgendwie keiner, ich werde es nochmals versuchen!

Ich habe eine ziemlich grosse Liste (ca. 6000 Zeilen)

In dieser Liste werden Nummern Namen zugeordnet.

Jetzt gibt es aber auch Nummern die frei sind, irgendwo mittendrin.

Mit einem Autofilter könnte ich die Liste jetzt natürlich nach frei sortieren, hätte aber dann immer noch mehrere Seiten...

Deswegen will ich jetzt eine kleine Liste wo einfach alle freien nebeneinander angezeigt werden (Freie Zahlen: 1,2,3,4,5,6,7,....)

Ich hoffe das erklärt das Problem ein wenig genauer

Hilf auch Du! http://forum.geizhals.at/t261360.html

Hallo sputum,

probiers doch mal mit ein paar Zeilen vba-code:

Erstelle unter Extras > Makros... mit "Makro aufzeichnen" ein neues,
leeres Makro indem du gleich nach Aufzeichnungsstart auf "Aufzeichnung
beenden" klickst.
Dann gehst du mit Alt+F8 in deine Makrosammlung und klick auf
den Button "Bearbeiten" um das Makro zu ergänzen.

Füge über "End Sub" folgenden 7 Zeilen ein:

' Hinweis: Die Spalte mit den Namen muss markiert werden und die
'    Zahlen-Spalte muss sich direkt links davon befinden.
'    Das Ergebnis mit allen freien Zahlen wird in die Zelle A1 geschrieben.

for each cell in Selection
    if cell.value = "frei" then Cells(1, 1).value = _
    Cells(1, 1).value & Cells(cell.row, cell.column - 1).value & ", "
next cell

Nachdem du die Datei abgespeichert hast kannst du das Makro mit
Alt+F8 und einem Doppelklick in der Makroliste ausführen.

(ich hoffe es funktioniert, hab nämlich grad kein Excel zur Hand)

he super sowas in der art ist ja schon geil....

folgende mods sind ,machbar?

- die Liste soll auf einem anderen Arbeitsblatt sein und nicht markiert werden müssen, dafür kann ich dir sagen dass es die b spalte auf dem arbeitsblatt bediener ist .....

GEHT DAS?

Hilf auch Du! http://forum.geizhals.at/t261360.html

also wenn du schon soweit gehen willst, dann bitte ordentlich

option explicit
public sub getFreeNumbers()
dim rngArea as excel.range,rngCell as excel.range,rngTarget as excel.range
dim wks as worksheet
dim strTarget as string
dim lngRow as long
dim intColumn as integer

set wks=worksheets("bediener")
'ziel auswaehlen
strtarget=getrange()
lngRow=clng(mid(strtarget,1,instr(1,strtarget,",")-1))
intcolumn=cint(mid(strtarget,instr(1,strtarget,",")+1))
set rngtarget=wks.cells(lngrow,intcolumn)

'spalte B
set rngarea=wks.cells(1,2).offset(0,0).range("a1:a65536")
'nur die mit constanten verwenden (nicht leer, keine Formel)
set rngarea=rngarea.SpecialCells(xlCellTypeConstants)

for each rngCell in rngarea
   if rngcell.text="frei" then
     if len(rngtarget.text)<>0 then
         rngtarget.value=rngtarget.text & "," & rngcell.row
     else
         rngtarget.value="Zahlen die frei sind: " & rngcell.row
    end if
  end if
next rngcell

end sub

public function getRange() as string
dim rng as excel.range
dim strText as string

strtext="Bitte eine Zelle im aktiven Arbeitsblatt auswaehlenauswaehlen " & _
"in die die Liste geschrieben werden soll:!"
anfang:
set rng=application.inputbox(strtext, Type:=8)
if rng.rows.count>1 or rng.columns.count>1 then
msgbox "Bitte nur eine einzelne Zelle im aktiven Arbeitsblatt auswaehlen!"
goto anfang:
endif
getRange=rng.row & "," & rng.column
end function

mfg

Penguin

shit ein Fehler:

Diese Zeile ersetzen:

Set rngTarget = wks.Cells(lngRow, intColumn)

mit dieser

Set rngTarget = activesheet.Cells(lngRow, intColumn)

(man sollte nicht aus dem Kopf heraus programmieren...)

mfg

Penguin

oka yhab das jetzt eingefügt, es kommt die erste msg box , ich wähle eine zelle aus drück auf okay und das war es dann, was soll das ding machen?

Hilf auch Du! http://forum.geizhals.at/t261360.html

hast die korrektur beruecksichtigt?

Es schreibt dir in die Zelle die du auswaehlst alle Zeilen die "frei" sind.

mfg

Penguin

hab grad gesehen du willst nicht die Zeile sondern die Nummer aus Spalte A:

ersetze die folgende Zeile:

rngTarget.Value = rngTarget.Text & "," & rngCell.Row

mit folgender:

rngTarget.Value = rngTarget.Text & "," & wks.cells(rngcell.row,1).text

mfg

Penguin

und diese auch:

rngTarget.Value = "Zahlen die frei sind: " & rngcell.Row

mit dieser:

rngTarget.Value = "Zahlen die frei sind: " & wks.Cells(rngCell.Row, 1).Text

mfg

Penguin

SUPER!

Könntest mir das ganze ein bisserl umwandeln:

Er soll es automatisch in die Zelle: A1664 anzeigen und dieses Feld zuvor löschen!

hier der komplette Code:

Sub freieuser()

Dim rngArea As Excel.Range, rngCell As Excel.Range, rngTarget As Excel.Range
Dim wks As Worksheet
Dim strTarget As String
Dim lngRow As Long
Dim intColumn As Integer

Set wks = Worksheets("bediener")
'ziel auswaehlen
strTarget = getRange()
lngRow = CLng(Mid(strTarget, 1, InStr(1, strTarget, ",") - 1))
intColumn = CInt(Mid(strTarget, InStr(1, strTarget, ",") + 1))
Set rngTarget = ActiveSheet.Cells(lngRow, intColumn)

'spalte B
Set rngArea = wks.Cells(1, 2).Offset(0, 0).Range("a1:a65536")
'nur die mit constanten verwenden (nicht leer, keine Formel)
Set rngArea = rngArea.SpecialCells(xlCellTypeConstants)

For Each rngCell In rngArea
   If rngCell.Text = "unbenutzt" Then
     If Len(rngTarget.Text) <> 0 Then
         rngTarget.Value = rngTarget.Text & "," & wks.Cells(rngCell.Row, 1).Text
     Else
         rngTarget.Value = "Unbenutzte Bediener: " & wks.Cells(rngCell.Row, 1).Text
    End If
  End If
Next rngCell

End Sub

Public Function getRange() As String
Dim rng As Excel.Range
Dim strText As String

strText = "Bitte eine Zelle im aktiven Arbeitsblatt auswaehlenauswaehlen " & _
"in die die Liste geschrieben werden soll:!"
anfang:
Set rng = Application.InputBox(strText, Type:=8)
If rng.Rows.Count > 1 Or rng.Columns.Count > 1 Then
MsgBox "Bitte nur eine einzelne Zelle im aktiven Arbeitsblatt auswaehlen!"
GoTo anfang:
End If
getRange = rng.Row & "," & rng.Column
End Function

Hilf auch Du! http://forum.geizhals.at/t261360.html

du aenderst folgende zeile:

Set rngTarget = ActiveSheet.Cells(lngRow, intColumn)

in folgende

Set rngTarget = ActiveSheet.range("a1664")

und fuegst folgende zeile gleich danach ein:

rngtarget.clearcontents

(Die Funktion getRange brauchst du dann eigentlich nicht)

Alternative (du laesst die Wahl offen, es soll nur standarmaessig A1664 sein):

Du ersetzt folgende Zeile:

Set rng = Application.InputBox(strText, Type:=8)

mit folgender

Set rng = Application.InputBox(strText, Default:="A1664", Type:=8)

und fuest trotzdem folgende Zeile:
rngtarget.clearcontents
and selbiger STelle wie zuvor beschrieben ein.

mfg

Penguin

die funktion

rngtarget.clearcontents

löst den debugger aus, sonst geht alles super....

Hilf auch Du! http://forum.geizhals.at/t261360.html

kann an mehreren Dingen liegen:

am wahrscheinlichsten: du hast eine verbundene Zelle angegeben (der Knopf wo du Felder neben/untereinander verbinden kannst).

Die schafft er mit dem clearcontents irgendwie nicht.

ABhilfe ist zb:

rngtarget.value=""

(also nicht loeschen sondern mit nichts ueberschreiben)

Hoffe aber es passt wenigstens der Rest und du bist zufrieden

mfg

Penguin

Du hast das Problem erkannt, ich werde das so probieren!

ICH BIN SUPER ZUFRIEDEN!

und schulde dir was, vielleicht ergibt sich ja mal was....

Hilf auch Du! http://forum.geizhals.at/t261360.html

ach passt schon, sitze derzeit absolut beschaeftigungslos in einer ueberbezahlten hackn herum und man will mir keine arbeit geben

mfg

Penguin

du frage:

kann man die resultate auch in eine msgbox andrucken, ich habs mal wieder net geschafft:

Sub freieuser()

Dim rngArea As Excel.Range, rngCell As Excel.Range, rngTarget As Excel.Range
Dim wks As Worksheet
Dim strTarget As String
Dim lngRow As Long
Dim intColumn As Integer

Set wks = Worksheets("bediener")

Set rngTarget = ActiveSheet.Range("frei")
rngTarget.Value = ""

'spalte B
Set rngArea = wks.Cells(1, 2).Offset(0, 0).Range("a1:a65536")
'nur die mit constanten verwenden (nicht leer, keine Formel)
Set rngArea = rngArea.SpecialCells(xlCellTypeConstants)

For Each rngCell In rngArea
   If rngCell.Text = "unbenutzt" Then
     If Len(rngTarget.Text) <> 0 Then
         rngTarget.Value = rngTarget.Text & ", " & wks.Cells(rngCell.Row, 1).Text
     Else
         rngTarget.Value = "" & wks.Cells(rngCell.Row, 1).Text
    End If
  End If
Next rngCell

End Sub

Hilf auch Du! http://forum.geizhals.at/t261360.html

jeden einzeln, oder alle zusammen?

jeden einzeln:
statt

If Len(rngTarget.Text) <> 0 Then
         rngTarget.Value = rngTarget.Text & ", " & wks.Cells(rngCell.Row, 1).Text
     Else
         rngTarget.Value = "" & wks.Cells(rngCell.Row, 1).Text
    End If

schreibst

If Len(rngTarget.Text) <> 0 Then
   msgbox rngcell.row & " :" & wks.Cells(rngCell.Row, 1).Text
End If

alle zusammen:
statt:
If Len(rngTarget.Text) <> 0 Then
         rngTarget.Value = rngTarget.Text & ", " & wks.Cells(rngCell.Row, 1).Text
     Else
         rngTarget.Value = "" & wks.Cells(rngCell.Row, 1).Text
    End If

schreibst:

If Len(strtarget) <> 0 Then
         strtarget = strtarget ", " & wks.Cells(rngCell.Row, 1).Text
     Else
        strtarget = "" & wks.Cells(rngCell.Row, 1).Text
End If

am ende dann:

msgbox strtarget

hoffe das hilft dir!

mfg

Penguin

ich will alle zahlen in einer msgbox!

bei dieser zeile hängt er sich auf:

strtarget = strtarget "," & wks.Cells(rngCell.Row, 1).Text

syntaxfehler!

Hilf auch Du! http://forum.geizhals.at/t261360.html

ja sorry in der eile vertippt:

so ghoerts:

strtarget = strtarget & "," & wks.Cells(rngCell.Row, 1).Text

das & vor "," hat gefehlt

mfg

Penguin

du bist ein wahnsinn, aber das weisst eh, oder!

Hilf auch Du! http://forum.geizhals.at/t261360.html

na, warum

mfg

Penguin

guck dir das mal an:

http://forum.geizhals.at/files/61744/beispiel.jpg
_______________________________________________

Mit Computer lösen wir die Probleme die wir ohne sie nicht hätten!!!

Woher soll ich wissen was ich schreibe, bevor ich lese was ich denke?!

Wer Rechtschreibfehler findet darf sie behalten !

Du markierst beide Spalten, wählst im Menü: Data->Filter->Autofilter (sorry, hab engisches Excel). Dann setzt du den Filter ("Pfeil nach unten-Button in Spalte B) auf "frei" und hast als Ergebnis die Liste aller freien. Die kannst dann markieren und überall hinpasetn, wo du willst.

Dieses Forum ist eine frei zugängliche Diskussionsplattform.
Der Betreiber übernimmt keine Verantwortung für den Inhalt der Beiträge und behält sich das Recht vor, Beiträge mit rechtswidrigem oder anstößigem Inhalt zu löschen.
Datenschutzerklärung