Planning and creation a database of medical data using Excel

Размещено на http://www.allbest.ru/

NJSC "Astana Medical University"

Department of general medicine

Planning and creation a database of medical data using Excel

Done by: Cheprakov Viktor 140 GM

Astana, 2020

Content

Introduction

1. Beginning of the medical investigation

2. Creating the first draft table in Excel

3. Acquaintance with Excel capabilities

4. Freezing Window Panes

Conclusion

References

Introduction

Medicine is not only about curing diseases. A qualified doctor, or even a student of the medical university should be able to operate with information. Considering the fact that there are different types of data, the knowledge people can get through the Internet, or from observations and medical data analysis, there should be something that can help with storage and systematization of that kind of information. That is why the ICT course is necessary in studying medicine. In this work I want to discuss one software program named Excel, and the way it can facilitate the process of working with medical data. The importance of the ability to use this program is always underestimated. It may be assumed that such a skill is either inherently present or can be quickly acquired on the job through referencing appropriate software manuals. Such manuals, however, are not often specifically geared to the needs of the medical trainee creating and using a clinical research database.

This study is dedicated to estimate the importance of Excel program in creating of database based on some medical data. Moreover, this work will provide people with medical trainees with a short, practical, hands-on tutorial on creating an effective database for the purposes of handling medical research data using readily available commercial software, Microsoft Excel. The instructions and figures used in this study refer to Microsoft Excel, however the information is relevant for other programs with the same functions, despite of different operating system you may have.

1. Beginning of the medical investigation

Before the data collection, you should make a plan of your further study. First of all, determine all the variabilities you may have during investigation. This is very important step, because you are planning to create a database in Excel after the work is done, so you should know what kind of datasheet you will get at the end. The list of variables should be as simple as possible, but no simpler. A common mistake is to design a study that tries to collect data on every possible thing in the hopes that something unexpected may later prove to be relevant. That kind of thought is absolutely wrong, because all hypotheses to be tested should be defined a priori. Moreover, every variable will add significant time to the data collection process, and some data can be unnecessary that can take hours from you. On the other hand, another important problem is having to go back and do it all again because an important variable was initially forgotten. Investing some time and thought upfront to designing the best possible data list can provide you with extra time you may need in the future.

2. Creating the first draft table in Excel

Your research spreadsheet should be created in a table format. Each row will contain data for the same single variability (such as one patient, one procedure, etc). Each column will contain the same single category of data (such as age, erythrocyte count, outcome, etc). There should be a heading row with the names of each column. The Heading Row is often situated on the top row of the spreadsheet. However, I recommend entering the Heading Row into the second row of the table. Then use the first row as an informational reference that contains definitions, instructions, cut-offs (the example will be given in the picture 1).

It is best to keep each variable name to 8 characters or less in the heading row, because all the needed information can be seen in the first row. An instruction sheet is often provided to data-collectors on a separate document, but there is no reason why it cannot be available right there on the spreadsheet itself. This is particularly useful if there will be more than one person working on data collection.

The informational reference can be deleted once all data collection is complete, because it was used for quick understanding of each type of data you have. That is why you can operate with the text there as you want. For example: you can colour-code this row to suit your needs. For example, I have used red for columns that are variable, so I can choose to hide them while doing my collection to make the sheet less congested. The green is for individual information, and the orange is for non-variables I can find in the patient chart. Colour-coding guides my eyes to where they need to be at any point in time.

3. Acquaintance with Excel capabilities

Formatting as a Table Using the cursor, select to highlight row 1 and as many additional rows as you will need for your data. Click on Home > Format as Table > select a Table Design of your preference > select "This Table has headers." You will notice that the spreadsheet is now formatted as a table, the first is highlighted. That is how you can highlight the important data. (Picture 2)

Using Autofill function. To quickly fill a column, select a cell, position the mouse over the Fill Handle (the little square at the lower right corner of the cell), and click and drag to the end of the column. This can be used to either copy the data in the cell, or to fill the column with an ordered sequence (such as numbers, letters, days, months, years, or a custom list of your choosing). As an example, you can write number 1 in the first cell and under this cell write 2, then choose these two cells and drag them down from the lower right corner. That is how you can get a sequential row of numbers. (Picture 3)

Choosing the format of data.

You need to tell Excel what type of data is in each particular column and how it is structured. This is especially crucial for columns that will contain dates that will be used for auto-calculations. Since most clinical research will need to calculate ages or times-to-events, this is commonly needed. For numbers, formatting will allow you to specify the number of decimal places that Excel should round to for that numeric variable. Select the entire column by clicking the Whole Header, right-click on any cell within the selected column, and click Format Cells. For dates, specify the format of how you will be entering dates (such as "dd/mm/yyyy"), and also put this into your Legend Row to remind yourself of what you chose. (Picture 4)

Picture 4.

4. Freezing Window Panes

You may want the first and second rows to remain visible even as you scroll far down the datasheet, or may want the first column (ID of the patient) to be visible even as you scroll far to the right. This will ensure that you are entering data into the intended column or row, thereby reducing entry errors. To do this, go to the View tab > Freeze Panes. For an example I chose the top two rows and the first column to be visible at all times. I place the cursor in the cell below and to the right of what I want to see (in this case, cell B3) and then click on Freeze Panes. (Picture 5) excel program medical

Drop-down lists.

Drop-down lists allow the data collector to select from the provided parameters in the field, rather than entering them manually. This is a great way to save time, maintain consistency, and avoid spelling and typographical errors. This is especially useful if you have multiple data collectors, each of which may have its own way of entering data. To create a drop-down list, first find an empty space on the Excel sheet to the right and below the main table. Enter the parameters that you want to display in the drop-down list in different cells of the same column. Then click on the cell in your main table where you want to display the list. Click on data > data validation > data validation > settings> in the allow menu, select list > click on the source row. Now select all the cells outside the main table where you wrote your drop-down list. You will notice that a down arrow appears in the selected cell. Clicking this arrow opens a drop-down list to display the list entries. To apply the list to each cell in a column, take the AutoFill square and drag it across the entire column. (Picture 6)

Auto-Calculations.

Excel can perform mathematical functions on the data contained in your table, so you don't need to. Let's assume that you need to find out the average values of any value for a proper conclusion about the study. A function like the average value comes to the rescue. All you need to do is select a cell, click on the auto-sum icon, select the average value, and then select the area of values that are needed for the study. When you click enter, the formula itself calculates the average value for the selected area. Example based on height of the patients is given below. (Picture 7)

The process of collecting of information.

Now you are aware of some useful features of Excel that can make your work easier. There are some more functions that are used during data collection.

Hiding.

Hiding and displaying entire columns is a good way to make your worksheet less congested during data collection. You can hide all columns that are already completed or use automatic calculations so that they don't interfere. Click on full screen Header to select the columns of interest, right-click and select Hide or Unhide.

Entering Dichotomous Data.

If there is only two variants of data in the column (male/female, yes/no, high/low, etc.) you can use 0's and 1's rather than any other combination of numbers and letters. It sounds complicated, however it can be very useful and convenient, when you get used to it. At the end of the data collection, click on the entire column header and you will see the amount reported in the lower-right corner of the Excel window. The sum quickly lets you know the number of patients in each category in this column. For example, you entered the gender data as Male=1, Female=0, if you select column with the information about gender and the sum is 12, you will immediately know that in your table of 25 patients there are 12 men and 13 women.

The last steps, when the data was collected.

Sorting data.

You can sort your data so that it appears in a specific order classed by your variables of interest. For example, if you want to see patients beginning from the youngest one, open the Data Tab, and choose the Sort Button. This allows you to sort the entire spreadsheet by category in this column. You can also sort by multiple categories at once. To return to the original layout, sort by the ID variable that contains the original numeric sequence of patients. Warning: never select the entire column before clicking the button Sort button. This will only sort this column and none of the others, and all your data will be out of sync. If this happens, quickly click undo button.

Using of Filters.

You can filter your data table to see only those elements that fill in the criteria you set. Rows that do not meet your criteria are hidden from view, but are not deleted. Excel places filter arrows in a table row when formatting a spreadsheet as a table. For example, if you want to see all patients with a hemoglobin level below 120, click the filter button in the corresponding column and select less than 120 from the filter parameters. To return to the full spreadsheet, click filter again and select All.

Other Functions.

If you need some specific calculations, there is a lot of formulas in the Excel. Choose an empty cell and click on the formula button under the ribbon. There are several fields and types of function, look for the one you needed.

Conclusion

At the very end I hope that after this study you can create your own medical database of your interest or field of research. Excel has many capabilities that can aid in the design of an effective research database, but that are often under-utilized by trainee researchers. This tutorial will hopefully allow users to quickly build a database that is efficient, organized, and easy to use.

References

1. https://support.microsoft.com/en-us/office/excel-for-windows-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb

2. https://utmj.org/index.php/UTMJ How to Design an Effective Clinical Research Database article

Размещено на Allbest.ru