Study Guides (247,934)
United States (123,242)
Business (15)
BUS 150 (12)
Midterm

CIS 150 Exam 2.pdf

2 Pages
186 Views
Unlock Document

Department
Business
Course
BUS 150
Professor
Maryann Barber
Semester
Spring

Description
Simple  Tables  (Structured  Data) • As  you  go  down  in  level,  you  get  less  informa▯on Data:  raw  facts • Prin▯ng:  can  collapse/expand  different  groups,  to  print  informa▯on  you   Informa▯on:  data  made  useful  by  processing/manipula▯ng/rearranging want  to  show 3  ways  to  convert  data  in  informa▯on:  sequence,  select,  calculatePivotTables  and  Pivot  Charts • Sor▯ng:  pu▯ng  in  numeric,  alphabe▯c,  or  chronological  order  Purpose  of  Pivot  Table:  Summarizes  data,  works  best  with  a  lot  of  data,  easy  to   (sequencing) rearrange • Filtering:  establishing  criteria  and  displaying  only  records  that  meet  it   (selec▯ng) • Calcula▯ng:  taking  raw  numeric  data  &  using  in  formulas/func▯ons Table:  stores  data  on  a  single  en▯ty  type,  made  up  of  records • Record:  specific  occurrence  of  the  en▯ty  (rows  in  table) • Each  record  should  be  unique • Made  up  of  fields • Field:  a▯ributes/characteris▯cs  of  en▯ty  (columns) Value  Fields:  Text  Fields  are  counted,  Numeric  Fields  are  summed • Numeric  Fields  are  automa▯cally  placed  in  values  area,  they  are  what  will  be   Record  Layout:  the  records,  their  order,  &  data  type  of  individuasummarized;  same   fields  should  be  in  same  order Row  Labels  Fields:  Text  Fields  automa▯cally  go  to  Row  Labels  regardless  of  how   many  you  check Non-­‐Unique  Fields:  are  permi▯ed  depending  on  the  situa▯onor  every Column  Labels  Fields:  Column  Labels  will  be  column  entries Simple  Table/Excel’s  Rules  for  Crea▯ng  Tables Report  Filter  Fields:  Allows  you  to  filter,  adds  dimension Field  Names  can  have  le▯ers,  numbers,  and  blanks  &  must  be: • In  header  row/first  row Crea▯ng  and  Rearranging  the  PivotTable • Unique • Format  numbers;  align  labels;  fix  column  widths;  rename  column  &  row  labels   • Forma▯ed  differently  from  data  records and  sum  of  amount • Descrip▯ve  of  data  in  field • Every  ▯me  table  is  refreshed,  columns  and  labels  will  be  changed  back  to   • Must  have  a  blank  row  above  &  below  and  a  blank  column  to  default  format  se▯ng   of  table   • If  PivotTable  is  too  large,  then  group,  i.e.  by  month • Excep▯on:  if  table  starts  in  the  1st  row  or  column • Pivot  Table  Field  List:  can  filter/sort  by  column  and  or  by  row  labels • 1  table  per  sheet • No  blank  rows  or  columns  within  table,  but  you  may  have  Refreshing  PivotTable  Data: • Pivot  table  isn’t  linked  to  data,  so  refresh  it  when  you  make  changes Excel  Table:  Insert  -­‐-­‐>  Table,  we  have  not  covered  this  ty•e  of  table  because  it   contains  structured  references Crea▯ng  and  Modifying  PivotCharts: Excel’s  Rules  for  Crea▯ng  Fields • You  cannot  have  a  PivotChart  without  a  preexis▯ng  PivotTable • Data  needs  to  be  stored  in: • 1st  column  of  PivotTable  becomes  x  axis  (category  labels)  of  PivotChart • Smallest,  useful  unit  for  flexibility,  i.e.  first  name  is  s•parate  from  last  Table  are  connected,  what’s  done  to  one  is  done  to  other name • Form  that  requires  least  amount  of  maintenance,  i.e.  DOB Slicers: • Numbers  not  used  in  calcula▯ons  should  be  entered  as  text,  •.eA  visual  tool  that  shows  what  filter  is  in  effect  for  PivotTable   • Should  be  done  prior  to  entering  data,  or  may  lose  lea•inAllow  you  to  filter  a  pivot  table • Will  print  as  long  as  in  print  area Filtering  Data • PivotTable  Connec▯ons:  Connect/disconnect  slicer  to  more/less  PivotTables • Applying  filters  to  one  or  more  fields: • When  filtering  2  items  in  the  same  field,  the  condi▯ons  Logical  Func▯ons connected  by  OR IF/Nested  IF  Func▯on:  =IF(condi▯on,  value  if  true,  value  if  false) • If  they  are  dates/numbers,  then  the  condi▯ons  must  be  connected   by  AND VLOOKUP  Func▯on:  =VLOOKUP(value  to  be  looked  up,  table  in  assump▯on  area,   • When  filtering  in  2  fields  or  more,  the  condi▯ons  must  be  connected  by   column  #  within  table  containing  result  we  want  to  use,  TRUE  or  FALSE) AND • TRUE:  approximate  match • Clearing  filters: • If  you  end  the  VLOOKUP  func▯on  a▯er  column  #  then  it  assumes  it  is   • To  clear  all  filters  choose  clear  bu▯on;  to  clear  one  filter  use  the  arrow   above  the  column • An  approximate  match  means  it  will  look  for  largest  value  less  than  or   • Text  Filters,  Number  Filters,  Date  Filters: equal  to  what  you  are  looking  up • Allow  you  to  broaden  way  you  filter • Comparison  values  need  to  be  in  ascending  order • For  dates:  Must  have  month,  day,  and  year • FALSE:  exact  match • Never  put  assump▯ons  on  the  side  of  a  table  because  rows  may •  be  hidden,  so    text needs  to  go  above  or  below • Entries  can  be  in  any  order  in  the  table • 1st  column  in  table  HAS  to  be  comparison  values Sequencing  Data • Don’t  include  headings  when  selec▯ng  table • Ascending  Order:  0-­‐1000  (lowest  number  first),  A-­‐Z  (alphabe▯c), •  i.e.  =VLOOKUP(B4,$A$19:$C$21,3,FALSE);  B4  says  Bronze One  Column  Sequencing:  Choose  a  single  cell  in  field  and  theAND  Func▯on:  =AND(condi▯on  1,  condi▯on  2,  ...  condi▯on  250) • Grouping  with  2  fields All  condi▯ons  must  be  true • “Give  it  to  me  by  secondary  in  primary”  means  group  by Result  will  be  true  or  false • within  that  sequence  by  secondary • First  sort  secondary,  then  sort  primary OR  Func▯on:  =OR(condi▯on  1,  condi▯on  2,  ...  condi▯on  250) • Primary  has  to  be  non-­‐unique • Only  one  condi▯on  must  be  true • No  more  than  ter▯ary  sor▯ng • Result  will  be  true  or  false • i.e.  NEW  customers  who  choose  PLATINUM  OR  GOLD Subtotals • =IF(AND(C4=”New”,  OR(B4=”Pla▯num”,  B4=”Gold”)),T,F) • Func▯ons  can  be:  SUM,  AVERAGE,  MAX,  MIN,  COUNT • Are  always  done  for  a  group  to  summarize  its  sta▯s▯cs,  but  you  must  always   Condi▯onal  Forma▯ng sequence/sort  by  groups  first • Finding  Duplicates  through  Highlight  Cells  Rules:   • When  you  insert  a  new  subtotal,  it  does  not  replace  any  preHome  -­‐-­‐>  Condi▯onal  Forma▯ng • Subtotals  cannot  be  inserted  in  an  excel  table   • New  Rule  Based  on  a  Formula:   • Data  >  Subtotal  >  At  each  change  in    >  Use  func▯on  >  Add  subtotal  to  henever  =J5
More Less

Related notes for BUS 150

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit