Ссылки на ячейки из других файлов

Чтобы использовать ссылку на ячейку какого-то рабочего листа из другого файла, ссылку на прописать так: [имя файла]имя листа! адрес ячейки.

Например, пусть в файле vas1.xls хранятся данные:

рис.15

Создадим файл vas2.xls, у которого на листе1 в ячейке A1 хранится число, а в ячейку B2 занесем формулу со значением формулы в ячейке D4 листа 2 файла vas1.xls. Получим: (см. рис.16 строку ввода)

рис.16

Иногда на рабочем листе удобно отображать сами формулы, а не результаты вычислений: Сервис ® Параметры ® Вид ® Установить флажок рядом с названием "Формулы".

Для возврата снять флажок.

Чтобы понять работу с электронной таблицей, рассмотрим примеры:

ПРИМЕР №6

Дано:

рис.17

Вычислить:

налог: пусть он равен 13% от зарплаты и премии

на руки: Зарплата + Премия – Налог

суммарное, среднее, наибольшее и наименьшее значения зарплаты, премии, налога, итога.

Решение:

рис.18

1. В ячейку E2 заносим формулу для расчета налога Голубеву (см. рис.) ® Enter

2. Копируем эту формулу в блок ячеек E3:E7:

2.1. выделяем ячейку E2 с формулой

2.2. курсор мыши подводим в правый нижний угол так, чтобы он принял вид черного крестика (маркер заполнения)

2.3. нажимаем левую кнопку мыши и, не отпуская ее, тянем вдоль блока до ячейки E7

2.4. если теперь щелкнуть в любую ячейку из блока E3:E7, в строке ввода можно увидеть верную формулу расчета налога для человека, чья фамилия указана в соответствующей строке
При копировании вниз ссылки C2 и D2 менялись. Такие ссылки являются относительными.

3. В ячейку F2 заносим формулу для расчета итога (в рублях) для Голубева ® аналогично копируем эту формулу в блок ячеек F3:F7 (И вновь относительные ссылки изменились: C2 перешла в C3; D2 – в D3, E2 в E3, C3 в C4, D3 в D4, E3 в E4, …)

4. В ячейку G2 занесем формулу для расчета Итога в долларах, используя курс доллара, заданный в ячейке B12:

4.1. формула в ячейке G2 первоначально выглядит так: =F2/B12

4.2. если эту формулу скопировать вниз, то получим:
=F3/B13
=F4/B14
=F5/B15
и так далее. Но в ячейках B13, B14, B15 нет никаких данных. А нам надо, чтобы при копировании получилось так:
=F3/B12
=F4/B12
=F5/B12
и так далее. Т.е. номер строки в ссылке B12 надо закрепить. Это делается с помощью значка $. В итоге, формула в ячейке G2 будет выглядеть так: = F2/B$12
Как уже говорилось, с сылка, в которой с помощью значка $ закреплена либо буква столбца, либо номер строки, называется смешанной. При копировании формулы в одну сторону смешанная ссылка не меняется, в другую – меняется. В нашем случае при копировании вниз формулы =F2/B$12 ссылка B$12 не меняется.Если бы мы копировали формулу вправо, то ссылка B$12 менялась.

5. В ячейке C8 надо посчитать суммарное значение зарплаты всех сотрудников. Для этого:

5.1. выделяем ячейку C8

5.2. на панели инструментов Стандартная щелкаем по кнопке S – Автосумма

5.3. в ячейке C8 появляется формула =Сумм(C2:C7)

5.4. предложенный автоматически блок ячеек нас устраивает ® щелкаем Enter

5.5. копируем полученную в ячейке C8 формулу в блок D8:G8.

6. В ячейке C9 требуется сосчитать среднее значение из чисел, расположенных в блоке C2:C7. Для этого:

6.1. вызываем мастер функций: меню Вставка ® Функция ® категория Статистические ® функция СрЗнач

6.2. автоматически вводится аргумент функции – блок ячеек C2:C8.

6.3. но аргументом должен быть блок C2:C7. Поэтому вносим исправления ® OK

6.4. копируем полученную в ячейке C9 формулу вправо

7. В ячейку С10 заносим статистическую функцию Макс – максимальное число из блока C2:C7

8. В ячейку С11 заносим статистическую функцию Мин – минимальное число из блока C2:C7

9. Выделяем ячейки C10:C11 и копируем блок вправо

10. Теперь наведем красоту в нашей таблице:

10.1. Зададим денежный формат в рублях для значений из блоков C2:F11; B12

10.1.1. выделим блоки ячеек, в которых хранятся деньги в рублях: C2:F11;B12

10.1.2. меню Формат ® Ячейки ® вкладка Число ® формат Денежный ® 2 знака после запятой ® Обозначение "р" ® OK

10.2. Зададим денежный формат в долларах для значений из блока G2:G11

10.2.1. выделим блок ячеек, где деньги в долларах: G2:G11

10.2.2. меню Формат ® Ячейки ® вкладка Число ® формат Денежный ® 2 знака после запятой ® Обозначение "$" ® OK

10.3. Отразим границы ячеек в таблице (если границы не отразить, при печати на бумаге их не будет):

10.3.1. выделяем таблицу

10.3.2. меню Формат ® Ячейки ® вкладка Границы ® задаем тип границ ® кнопка Внешние ® кнопка Внутренние ® OK

С помощью этого примера мы достаточно подробно познакомились с тем, как заносятся формулы в электронные таблицы; что такое относительные и смешанные ссылки; как вызываются и как работают некоторые функции.

Теперь познакомимся с тем, как строятся диаграммы в ЭТ Excel:

ПОСТРОЕНИЕ ДИАГРАММ

ПРИМЕР №7

Дано: таблица с данными среднего роста девочек и мальчиков в разных классах школы:

рис.19

Нарисовать сравнительную диаграмму среднего роста девочек и мальчиков в каждом классе.

Итак, вдоль оси X необходимо отложить Класс, вдоль оси Y – средний рост девочек и средний рост мальчиков (т.е. рядов данных будет два).

1. На первом этапе надо: либо поставить маркер ячейки на любую ячейку внутри таблицы с данными, либо выделить ряды данных для будущей диаграммы (графика), т.е. те данные, которые отразятся по оси Y. (В нашем случае это ряд Средний рост девочек B2:B12 и Средний рост мальчиков D2:D12.). (далее рассматриваем 2-рой вариант, т.е. ряды данных предварительно выделены)

2. Вызываем мастер диаграмм:

ü на панели стандартная щелкаем по кнопке Мастер диаграмм ® в первом диалоговом окне выбираем тип диаграммы Гистограмма ® кнопка Далее

3. Во втором диалоговом окне на вкладке Диапазон данных соглашаемся с выбором: "В столбцах" ® выбираем вкладку Ряд

ü Даем имена рядам данных:

Ú выделяем ряд и в поле Имя даем ряду имя: Ряд1 назовем "Рост девочек"; Ряд2 назовем "Рост мальчиков" (эти названия отразятся в легенде к диаграмме)

ü Задаем диапазон для Подписей по оси X:

Ú щелкаем в поле Подписи по оси X ® выделяем диапазон ячеек A2:A12 ® щелкаем по кнопке Далее

4. В третьем диалоговом окне задаются заголовки, различные подписи, оси, координатные сетки, легенды, таблицы данных. Т.е. это окно служит для придания внешнего вида будущей диаграммы ® кнопка Далее

5. В четвертом окне выбираем, где будет располагаться диаграмма: на отдельном листе или на том же, где и таблица ® кнопка Готово

Внешний вид диаграммы может оказаться не совсем тот, какой ожидался. Ничего страшного! Просто надо помнить, что каждый элемент диаграммы имеет свое контекстное меню, вызываемое щелчком правой кнопкой мыши по элементу.

Итак,

ü щелкнем по диаграмме

ü с помощью маркеров вокруг диаграммы изменим ее размеры по своему усмотрению

ü щелкнем правой кнопкой мыши по элементу, например заголовок по оси Y, который для данной диаграммы, якобы, имеет крупный размер ® из контекстного меню выбираем Формат ® Шрифт ® меняем размер шрифта ® дополнительно можно задать Выравнивание "на 90 градусов" ® OK

ü другие не устраивающие элементы диаграммы меняем аналогичным образом

рис.20

Теперь познакомимся с одной важной функцией, с которой не редко приходится иметь дело тем, кто работает с ЭТ. Это функция Если()

ФУНКЦИЯ ЕСЛИ()

Синтаксис функции такой: