1 / 12

Querying Using Excel

Querying Using Excel. Asking questions of the data. If you imagine the master table as a set, when you ask questions of the data you will filter out information into subsets eg all the servants, the women, the children, the male defendants found guilty of murder etc.

muriel
Télécharger la présentation

Querying Using Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Querying Using Excel

  2. Asking questions of the data • If you imagine the master table as a set, when you ask questions of the data you will filter out information into subsets eg all the servants, the women, the children, the male defendants found guilty of murder etc. • In each case go to Data then Filter • Next to each heading a box with a down arrow appears. This will form the basis for our queries.

  3. Meeting exact conditions • For example all those presented at the Frankpledge court • Click on the down arrow next to the ROLE column. • Click on Presented. • To return to the whole table, click on the clear button • The bottom line tells you the number of records filtered from the total.

  4. Text filters • Use the text filters to refine the search • For example using does not equal or does not contain • Or begins with/ends with • The custom autofilter allows wildcards. So b*r will select butcher, baker, builder etc. *smith will select blacksmith, gunsmith, coachsmith. Sm?th will select Smith and Smyth

  5. Ranges • It is possible in numerical fields to select data within a certain range. • For example to select all the presentments between 1600 and 1605 use the Number Filter option and choose greater than 1600 and less than 1605. • The Top Ten… function allows you to select the ten most numerous values.

  6. Pivot tables • To create frequency counts or cross tabulations use the Pivot Table option • The pivot table allows you to select fields to count or to summarise • You can count up all the butchers, bakers and candlestick makers • Or you can compare columns such as age and sex or role and place

More Related