Excel: Абсолютные и относительные ссылки
От fin-analis / 17.02.2019 17.02.2019
Сегодняшняя статья для новичков. Помнится, не так давно на глаза попалась «экселевская» шутка, про то, что если ты не знаешь, чем $A$1 отличается от A1, то нам не о чем с тобой разговаривать. Но нам всегда есть о чём поговорить с нашими читателями и тем более о таком базовом вопросе, как абсолютные и относительные ссылки в Excel.
Итак, чем же эти ссылки отличаются одна от другой? Относительная ссылка в формуле вида A1 – это адресация на ячейку, которая удалена от ячейки с формулой на определённое расстояние. Поэтому, при протягивании формулы в сторону, она будет ссылаться на то же самое расстояние, а не на определённую ячейку. Например, если формулу протянуть вправо на одну ячейку, то она уже будет ссылаться не на A1, а на B1. Если вместо этого мы протянем формулу вниз, то ссылка окажется не на A1, а на A2. Таким образом, ссылки собьются и формула выдаст неправильный результат.
Если же мы вместо ссылки на ячейку A1, проставим $A$1, то куда бы мы не сдвигали формулу и на какое бы количество ячеек, формула всё равно будет ссылаться именно на ячейку A1.
Сделать такую ссылку можно во время выбора ячейки в формуле, нажав клавишу F4.
Кроме жёсткой привязки к определённой ячейке может быть смешанная ссылка вида $A1 (фиксация столбца) или A$1 (фиксация строки). Это также достигается нажатием клавиши F4 два или три раза, пока знак $ не появится в нужном месте. Что это значит? При фиксации столбца $A1 во время сдвига формулы в сторону не будет происходить изменений. Ссылка будет изменяться только при движении по столбцу, то есть при протягивании формулы вверх или вниз. Соответственно, с учётом того, что ссылка идёт на ячейку A1, то при протягивании вверх, будет выдаваться ошибка #ССЫЛКА! (потому что выше A1 ячеек нет), а при протягивании формулы вниз, ссылка на ячейку примет вид $A2.
При создании ссылки вида A$1, будет зафиксирована строка, в результате, при протягивании формулы вверх или вниз, изменений в адресации ячейки не будет. При протягивании формулы влево будет выдаваться ошибка #ССЫЛКА! (слева от A1 нет ячеек), при протягивании формулы вправо, ссылка примет вид B$1 и выдаст значение из этой ячейки.
Наглядным примером использования разной адресации ячеек служит таблица Пифагора. Если кто-то не помнит – это таблица умножения в шахматном виде, где перекрёстно перемножаются цифры из верхней строки (от 1 до 10) и из левого столбца (от 1 до 10).
Соответственно, мы получаем значения от 1 до 100. Если мы в ячейку B2 введём формулу =A2*B1 и протянем строки и столбцы, то формула будет считать неправильно, или, верней сказать, выдаст не те значения, которые нам нужны, так как, если вы помните, формула будет перемножать данные на определённом расстоянии от себя.
Если мы поставим формулу с жёсткой фиксацией (абсолютная адресация), то также не получим нужный результат. Формула просто скопирует одно и то же значение.
Лишь вводя смешанную адресацию, фиксируя левый столбец и верхнюю строку, мы получим правильный результат.
Наглядно ещё раз этот пример нам поможет проиллюстрировать короткое видео.
Таблица Пифагора» src=»https://www.youtube.com/embed/5j-OpeIKlXo?feature=oembed» frameborder=»0″ allow=»accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture» allowfullscreen=»»> Если Вы новичок, то отличным подспорьем для расчётов в excel станет наш шаблон Финансовых вычислений https://fin-analis.ru/finexcel/
Эксель. Excel. Курс для новичков «Электронные таблицы». Часть 2. Тренировка. Таблица умножения. Видео.
Видео
Код youtube для установки на сайт или блог
Microsoft Excel.
Майкрософт Эксель. Курс для новичков «Электронные таблицы». За час от новичка до профи по работе с электронными таблицами. Часть 2. Тренировка. Таблица умножения и пр.
Красавица Светлана о себе рассказывает немного, но активно помогает мне, участвуя в альфа тестировании курса.
Важно!
Это 2-я часть видео курса.
Предварительно следует просмотреть 1-ю часть.
Продолжение видео курса следует.
А вот в текстовом виде курс полностью готов, включая отлаженные примеры с подробными пояснениями. См здесь: https://wpvi.ru/pages/soft/excel/
ТаймЛайн
0:18 Напоминалка: что у нас было в первой части и на чем мы остановились.
0:45 Создаем файл study2023.xls в папке d:\mso\excel\.
3:30 Светлана — альфа тестер.
5:00 Переименование листов в электронной книге.
5:55 Упражнение 1. Изменение высоты столбца и ширины строки.
7:14 Упражнение 2. Ввод информации в ячейку.
8:04 Упражнение 3. Изменение свойств ячеек, объединение ячеек.
Microsoft Excel. Майкрософт Эксель. Курс для новичков. Тексты программ для скачивания.
… или …
Посмотреть это видео на сайте youtube через домен www.youtube.com:
https://www.youtube.com/watch?v=prDU74DjeZA
… или …
Посмотреть это видео на сайте youtube через домен youtu.be:
https://youtu.be/watch?v=prDU74DjeZA
… или …
Посмотреть список всех видео на сайте www.youtube.com:
https://www.youtube.com/channel/UCrkp4mgrDi2Pnux9hLT_1pQ/videos
.
.. или …
список видео на сайте wpvi.ru ➳
… или …
карта сайта wpvi.ru (список всех страниц) ➳
… или …
перейти на главную страницу сайта ➳
| #!/usr/bin/env python3 | |
| # Эта программа берет число N из командной строки | |
| # и создает таблицу умножения N на N в электронной таблице Excel. | |
| система импорта, openpyxl | |
из openpyxl. styles импортировать шрифт | |
| из openpyxl.utils import get_column_letter, column_index_from_string | |
| # По умолчанию n равно 6 | |
| , если len(sys.argv) > 1: | |
| n = интервал (sys.argv[1]) | |
| иначе: | |
| n = 6 | |
| # Откройте новую книгу и переключите активный лист | |
| wb = openpyxl.Workbook() | |
 active | |
| # Распечатать заголовки | |
| жирный шрифт = шрифт (полужирный = истина) | |
| для i в диапазоне (2, n+2): | |
| множитель | = i — 1 |
| левая ячейка = ‘A’ + строка (я) | |
| листов[leftCell] = множитель | |
| лист[leftCell].font = полужирный шрифт | |
| rightCell = get_column_letter(i) + ‘1’ | |
| лист[rightCell] = множитель | |
лист [правая ячейка]. шрифт = полужирный шрифт | |
| # Распечатать таблицу умножения | |
| для i в диапазоне (2, n+2): | |
| левый множитель = i — 1 | |
| для j в диапазоне (2, n+2): | |
| правый множитель = j — 1 | |
| ячейка = get_column_letter(j) + str(i) | |
| лист [ячейка] = левый множитель * правый множитель | |
| # Заморозить заголовки | |
лист. freeze_panes = ‘B2’ | |
| # Сохранить в новый файл Excel в текущем рабочем каталоге | |
| wb.save(‘Таблица умножения.xlsx’) |
Учебное пособие по таблице умножения в Excel – Блог Contextures
Вы помните числовые сетки со школьных времен, когда вы заполняли ответы на вопросы по умножению? Сколько будет 4 умножить на 8? Или 9 умножить на 7? Если вам нужно освежить знания или вы помогаете кому-то их выучить, у меня есть для вас практическая рабочая тетрадь Excel для таблицы умножения.
Учеба дома
Эти таблицы умножения снова стали преследовать меня в этом году, когда школы закрылись из-за пандемии.
Студенты работали из дома, поэтому я проводил встречи с внуком в Zoom и помогал с его домашним заданием по математике. Мы проделали большую работу над этими таблицами умножения!
Рабочая тетрадь Excel для умножения
Учебный год закончился, но, возможно, мой внук хочет практиковать таблицу умножения все лето! (или нет)
На всякий случай я сделал учебник по таблице умножения в Excel, и вы тоже можете скачать копию.
В конце этой записи в блоге есть ссылка, а также несколько видеороликов с советами по запоминанию таблицы умножения.
Практика по таблице умножения – одна строка
В рабочей тетради Excel есть 2 практических листа.
В практическом листе OneRow выберите число от 1 до 12 из раскрывающегося списка.
Затем заполните сетку ответами на умножение или вашим лучшим предположением!
Чтобы проверить свои ответы, выберите Да в другом раскрывающемся списке.
Неправильные ответы выделены оранжевым цветом. О, пора сделать еще одну практику!
Сетка таблицы умножения
На другом тренировочном листе, названном Таблица , вы выберете диапазон чисел, которые хотите попрактиковать.
Строки создаются для того диапазона чисел, где вы можете заполнить ответы на умножение.
Затем выберите «Да» в раскрывающемся списке «Проверить», чтобы выделить все ошибки.
Выделение неправильных ответов
Вот формула правила условного форматирования для выделения неправильных ответов на листе таблицы.
=И(C9<>””, $M$4=»Да», C9<>$B9*C$8)
Ячейка E9 имеет оранжевую заливку, потому что 3×3=9, и ответ был введен как 8.
ПРИМЕЧАНИЕ : В ячейке M4 есть раскрывающийся список, где вы выбираете «Да», чтобы проверить свои ответы.
Добавить границы ячеек в сетке
Чтобы таблица не выглядела загроможденной, границы ячеек отображаются только для выбранного диапазона чисел. Это делается с помощью условного форматирования по следующей формуле:
=$B9<>””
В строке 12 нет границ сетки, так как ячейка B12 пуста.
Последовательность номеров
В столбце B показаны числа множителей, основанные на выбранном вами диапазоне. В ячейке B9 формула ссылается на ячейку D4, где вы вводите первое число в диапазоне
=D4
Чтобы создать последовательность, эта формула вводится в ячейку B10 и копируется в ячейку B20: ЕСЛИ(ИЛИ(B9=»»,$D$4>$F$4)»,»,ЕСЛИ(B9=$F$4″,»,SUM(B9,1)))
Результатом является пустая строка («») если:
- ячейка выше имеет пустую строку
- ячейка выше имеет номер, равный конечному номеру в ячейке F4
- конечный номер в F4 больше, чем начальный номер в D4
В противном случае результатом является указанное выше число плюс 1
В этом примере диапазон составляет от 2 до 6.
Защита листа
В примере файла листы OneRow и Table защищены без пароля
На этих листах
- ячейки ввода данных разблокированы
- все остальные ячейки заблокированы
- можно выбрать только разблокированные ячейки
Это помогает защитить формулы рабочего листа, чтобы они не были случайно удалены.
Чтобы снять защиту с листа:
- На ленте Excel щелкните вкладку Обзор
- В группе Защита щелкните Снять защиту с листа
Получить образец файла
Чтобы получить рабочую тетрадь Excel для работы с таблицей умножения, перейдите на страницу с образцами файлов на моем сайте Contextures.
В разделе «Функции» найдите FN0065 — Таблицы умножения Excel.
Заархивированный файл Excel имеет формат xlsx и не содержит макросов.
Советы экспертов по видео
Если вы также помогаете кому-то выучить таблицу умножения, вам могут помочь советы и рекомендации из этих двух видео.