[excel] cel doorstrepen en niet meer optellen

[Galois]

Eenvoudige vraag en na een kwartier zoeken, geef ik het op.
(en ongetwijfeld zijn de eerste drie reacties 'je hebt niet goed gezocht, het is heel makkelijk' )

Ik heb een rekening in Excel waar ik bepaalde posten wil doorstrepen. Dat kan natuurlijk erg eenvoudig. Maar ik wil dat als ze zijn doorgestreept, dat ze ook niet meegenomen worden in de som. En dat lukt mij niet op een eenvoudige manier.

Wie?

[Arjan90]

Hoe bedoel je doorstrepen, door middel van opmaak? Volgens mij is er geen standaard manier voor in Excel waardoor je dus een eigen functie hiervoor zal moeten schrijven... Maar waarom wil je dit?

De makkelijkste manier zou m.i. zijn om er een kolom voor te plakken met een selectieveld ("ja" of "nee"). Met voorwaardelijke opmaak streep je dan de cel ernaast door (dus als het "nee" is) en met een sommen.als formule tel je alleen de waarden in bijv. range B:B als in range A:A de waarde gelijk is aan "ja". Dus:

Ja	10,00
Ja	20,00
Nee	20,00
Subtotaal	30,00

Je kan de "ja" en "nee" verbergen door de tekst daar wit te maken en de achtergrond ook wit te maken.

[ Voor 26% gewijzigd door Arjan90 op 09-11-2013 10:36 ]

[Galois]

Waarom ik dit wil is eenvoudig.
Ik heb een rekening, en op die rekening moeten bepaalde posten doorgestreept worden, omdat ze vervallen zijn. Maar ik wil wel zien dat ze er wel opgestaan hebben. Dus ze moeten nog wel zichtbaar (doorgestreept) zijn, maar niet meer opgeteld worden.

Snap je wat ik bedoel?

De oplossing hierboven die had ik ook bedacht, maar dat vind ik wel een heel gedoe. En ik dacht dat er vast wel een makkelijkere manier zou zijn. Het is denk ik iets wat wel vaker voorkomt toch? Iets op een rekening doorstrepen en dus niet meer in rekening brengen, maar nog wel zichtbaar.

[ Voor 32% gewijzigd door Galois op 09-11-2013 10:38 ]

[Arjan90]

Voor de duidelijkheid: bedoel je met je rekening een factuur? Dan is het absoluut niet normaal om door te strepen namelijk. Als er een fout is gemaakt op een factuur moet je dit corrigeren door een creditfactuur te versturen. Wat jij noemt is eerder iets wat je op een grootboek doet, maar dan heb je altijd een 'tegenboeking' (dus als je 30 euro in de plus hebt dan heb je ook altijd 30 euro in de min, die zijn dan samen 0 en zijn dan dus pas vervallen).

Maar wat je eventueel zou kunnen doen:

Product 1	10,00
Product 2	20,00
Product 3	20,00	Vervallen
Subtotaal	30,00

[ Voor 28% gewijzigd door Arjan90 op 09-11-2013 10:42 ]

[Galois]

Arjan90 schreef op zaterdag 09 november 2013 @ 10:40:
Voor de duidelijkheid: bedoel je met je rekening een factuur? Dan is het absoluut niet normaal om door te strepen namelijk.

Product 1	10,00
Product 2	20,00
Product 3	20,00	Vervallen
Subtotaal	30,00

Dit is inderdaad wat ik wil op een eenvoudige manier! Zonder een derde kolom.

(en ik bepaal zelf wel of het normaal is of niet )

[Arjan90]

Laat ik mijn antwoord dan anders formuleren: je vraagt of dit wel vaker voorkomt en ik denk dat je dit niet of zelden tegen zal komen. Het enige wat je eventueel tegenkomt is een kortingspercentage van 100% maar je kan niet zelf iets doorstrepen op een factuur en zeggen dat het is vervallen. Dat accepteert een accountant niet.

Ik denk dat het niet gemakkelijk kan zonder de derde kolom, maar dan moet je even zoeken op Excel UDF en uitzoeken wat Excel aangeeft als je de tekst in een cell doorstreept.

[ Voor 20% gewijzigd door Arjan90 op 09-11-2013 10:47 ]

[Fish]

Galois schreef op zaterdag 09 november 2013 @ 10:42:
[...]


Dit is inderdaad wat ik wil op een eenvoudige manier! Zonder een derde kolom.

(en ik bepaal zelf wel of het normaal is of niet )

Uhm nee, een normaal is baseerd op meerdere bevindingen. wat jij normaal vind hoeft niet normaal te zijn

[Galois]

Arjan90 schreef op zaterdag 09 november 2013 @ 10:45:
Laat ik mijn antwoord dan anders formuleren: je vraagt of dit wel vaker voorkomt en ik denk dat je dit niet of zelden tegen zal komen. Het enige wat je eventueel tegenkomt is een kortingspercentage van 100% maar je kan niet zelf iets doorstrepen op een factuur en zeggen dat het is vervallen. Dat accepteert een accountant niet.

Ik denk dat het niet gemakkelijk kan zonder de derde kolom, maar dan moet je even zoeken op Excel UDF en uitzoeken wat Excel aangeeft als je de tekst in een cell doorstreept.

De reden is onbelangrijk toch? Ik wil de oplossing!

offtopic:
Maar als je het perse wilt weten: we hebben een offerte gekregen van een aannemer voor de complete renovatie van een huis. Ik heb 'm gevraagd om daarop alles te zetten. Nu is het zo dat ik de kosten iets wil drukken door zelf bepaalde werkzaamheden te doen, zoals bv. het sloopwerk en het schilderwerk. Maar aangezien ik voor het totaalplaatje wel wil zien wat er geschrapt is, dacht ik het eenvoudig zo te doen zoals jij in jouw tabel hierboven schetst.

[Xtremelead]

Galois schreef op zaterdag 09 november 2013 @ 10:42:
[...]


Dit is inderdaad wat ik wil op een eenvoudige manier! Zonder een derde kolom.

(en ik bepaal zelf wel of het normaal is of niet )

Je kan altijd nog de derde kolom verbergen

[RemcoDelft]

Galois schreef op zaterdag 09 november 2013 @ 10:36:
De oplossing hierboven die had ik ook bedacht, maar dat vind ik wel een heel gedoe. En ik dacht dat er vast wel een makkelijkere manier zou zijn. Het is denk ik iets wat wel vaker voorkomt toch? Iets op een rekening doorstrepen en dus niet meer in rekening brengen, maar nog wel zichtbaar.

Als je willekeurige tekst bij het bedrag typt, wordt het niet meer als getal gezien.

LibreOffice Calc:

[Fiber]

Je wilt dus eigenlijk een voorwaardelijke opmaak die andersom werkt: Opmaak dicteert celeigenschappen.

Maar wat is er tegen een extra kolom? Die kun je later altijd nog verbergen. Of je maakt een extra blad aan met een kopie van de kolommen die je wil laten zien zonder de 'hulp' kolommen.

[Fish]

RemcoDelft schreef op zaterdag 09 november 2013 @ 10:53:
[...]

Als je willekeurige tekst bij het bedrag typt, wordt het niet meer als getal gezien.

LibreOffice Calc:
[afbeelding]

mja of je zet het er gewoon in als tekst, dus met een enkele qoute ' ervoor

[Lustucru]

Galois schreef op zaterdag 09 november 2013 @ 10:30:
(en ongetwijfeld zijn de eerste drie reacties 'je hebt niet goed gezocht, het is heel makkelijk' )
Wie?

Beter zoeken

O.a. deze post excel cellen op kleur sorteren/optellen geeft antwoord op de vraag hoe je -zonder hulpkolom- cellen optelt met een bepaalde achtergrondkleur. Een kleine wijziging laat je cellen optellen met een specifiek opmaak.

of zie hier: [Excel] gekleurde cellen tellen*

[ Voor 6% gewijzigd door Lustucru op 09-11-2013 10:57 ]

[Fiber]

Overigens lijkt het mij handiger om in plaats van de posten door te strepen die posten gewoon in een andere kolom te zetten. Dan kun je daar ook weer mee rekenen...

[- J.W. -]

code:

Public Function SUMNOSTRIKE(ByVal myRange As Range)
Dim cell As Range, x As Double

For Each cell In myRange
If cell.Font.Strikethrough = False Then x = x + cell
Next cell
SUMNOSTRIKE = x
End Function

In een module doen en dan kun je in de workbook bijv. =SUMNOSTRIKE(A1:A3) doen.

Kleine aanpassing op dit

[F_J_K]

VBA is eleganter / mooier, maar het kan zonder (bijv als de sheet wordt gedeeld, anderen zullen hopelijk andermans selfsigned VBA niet accepteren/vertrouwen):

Verborgen (of buiten printbereik) hulpkolom C die gelijk is aan de waarde.
Sommeer in kolom B de kolom C
Zet C op 0 als je wilt wegstrepen
Voorwaardelijke opmaak: als 0 dan doorstrepen.

-

Je wilt inderdaad wel oppassen die enkel voor intern gebruik doen. Afspraken met externen wil je helderder maken dan enkel doorstrepen. (Al doe ik het ook wel eens op een opdrachtbevestiging, met pen ).

[ Voor 16% gewijzigd door F_J_K op 09-11-2013 11:47 ]

[Lustucru]

Lustucru schreef op zaterdag 09 november 2013 @ 10:56:
[...]

Bij nader inzien is er een mooiere, generieke VBA oplossing denkbaar door de berekening zelf uit de functie te halen.
Plak het volgende in een nieuwe module:

Visual Basic:

Option Explicit
Option Base 1

Public Function StrikedThroughValues(r As Range, IsStrikedThrough As Boolean) As Variant
    Application.Volatile True
    ReDim v(r.Rows.Count, r.Columns.Count)
    Dim i As Integer, j As Integer

    For i = 1 To r.Rows.Count
        For j = 1 To r.Columns.Count
            If (r.Cells(i, j).Font.Strikethrough = IsStrikedThrough) Then
                v(i, j) = r.Cells(i, j)
            End If
        Next
    Next

    StrikedThroughValues = v
    
End Function

De functie levert een matrix op van celwaarden die voldoen aan het criterium (in dit geval al dan niet doorgestreept). De functie kun je dan weer gebruiken als input voor iedere Excelfunctie die een matrix accepteert:

Visual Basic:

=Som(StrikedThroughValues(A1:B5;WAAR)) 'levert het totaal van doorgestreept
=Som(StrikedThroughValues(A1:B5;ONWAAR)) 'levert het totaal van niet doorgestreept
=Aantal(StrikedThroughValues(A1:B5;WAAR)) 'levert het aantal doorgestreepte waarden
etc.