Egne regnearksfunktioner i Excel – smart

Jeg har fået øjnene op for, hvor smart det er at definere sine egne regnearksfunktioner og gemme dem i et tilføjelsesprogram.

Normalkubikmeter

Funktion til beregning af normalkubikmeter gas ved 1013 mbar, 0° C, tør og 10 % ilt

Jeg har længe vidst, at man kan lave sine egne regnearksfunktioner i Excel, som kan supplere de indbyggede (SUM, MIDDEL osv.), men havde aldrig dyrket det, før en læser satte mig i gang.

Han spurgte, om jeg vidste, hvordan man kunne få SUM.HVIS-funktionen til at ignorere skjulte celler?

Tænk, jeg kendte slet ikke SUM.HVIS-funktionen, men fandt ud af, at den summerer cellerne i et område, hvis nogle celler i et andet område opfylder en betingelse.

At funktionen medregner skjulte celler kan fx være et problem, hvis man arbejder med filtrerede data, hvor Excel skjuler de rækker, som er filtreret bort.

På nettet fandt jeg en langhåret omvej, som benytter hele tre andre regnearksfunktioner, men det er ret indviklet.

Jeg skrev derfor en brugervenlig regnearksfunktion (makro) i VBA, og læseren blev glad.

Det gav mig blod på tanden. Jeg indså, at jeg på arbejde ofte beregner forskellige ting, men ikke ofte nok til at kunne ryste formlerne ud af ærmet, og så må jeg slå op.

Hvordan er det nu lige, at man kommer fra mg NO2 til mol NO? Hvor mange mol NH3 er der i en liter ammoniakvand 24,5 %, og hvordan er det nu, at man kommer fra et målt gasflow i m³ til normalkubikmeter ved 1013 mbar, tør, 0° C og 10 % ilt? Osv.

Jeg skrev en stribe regnearksfunktioner i VBA og gemte dem i et tilføjelsesprogram, så de er tilgængelige for alle mine regneark. Det skulle jeg have gjort for længe siden, men sådan er der så meget.

Hvis det er helt sort snak for dig, er en brugerdefineret funktion en stump kode i et regnearks kodedel, et VBA-modul, og en helt primitiv funktion, der beregner en firkants areal, kunne se således ud:

Function FIRKANTAREAL(Længde, Bredde)
   FIRKANTAREAL = Længde * Bredde
End Function

Når man har gjort det, fungerer den præcis som Excels indbyggede funktioner – den skal bare fodres med længde og bredde.

Jeg har nu skrevet en side om min nye kærlighed, Egne regnearksfunktioner og tilføjelsesprogrammer.

Funktionen, der virker som SUM.HVIS, men ignorerer skjulte celler, har jeg også lagt ud, da jeg kan se, at det er en facilitet, der ind imellem efterlyses.

Så gik tiden med det. Nu mangler jeg bare en funktion, der kan slå græsset, men indtil videre kan jeg ikke komme på andet end et pop op-vindue, hvor der står:

”Let rumpen!”

/Eric

16 thoughts on “Egne regnearksfunktioner i Excel – smart

  1. Stegemüller

    Hvor er det dog fascinerende – tænk al det man kan vha. de fire regningsarter! De fire regningsarter og så de fire regningsarter…. Jeg vil gå langt og se, hvor jeg kommmer hen, og dernæst se, hvor jeg kommer hen…. ….. se hen hvor langt man kommer hen.

    Svar
  2. Donald

    I dag ved jeg hvor meget man kan med et regneark. MSoft Excel er udmærket. Jeg tænker somme tider tilbage på de ting jeg har oplevet når jeg som her skal sætte flexibiliteten af regneark i perspektiv. Jeg fik for mere end 25 år siden (og jeg føler ikke rigtigt at der er gået 25+ år) en opgave for daværende Aktuelt administration, eller Den Socialdemokratiske Presse, og det springer frem i min erindring at IT-chefen blev fyret efter at have betalt mig 20000 kr. for et par ugers arbejde (måske ikke derfor, for man hyrede mig igen, hans efterfølger ville have nogle større ændringer, men jeg måtte takke nej, det var ikke “bare lige”). Og avis-administrationen købte et andet større avis-system fra Siemens som også var fuldt af problemer.

    Men på et tidspunkt inden IT-chefen måtte gå, spurgte han om ikke man kunne sige at man programmerer et regneark, (jeg går ud fra at det betød et kryds i et CV), joe, det kunne man vel godt sige, sagde jeg og tænkte, som jeg stadig gør, at folk burde bruge et programmeringssprog som C til det meste, hvis vel at mærke man kunne blive enige om formatet for hjælpefunktioner – sådan at man fx. kunne sige “smæk resultatet af disse beregninger op i et regneark”, og i stedet for at programmere et besværligt skærmbillede med redigering af felter og input – bare kalde en funktion “læs input fra regneark” (readc_sheet(ident, &values) eller noget i den retning.)

    Så vidt jeg ved kan man faktisk sådanne ting i dag, men jeg er ikke up-to-date og programmerer ikke ret meget i dag.

    Svar
    1. Eric

      Jeg er ikke rigtig med på, hvad du mener med dine C-funktioner. Jeg tror faktisk, at Excel er programmeret i C++, så på en måde kan man godt sige, at C læser input fra regnearket og serverer resultatet. VBA, som man kan bruge til selv at programmere med, er selvfølgelig ikke på højde med C(++), om end det er gennemført objektorienteret, men det er nemmere at lære, og med tidens hurtige computere, afvikles koden faktisk pænt stærkt.

      Svar
      1. Donald

        VBA og Excel er fremragende produkter – det skal understreges. Jeg forestillede mig (ud fra en hvad jeg har set i en virksomhed) at det var et C-program, som byder Excel at køre et regneark m. diverse inputfelter og sende data tilbage i beregnet form.

        Det man som udvikler (programmør, system-planlægger/designer) tænker om MSoft og VBA er at VBA er et “ad-hoc” sprog og at det ville være rart, hvis kunne styre et Excel-system ud fra et overordnet C-program (altså lade excel hente input, ikke omvendt).

        Man kan meget med VBA, bl.a. vil man også kunde sende data fra et excel ark til en fil og derfra til et regnskabsprogram, men en system-designer vil blive lidt bekymret over at sætte sin lid til sådan en mekanisme der kan være vanskelig at skalere. Man ønsker at de enkelte elementer i et IT-system skal være udskiftelige, så at man med tidens gang kan forbedre (“vedligeholde”).

        Da jeg begyndte på denne kommentar, anede jeg ikke hvor meget det fylder at forklare rationalet, begrundelsen, for systemdesign. En Manchester Uni lærer Andrew Hazel forklarer det bl.a. sådan her:

        At the time of writing, there are hundreds, if not thousands, of different programming
        languages, each with different strengths and weaknesses. The choice of programming
        language is driven in part by the nature of the project. Excel Visual Basic for Applications
        (VBA) is the native language of Excel and is ideal for writing small extensions, or macros,
        within Excel Worksheets. In essence, Excel is the compiler for Excel VBA. VBA is not
        suitable for every task, however. One restriction is that the language is not very portable;
        every Excel VBA program must run from within Excel, which means that you need to
        have Excel installed on your computer. In addition, for intensive numerical calculations,
        VBA can be rather slow. [… … …]
        Ch.5 Interfacing C++ and Excel
        Communication between C++ and Excel is a complex topic and we shall cover only the
        very basics in these notes. There are two different (philosophical) choices:
        · Call a C++ Add-in from Excel,
        · Call Excel functions from within a C++ program.
        http://www.maths.manchester.ac.uk/~ahazel/EXCEL_C++.pdf

        — Den PDF og mine tanker handler egentlig om at lære unge mennesker om computer-teknik i bred almindelighed med Excel som eksempel. Til almindelig MSoft en- eller tomandsbrug er Excel+VBA et godt valg.

        Svar
        1. Eric

          Så er jeg mere med, tak! Det kunne vel fikses ved at gøre Excels dll’er (altså dem med funktionerne) “kaldbare” fra eksterne programmer. Noget i stil med de API-kald man kan lave til Windows.

          Svar
          1. Donald

            Ja, det er i dll’erne at excels egne funktioner styres, det er den direkte og hurtige måde. Der er også en anden måde at koble sig på MS-produkter (COM, component object model) som er mere kompliceret.

            Svar
  3. Henny Stewart

    En funktion, der kunne rydde op, ville være ideel til mig. Så skulle jeg såmænd nok selv klare rengøringen. Men det er det der “clutter”, der samler sig alle vegne, og også lader til at formere sig, der er lemet. Lav en “declutter”-macro, og du vil blive rigere end Bill Gates, Zuckerberg, ham Applefyren, og alle de andre til sammen!

    Svar
    1. Eric

      Det har nok samme udsigter som græsslåningen, men du kan da få en kopi af Let rumpen!-makroen, hvis det har interesse. 😉

      Svar

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *