Funkcija za identifikacijo formule

[markox]

Ali kdo ve, ali obstaja funkcija ali trik, kako ugotoviti, ali vsebuje celica formulo.
Problem je sledeč. Imam eno tabelco, v nekaterih vrsticah se nahajajo formule, v drugih samo vrednosti. Formule so tipa "=A1+A3+A5" ali "=B2/B3".
Sedaj moram vrstice, kjer so samo vrednosti preračunat v tem primeru v €, kjer pa so formule, moram pustit celico nedotaknjeno.

Se pravi bi zgledala formula nekak takole "=if(not(isfunkcija(A2));A2/239,64;A2)", pri čemer je isfunkcija tista namišljena funkcija, ki je ne poznam.

Ma kdo kak drug trik ali ve za tako funkcijo.

LP Marko

[cedra]

Napisal sem vam makro, ki vam bo seštel vrednosti celic v katerih ni formule:

Koda: Izberi vse

Sub Sestej_vrednosti_celic()

On Error Resume Next

   Dim Cell As Object
   Dim podrocje As Range
   Dim x As Double
   Application.Volatile
   x = 0
Set podrocje = Selection


   For Each Cell In podrocje

        If Cell.HasFormula Then Cell.Font.ColorIndex = 3
        If Cell.Font.ColorIndex <> 3 Then x = x + Cell.Value
        Cell.Font.ColorIndex = 1
        
   Next Cell
   
   MsgBox x
   
End Sub

Najprej obvezno označite področje kjer naj makro išče vrednosti celic in ga nato poženite! V Msgbox-u pa boste dobili rezultat!

Namesto tistega MsgBox x, pa lahko določite neko celico kamor vam bo zapisal rezultat (nekaj v stilu: Range("A1") = x

Makro preizkušeno dela, čeprav sem telovadil s tem, da makro deluje tako, da v celici kjer je formula, spremeni barvo pisave v rdečo in na koncu spet nazaj v črno. Vmes med tema dvema ukazoma, pa sešteje vrednosti celic, ki zaradi tega, ker ne vsebujejo formulo, niso spremenile barve pisave.
Zanesljivo se da nalogo rešiti bolj elegantno, toda jaz ga ne poznam oz. ne vem!

[cedra]

No tudi funkcijo sem spravil skupaj

Koda: Izberi vse

Function Sestej_celice(cellrange As Range) As Double
   Application.Volatile
   Dim cell As Range
   For Each cell In cellrange.Cells
     If Not (cell.HasFormula) Then
                   If IsNumeric(cell.Value) Then
         Sestej_celice = Sestej_celice + cell.Value
                End If
     End If
   Next
End Function

Kako jo uporabiti pa verjetno veste.

[markox]

Najlepša hvala za pomoč. Bom preizkusil. Sam sem se sicer hotel izogniti pisanju makrov, saj je v zvezku precej listov, tako da bom moral kar malo telovadit. Obstaja sicer funkcija CELL, ki pa vrne vse mogoče, le to ali celica vsebuje funkcijo ne.
Bom jutri preizkusil in vam javim rezultat.

LP Marko

[cedra]

Uporabite funkcijo in ne makro, ki jo najprej prekopirajte v osebni (personal) zvezek z makri in funkcijo boste lahko uporabili povsod. V vseh zvezkih in na vseh listih. Našli jo boste med vsemi fukcijami kot "User defined" oz "Uporabnikove funkcije". Nobena telovadba ne bo potrebna.

[markox]

Super, hvala še enkrat. A pol se da recimo narest malo bolj simpl način, ko ti funkcija vrne iskano vrednost iz drugega lista.
Npr.: ena možnost, da ti vrne funkcija določeno vrednost ki se nahaja na drugem listu za iskani niz je LookUp funkcija, pri čemer pa moraš pazit na to, da so na listu, kjer iščeš niz vrednosti sortirane.

Primer : Na prvem listu imaš seznam imen, v drugi koloni pa recimo vrednosti za mesec Januar. Potem dobiš nov list, kjer so prav tako podatki z imeni ter vrednostmi za mesec februar. S tem da na drugem listu niso vsa imena, ki se pojavljajo v januarju. V tem primeru se tudi če sortiraš po imenu na drugem listu pojavljajo napačne vrednosti za tisat imena, ki se ne pojavijo na drugem listu.

Za tak primer jaz uporabljam kombinacijo funkcij Index in match:
=index('Kolona vrednosti za feb';match('Ime v listu Jan';'kolona imen v listu feb';0))

Ta funkcija mi za imena ki se ne ujemajo vrne #NV, kar je ok.

Meni ni problem uporabljati kombinacijo funkcij, če pa to pokažem nekemu bolj preprostemu upoarbniku, pa že nastane problem, ker se ne znajdejo.

Vprašanje: Ali bi lahko potem ustvaril funkcijo, kjer bi se odprlo pogovorno okno:

• Vnesi kolono, iz katere naj vrne iskano vrednost
  Vnesi celico, katere vrednosti iščeš
  Vnesi kolono, v kateri iščeš iskani niz

LP Marko

[cedra]

Moram povedati, da vašega vprašanja ne razumem najbolje. Sem pa skoraj prepričan, da za problem, že obstaja kaka vgrajena rešitev. Če opišete malo podrobneje in daste kakšen kratek primer pa vam bom skušal pomagati, če bom le znal.

[markox]

Ok kratek primer :

Podatki List 1:

Ime Znesek
Marko 1000
Jože 2000
Ivan 1500

Podatki List 2:


Ime Znesek
Marko 1300
Ivan 1600

V primeru je malo vrstic, zamislite da jih je 20.000.

Sedaj bi rad v list 1 v tretjo kolono prepisal zneske iz lista 2, seveda pripadajoče glede na ime v prvem stolpcu. Kot sem rekel, bi v primeru, da oba lista vsebujeta iste zapise v 1. koloni lahko uporabil funkcijo lookup, ker pa v drugem listu ni vseh zapiasov iz prvega lista nam ta funkcija pri mankajočih imenih prikaže napačno vrednost. Zato kot rečeno uporabljam kombinacijo funkcij INDEX in MATCH:

=index('Kolona List2 Znesek';match('Ime v listu 1';'kolona imen v listu 2';0))

[kljuka13]

Pozdravljeni,

poskusite s to kodo.

Koda: Izberi vse

Sub najdiIzpiši()
Dim ime
Dim i
Dim zVrstica

On Error GoTo err

i = 1

ime = Range("a" & ActiveCell.Row).Value
zVrstica = ActiveCell.Row

Sheets(2).Activate

Do Until Range("a" & i).Value = ime
    i = i + 1
Loop

podatek = Range("b" & i).Value

Sheets(1).Activate

Range("c" & zVrstica).Value = podatek
Exit Sub

err:
MsgBox "Ne najdem podatka!"
End Sub

[markox]

Ja bi reku simpl k pasulj. Hvala, samo mislil sem si dodati funkcijo, ki bi vodila uporabnika da izbere tri parametre. Se pravi:

• Kolono, iz katere jemlje rezultrat
  Iskana vrednost
  Kolono, v kateri naj išče iskano vrednost

Se pravi da bi nek preprost uporabnik namesto tiste kombinacije dveh uporabljal mojo funkcijo.

LP Marko[/list][/list]

[kljuka13]

Ja bi reku simpl k pasulj. Hvala, samo mislil sem si dodati funkcijo, ki bi vodila uporabnika da izbere tri parametre. Se pravi:

• Kolono, iz katere jemlje rezultrat
  Iskana vrednost
  Kolono, v kateri naj išče iskano vrednost

Po pravici povedano, ne razumem. Zakaj tri parametre .

Koda: Izberi vse

Function najdiIzpiši()
Dim ime
Dim i

On Error GoTo err

i = 1

ime = Range("a" & ActiveCell.Row).Value

Do Until Sheets(2).Range("a" & i).Value = ime
    i = i + 1
Loop

podatek = Sheets(2).Range("b" & i).Value

najdiIzpiši = podatek
Exit Function

err:
najdiIzpiši = "Ne najdem podatka!"
End Function

[markox]

Ja, saj tale makro je pravilen. Ampak kako bom uporabil funkcijo, če se na primer iskana vrednost nahaja v koloni F. Potem ne bo delovala. Rad bi parametriziral katero vrednost iščem(določena celica), V katerem stolpcu se nahaja iskana vrednost ter v katerem stolpcu se nahaja rezultat, ki naj ga vrne, če najde iskano vrednost. Se pravi naj odpre pogovorno okno za vnos parametrov.

LP Marko

[kljuka13]

Tako, sedaj pa upam, da bo v redu.

Koda: Izberi vse

Function najdiIzpiši()
On Error GoTo err

Dim i
Dim podatek
Dim kolona
Dim iskalnaBeseda As String
Dim kolonaSPodatkom

kolona = InputBox("Kolona s rezultatom")
iskalnaBeseda = InputBox("Iskana vrednost")
kolonaSPodatkom = InputBox("Kolona s iskano vrednostjo")

i = 1

Do Until Sheets(2).Range(kolonaSPodatkom & i).Value = iskalnaBeseda
    i = i + 1
Loop

podatek = Sheets(2).Range(kolona & i).Value

najdiIzpiši = podatek
Exit Function

err:
najdiIzpiši = "Ne najdem podatka!"
End Function

P.S. Iskana vrednost je podatek(beseda) in ne celica!"

[admin]

Uporabnik pri prvem vprašanju išče način za ugotovitev ali neka celica vsebuje funkcijo ali ne. Če je to še vedno aktualno, je tukaj odgovor:

Excel mislim, da te možnosti ne pozna, jo pa pozna VBA in si takšno funkcijo lahko preprosto napišemo:

Koda: Izberi vse

Function AliVsebujeFunkcijo(Podrocje As Range)
    AliVsebujeFunkcijo = Podrocje.HasFormula
End Function

In potem funkcija =AliVsebujeFunkcijo(A4) vrne TRUE, če celica A4 vsebuje funkcijo in FALSE če je ne.