Инфознайка
Главная

Информация вокруг нас

Виды информации
Измерения информации
Алфавитный подход
Содержательный подход
Файловая система
Кодирование графики
Кодирование звука
Скорость передачи
Электронная таблица Excel
Графы
Система счисления
Кодирование информации
Логика
Адресация в Интернете
Поиск в Интернете
Алгоритмы
Кумир
Массивы

Тема: Электронная таблица MS Excel

Коротко о главном

·    адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, C15

·    формулы в электронных таблицах начинаются знаком = («равно»)

·    знаки +, –, *, / и ^ в формулах означают соответственно сложение, вычитание, умножение, деление и возведение в степень

·    запись B2:C4 означает диапазон, то есть, все ячейки внутри прямоугольника, ограниченного ячейками B2 и C4:

·    например, по формуле =СУММ(B2:C4) вычисляется сумма значений ячеек B2, B3, B4, C2, C3 и C4

·    в заданиях ЕГЭ могут использоваться стандартные функции СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение),  МАКС (максимальное значение)

·    функция СРЗНАЧ при вычислении среднего арифметического не учитывает пустые ячейки и ячейки, заполненные текстом; например, после ввода формулы в C2 появится значение 2 (ячейка А2 – пустая):

функция СЧЕТ(A1:B2) в этом случае выдаст значение 3 (а не 4).

·    адреса ячеек (или ссылки на ячейки) бывают относительные, абсолютные и смешанные, вся разница между ними проявляется при копировании формулы в другую ячейку:

o   в абсолютных адресах перед именем столбца и перед номером строки ставится знак доллара $, такие адреса не изменяются при копировании; вот что будет, если формулу =$B$2+$C$3 скопировать из D5 во все соседние ячейки

знак $ как бы «фиксирует» значение: в абсолютных адресах и имя столбца, и номер строки зафиксированы

o    в относительных адресах знаков доллара нет, такие адреса  при копировании изменяются: номер столбца (строки) изменяется на столько, на сколько отличается номер столбца (строки), где оказалась скопированная формула, от номера столбца (строки) исходной ячейки; вот что будет, если формулу =B2+C3 (в ней оба адреса – относительные) скопировать из D5 во все соседние ячейки:

o   в смешанных адресах часть адреса (строка или столбец) – абсолютная, она «зафиксирована» знаком $, а вторая часть – относительная; относительная часть изменится при копировании так же, как и для относительной ссылки:

Пример задания:                                   

В ячейке B4 электронной таблицы записана формула = $C3*2. Какой вид приобретет формула, после того как ячейку B4 скопируют в ячейку B6? Примечание: знак $ используется для обозначения абсолютной адресации.

1)  =$C5*4             2) =$C5*2                        3) =$C3*4                  4)  =$C3*2

Решение:

1) ссылка $C3 – это смешанная ссылка, в которой «заблокирован» столбец C, а строка 3 – это относительный адрес;

2) после того, как ячейку B4 скопировали в B6, номер строки увеличился на 2, поэтому и в ссылке $C3 номер строки (относительная часть) также увеличится на 2, ссылка превратится в $C5

3) константы при копировании формул не меняются, поэтому получится =$C5*2

4) таким образом, правильный ответ – 2.

 

Возможные ловушки и проблемы:

·    если ошибочно посчитать, что знак $ защищает от изменений всю ссылку, получим неверный ответ 4

Еще пример задания:

В электронной таблице значение формулы =СУММ(B1:B2) равно 5. Чему равно значение ячейки B3, если значение формулы =СРЗНАЧ(B1:B3) равно 3?

1)  8                          2) 2                                     3) 3                               4)  4

Решение:

1)      функция СУММ(B1:B2) считает сумму значений ячеек B1 и B2, поэтому B1 + B2 = 5

2)      функция СРЗНАЧ(B1:B3) считает среднее арифметическое диапазона B1:B3

3)      строго говоря, такие задачи некорректны, потому что

а)      функция СРЗНАЧ учитывает только числовые данные (числа или формулы, при вычислении которых получается число), то есть возможны варианты:

СРЗНАЧ(B1:B3)=СУММ(B1:B3), если есть только одна числовая ячейка

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/2, если есть две числовых ячейки

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/3, если все три ячейки – числовые

б)      в условии не задано, сколько числовых ячеек в диапазоне B1:B3

4)      в такой ситуации логичнее всего считать, что все три ячейки содержат числовые данные (во всех известных автору задачах такого типа используется именно это допущение)

5)      итак, в диапазон B1:B3 входят три ячейки; предполагаем, что все они содержат числовые данные, тогда  среднее арифметическое – это сумма их значений, деленная на 3; таким образом B1 + B2 + B3 = 3 · 3 = 9

6)      поскольку B1 + B2 = 5, сразу получаем B3 = 9 – 5 = 4

7)      таким образом, правильный ответ – 4.

 

Возможные ловушки и проблемы:

·    чтоб сбить угадывание, среди ответов приведены сумма исходных данных (8) и их разность (2) , это неверные ответы

Еще пример задания:

Дан фрагмент электронной таблицы:

 

А

В

С

1

1

2

 

2

2

6

=СЧЁТ(A1:B2)

3

 

 

=СРЗНАЧ(A1:C2)

Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1)

 1) –2                       2) –1                            3) 0                              4) +1

Решение:

1)      это задача на знание особенностей функций СЧЕТ и СРЗНАЧ, которые не учитывают пустые ячейки

2)      после ввода формул в С2 окажется количество непустых ячеек диапазона А1:В2, равное 4

3)      в С3 будет выведено среднее значение диапазона А1:С2 равное

         (1+2+2+6+4)/5 = 3

4)      после перемещения (не копирования!) содержимого ячейки В2 в В3 ячейка В2 окажется пустой, поэтому в С2 выводится число 3 – количество непустых ячеек диапазона А1:В2

5)      в С3 будет выведено среднее значение диапазона А1:С2 равное

         (1+2+2+3)/4 = 2,

то есть значение С3 уменьшится на 1

6)      таким образом, правильный ответ – 2.

Возможные ловушки и проблемы:

·    нужно помнить, что при перемещении содержимого ячейки в другое место она становится пустой

·    нужно помнить, что функции СЧЕТ и СРЗНАЧ не учитывают пустые ячейки

Еще пример задания:

В цехе трудятся рабочие трех специальностей – токари (Т), слесари (С) и фрезеровщики (Ф). Каждый рабочий имеет разряд не меньший второго и не больший пятого. На диаграмме I отражено количество рабочих с различными разрядами, а на диаграмме II – распределение рабочих по специальностям. Каждый рабочий имеет только одну специальность и один разряд.

Имеются четыре утверждения:

          А) Все рабочие третьего разряда могут быть токарями

          Б) Все рабочие третьего разряда могут быть фрезеровщиками

          В) Все слесари могут быть пятого разряда

          Г) Все токари могут быть четвертого разряда

Какое из этих утверждений следует из анализа обеих диаграмм?

     1) А                       2) Б                              3) В                              4)     Г

Решение:

1)      в условии даны столбчатая диаграмма, по которой можно определить все числовые данные, и круговая диаграмма, по которой можно определить только доли отдельных составляющих в общей сумме

2)      по данным столбчатой диаграммы определим, сколько рабочих имеют 2-ой, 3-й, 4-й и 5-й разряды:

2-ой разряд:     25 чел.                 3-й разряд:        40 чел.

4-й разряд:       20 чел.                 5-й разряд:        15 чел.

3)      сложив все эти числа, определим, что всего в цехе 25 + 40 + 20 + 15 = 100 рабочих

4)      по круговой диаграмме видим, что половина из них – токари (значит их 50 человек), четверть – слесари (25 чел.) и еще четверть – фрезеровщики (25 чел.)

5)      теперь последовательно рассмотрим все утверждения-ответы:

А: Все рабочие третьего разряда (их 40 чел.) МОГУТ быть токарями, потому в цеху 50 токарей

Б: Все рабочие третьего разряда (их 40 чел.) НЕ могут быть фрезеровщиками, потому в цеху всего 25 фрезеровщиков

В: Все слесари (их 25 чел.)  НЕ  могут быть 5-ого разряда, потому в цеху только 15 рабочих имеют 5-й разряд

Г: Все токари (их 50 чел.)  НЕ  могут быть четвертого разряда, потому в цеху только 20 рабочих имеют 4-й разряд

6)      таким образом, правильный ответ – 1.

Еще пример задания:

На диаграмме показано количество призеров олимпиады по информатике (И), математике (М), физике (Ф) в трех городах России.

Какая из диаграмм правильно отражает соотношение общего числа призеров по каждому предмету для всех городов вместе?

Решение:

1)      в условии дана столбчатая диаграмма, по которой можно определить все числовые данные

2)      в ответах все диаграммы – круговые, по ним можно определить только доли отдельных составляющих в общей сумме

3)      при анализе диаграмм-ответов нужно «вылавливать» их характерные черты (половину или четверть круга, одинаковые значения, соотношения между секторами), именно они позволяют определить верный ответ

4)      попробуем сначала проанализировать круговые диаграммы (ответы)

·      наибольшая доля (на всех диаграммах) приходится на математику

·      самый меньший сектор на диаграммах 1-3 – информатика, а на 4-ой – физика

·      на 1-ой диаграмме информатика составляет четверть от общей суммы

·      на 3-ей диаграмме математика составляет половину от общей суммы

5)       теперь снимем данные с заданной столбчатой диаграммы и подсчитаем сумму призеров по каждому предмету:

 

 

М

Ф

И

Всего

Екатеринбург

180

120

120

 

Томск

160

140

60

 

Новосибирск

180

120

120

 

Всего

 =SUM(ABOVE) 520

 =SUM(ABOVE) 380

 =SUM(ABOVE) 300

 =SUM(LEFT) 1200

 

6)      по условию для построения круговой диаграммы использовалась нижняя строка таблицы

7)      общее количество призеров ­ – 1200, информатика составляет ровно четверть от этого числа

8)      таким образом, правильный ответ – 1.

Еще пример задания:

У исполнителя Калькулятор две команды:

1. прибавь 1

2. умножь на 2.

Первая из них увеличивает число на экране на 1, вторая – удваивает его.

Программа для Калькулятора – это последовательность команд. Сколько различных чисел можно получить  из числа 2 с помощью программы, которая содержит ровно 4 команд?

Решение:

1)      будем строить дерево решений следующим образом: выясним, какое число можно получить из начального значения 1 за 1 шаг:

2)      теперь посмотрим, что удается получить за 2 шага:

3)      делаем 3-й шаг, получаем 8 разных чисел:

 

4)      на 4-ом шаге рассматриваем все возможные программы из 4-х команд, получаем числа

6, 10,    9, 16,    8, 14,    13, 24,   7, 12,    11, 20,    10, 18,   17, 32

5)      здесь всего 16 чисел, но одно из них (10) повторяется 2 раза, а остальные встречаются по 1 разу, поэтому получаем 15 различных чисел

6)      Ответ: 15.

Проверочные задания

  1. В ячейке B1 записана формула =2*$A1. Какой вид приобретет формула, после того как ячейку B1 скопируют в ячейку C2?

      =2*$B1
      =2*$A2
      =3*$A2
      =3*$B2Н


  2. В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно (-2). Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно 5?

     1
    -1
    -3
      7

  3. Дан фрагмент электронной таблицы:

     

    А

    В

    С

    D

    1

    1

    2

    3

     

    2

    4

    5

    6

     

    3

    7

    8

    9

     

     

    В ячейку D1 введена формула =$А$1*В1+С2, а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?

    10
    14
    16
    24.

  4. В регионах А,  B и С вел наблюдение за атмосферными осадками. На диаграмме 1 показаны суммарные ежеквартальные уровни осадков, а на диаграмме 2 – годовое распределение осадков по регионам. Какое из этих утверждений ПРОТИВОРЕЧИТ информации, показанной на диаграммах?

    Диаграмма 1

    Диаграмма 2

    Какое из этих утверждений ПРОТИВОРЕЧИТ информации, показанной на диаграммах?

    Во третьем квартале осадков в регионе А выпало меньше, чем в регионе В.
    Во втором и третьем кварталах в регионе A осадков не выпадало.
    Во втором и третьем кварталах в регионе C осадков не выпадало.
    В регионе А во втором квартале выпало больше осадков, чем в третьем.

      

  5.  На диаграмме показаны объемы выпуска продукции трех видов (А, Б и В) за каждый месяц первого квартала:

    Какая из диаграмм правильно отражает соотношение объемов выпуска этих видов продукции за весь квартал?

    1
    2
     3
    4