Как называются координаты ячейки в таблицах excel
Как с помощью VBA получить значение с ячейки таблицы MS Word в переменую?
Как с помощью VBA получить значение с ячейки таблицы MS Word в переменую. Range(x,y).
Можно ли получить доступ к содержимому определенной ячейки стека по адресу?
Здравствуйте, скажите пожалуйста, могу вывести по определенному адресу из стека, при этом не теряя.
Получить значение последней записи ячейки определённого столбца VBA Excel
Приветствую форумчани подскажите кто может как получить последнюю записть определёного столбца q.
Можно ли с помощью формы в одной книге Excel вносить данные в ячейки двух книг?
Можно ли с помощью формы в одной книге Excel вносить данные в ячейки двух книг?
Это понятно, просто я пользуюсь проц AddPicture (name_of_File,x,y,widht,height). И этот рисунок надо вставить напр в ячейку (2,5).
Как я понимаю, вопрос должен звучать так: 'Как привязать объект к ячейке, т.е. как получить координаты ячейки в пикселях или других физ. величинах, а затем их использовать'?
На этот вопрос я ответа не знаю: сложение ширин столбцов (так же как и высот строк), например, требуемого результата не даёт.
Поступать нужно след. образом:
1.Определить адрес ячейки Cells(k, m).Address.
2.Пересчитать в циклах суммы Columns(i).Width и Rows(j).Height
от i = 1 до i = k - 1 (аналогично For j = 1 To m - 1)
3.X = СУММАi, Y = СУММАj,
ширина и высота картинки - по размерам ячейки, если это необходимо:
Picture.Width = Columns(m).Width, Picture.Height = Rows(k).Height
ThisWorkbook.Sheets('Лист1').Shapes.AddPicture name_of_File, _
True, True, X, Y, cOLUMNS(nSTOLB).wIDTH, rOWS(nSTROK).hEIGHT
Здесь (Columns(Nstolb).Width, Rows(Nstrok).Height) размер рисунка
устанавливается равным размеру ячейки и соответственно, центрируется.
Что-то не устраивает?
Не устраивает в том плане, что я рисунок вывожу в несколько объединенных ячейках (образуют квадрат), а имейдж - прямоугольник, и происходит искажения, а этого не должно быть.
Вот так всё работает. Правда. длинновато получилось
Уважаемый, почему молчим? Погряз, решая задачу оптимизации?
Молчание удивительно, поскольку то, что я второпях набросал,
содержит несколько ошибок, что, конечно работоспособности коду не добавляет (типы объявленных переменных Long вместо Double,
использование переменной 'i' вместо 'j' в цикле
'For j = Nstolb To Nstolb1').
Держи протестированный вариант. Определение границ объединённой
ячейки теперь возложено на строковые функции, дабы ускорить
выполнение программы.
Убрано всё 'лишнее'. Теперь код короткий, прозрачный и быстрый.
Как программно определить фон определенной ячейки Excel?
Как программно определить фон определенной ячейки Excel. Заранее спасибо.
Как получить координаты определенной вкладки в QTabBar?
Я хочу получить QRect определенной вкладки в QTabBar, то есть её координаты и ширину с высотой.
Как извлечь текст примечания из определенной ячейки листа Excel?
Приложение работает с документом Excel. Как извлечь текст примечания из определенной ячейки листа.
Как получить при нажатии координаты определённой программы?
Нажав мышкой где-то на другой windows программе, получить той программы x и y, не самого windows, а.
Для пользователей программы Microsoft Excel не секрет, что данные в этом табличном процессоре размещаются в отдельных ячейках. Для того, чтобы пользователь мог обращаться к этим данным, каждому элементу листа присвоен адрес. Давайте выясним, по какому принципу нумеруются объекты в Экселе и можно ли изменить данную нумерацию.
Виды нумерации в Microsoft Excel
Прежде всего, следует сказать о том, что в Экселе имеется возможность переключения между двумя видами нумерации. Адрес элементов при использовании первого варианта, который установлен по умолчанию, имеет вид A1. Второй вариант представлен следующей формой — R1C1. Для его использования требуется произвести переключение в настройках. Кроме того, пользователь может собственноручно пронумеровать ячейки, воспользовавшись сразу несколькими вариантами. Давайте рассмотрим все эти возможности подробнее.
Способ 1: переключение режима нумерации
Прежде всего, давайте рассмотрим возможность переключения режима нумерации. Как уже говорилось ранее, адрес ячеек по умолчанию задается по типу A1. То есть, столбцы обозначаются буквами латинского алфавита, а строчки — арабскими цифрами. Переключение в режим R1C1 предполагает вариант, при котором цифрами задаются не только координаты строк, но и столбцов. Давайте разберемся, как произвести такое переключение.
Для того, чтобы вернуть обозначение координат по умолчанию, нужно провести ту же самую процедуру, только на этот раз снять флажок с пункта «Стиль ссылок R1C1».
Урок: Почему в Экселе вместо букв цифры
Способ 2: маркер заполнения
Кроме того, пользователь сам может пронумеровать строки или столбцы, в которых расположены ячейки, согласно своим потребностям. Эта пользовательская нумерация может использоваться для обозначения строчек или колонок таблицы, для передачи номера строчки встроенным функциям Excel и в других целях. Конечно, нумерацию можно произвести вручную, просто вбивая с клавиатуры нужные числа, но намного проще и быстрее выполнить данную процедуру, используя инструменты автозаполнения. Особенно это актуально при нумерации большого массива данных.
Взглянем, как при помощи маркера заполнения можно произвести автонумерацию элементов листа.
-
Ставим цифру «1» в ту ячейку, с которой планируем начинать нумерацию. Затем наводим курсор на правый нижний край указанного элемента. При этом он должен трансформироваться в черный крестик. Он носит название маркера заполнения. Зажимаем левую кнопку мышки и тащим курсор вниз или вправо, в зависимости от того, что именно нужно пронумеровать: строчки или столбцы.
Способ 3: прогрессия
Ещё одним способом, с помощью которого можно пронумеровать объекты в Экселе, является использование инструмента под названием «Прогрессия».
-
Как и в предыдущем способе, устанавливаем цифру «1» в первую ячейку, подлежащую нумерации. После этого просто выделяем данный элемент листа, кликнув по нему левой кнопкой мыши.
В блоке настроек «Тип» для наших целей нужно установить переключатель в позицию «Арифметическая». Впрочем, он и так по умолчанию расположен в данной позиции, поэтому нужно лишь проконтролировать его положение.
Блок настроек «Единицы» становится активным только при выборе типа «Даты». Так как мы выбрали тип «Арифметическая», нас вышеуказанный блок интересовать не будет.
В поле «Шаг» следует установить цифру «1». В поле «Предельное значение» ставим количество нумеруемых объектов.
После выполнения перечисленных действий жмем на кнопку «OK» внизу окошка «Прогрессия».
Если вы не желаете производить подсчет количества элементов листа, которые нужно пронумеровать, чтобы указать их в поле «Предельное значение» в окне «Прогрессия», то в этом случае нужно перед запуском указанного окна выделить весь диапазон, подлежащий нумерации.
После этого в окне «Прогрессия» выполняем все те же действия, которые были описаны выше, но на этот раз оставляем поле «Предельное значение» пустым.
Результат будет тот же: выделенные объекты окажутся пронумерованными.
Урок: Как сделать автозаполнение в Экселе
Способ 4: использование функции
Пронумеровать элементы листа, можно также прибегнув к использованию встроенных функций Excel. Например, для построчной нумерации можно применять оператор СТРОКА.
Функция СТРОКА относится к блоку операторов «Ссылки и массивы». Её основной задачей является возврат номера строчки листа Excel, на который будет установлена ссылка. То есть, если мы укажем в качестве аргумента этой функции любую ячейку в первой строке листа, то она выведет значение «1» в ту ячейку, где располагается сама. Если указать ссылку на элемент второй строчки, то оператор выведет цифру «2» и т.д. Синтаксис функции СТРОКА следующий:
Как видим, единственным аргументом данной функции является ссылка на ячейку, номер строки которой нужно вывести в указанный элемент листа.
Посмотрим, как работать с указанным оператором на практике.
-
Выделяем объект, который будет первым в нумерованном диапазоне. Щелкаем по значку «Вставить функцию», который размещен над рабочей областью листа Excel.
Но мы совершили нумерацию только строк, а для полноценного выполнения задачи присвоения адреса ячейки в виде номера внутри таблицы следует пронумеровать ещё и столбцы. Это также можно сделать при помощи встроенной функции Excel. Данный оператор ожидаемо имеет наименование «СТОЛБЕЦ».
Функция СТОЛБЕЦ также относится к категории операторов «Ссылки и массивы». Как нетрудно догадаться её задачей является выведение в указанный элемент листа номера столбца, на ячейку которого дается ссылка. Синтаксис этой функции практически идентичен предыдущему оператору:
Как видим, отличается только наименование самого оператора, а аргументом, как и в прошлый раз, остается ссылка на конкретный элемент листа.
Посмотрим, как выполнить поставленную задачу с помощью данного инструмента на практике.
-
Выделяем объект, которому будет соответствовать первый столбец обрабатываемого диапазона. Клацаем по пиктограмме «Вставить функцию».
Теперь все ячейки нашей условной таблицы имеют свою относительную нумерацию. Например, элемент, в котором на изображении ниже установлена цифра 5, имеет относительные пользовательские координаты (3;3), хотя абсолютный его адрес в контексте листа остаётся E9.
Урок: Мастер функций в Microsoft Excel
Способ 5: присвоение имени ячейке
В дополнение к вышеуказанным способам нужно отметить, что, несмотря на проведенное присвоения номеров столбцам и строкам определенного массива, наименования ячеек внутри него будут задаваться в соответствии с нумерацией листа в целом. Это можно увидеть в специальном поле имен при выделении элемента.
Для того, чтобы изменить имя, соответствующее координатам листа на то, которое мы задали с помощью относительных координат для нашего массива, достаточно выделить соответствующий элемент кликом левой кнопки мыши. Затем просто с клавиатуры в поле имени вбить то название, которое пользователь считает нужным. Это может быть любое слово. Но в нашем случае мы просто введем относительные координаты данного элемента. В нашем наименовании обозначим номер строки буквами «Стр», а номер столбца «Стол». Получаем наименование следующего типа: «Стол3Стр3». Вбиваем его в поле имен и жмем на клавишу Enter.
Теперь нашей ячейке присвоено наименование согласно её относительному адресу в составе массива. Таким же образом можно дать имена и другим элементам массива.
Урок: Как присвоить имя ячейке в Экселе
Как видим, существуют два вида встроенной нумерации в Экселе: A1 (по умолчанию) и R1C1 (включается в настройках). Данные виды адресации распространяются на весь лист в целом. Но кроме того, каждый пользователь может сделать свою пользовательскую нумерацию внутри таблицы или определенного массива данных. Существует несколько проверенных способов присвоения ячейкам пользовательских номеров: с помощью маркера заполнения, инструмента «Прогрессия» и специальных встроенных функций Excel. После того, как выставлена нумерация, можно на её основе присвоить имя конкретному элементу листа.
Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]
Как создать именованный диапазон?
Существуют три способа создания именованных диапазонов:
- путем ввода имени диапазона в поле Имя;
- путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
- путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.
Для создания имени диапазона с помощью поля Имя (рис. 1.1) выделите ячейку или диапазон ячеек, которым требуется присвоить имя, установите курсор в поле Имя, введите имя диапазона, и нажмите клавишу . На рис. 1.1 ячейке В3 присвоено имя Старт.
Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя
При нажатии в поле Имя на стрелку раскрывающегося списка появятся имена диапазонов, определенные в текущей книге (рис. 1.2). При выборе в поле Имя имени диапазона все ячейки, соответствующие этому диапазону, отмечаются автоматически. Это позволяет убедиться в правильности выбора ячейки или диапазона ячеек для указанного имени. В именах диапазонов регистр не учитывается. Например, если выбрать имя Финиш, будет отмечена ячейка Е8 (рис. 1.3).
Рис. 1.2. Список имен диапазонов
Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону
При нажатии клавиши открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.
Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.
Предположим, что необходимо присвоить имя Данные прямоугольному диапазону ячеек A1:B5. Выделите диапазон ячеек A1:B5, введите с клавиатуры Данные в поле Имя и нажмите клавишу . Теперь с помощью формулы =СРЗНАЧ(Данные) можно вычислить среднее значение содержимого ячеек A1:B4 (рис. 1.4).
Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона
Иногда требуется присвоить имя диапазону ячеек, состоящему из нескольких несмежных прямоугольных диапазонов. Например, B3:C4, E6:G7 и B10:C10 (рис. 1.5). Для присвоения имени выделите любой из трех прямоугольников. Удерживая клавишу , выделите остальные два диапазона. Отпустите клавишу , введите имя Несмежный в поле Имя и нажмите клавишу . Теперь имя Несмежный в любой формуле указывает на содержимое ячеек B3:C4, E6:G7 и B10:C10.
Рис. 1.5. Присвоение имени несмежному диапазону ячеек
Создание имен с помощью инструмента Создать из выделенного. На листе «Рис. 1.6» Excel-файла с примерами содержатся продажи за март для каждого из 50 штатов США (рис. 1.6). Требуется присвоить каждой ячейке в диапазоне B2:B51 сокращенное название штата. Выделите диапазон A2:B51 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного, и затем в открывшемся диалоговом окне установите флажок в столбце слева.
Рис. 1.6. Создание имен с помощью инструмента Создать из выделенного
Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона СА, ячейка B7 имеет имя СО и т.д. Создавать имена таких диапазонов с помощью поля Имя было бы невероятно утомительно! Нажмите на стрелку раскрывающегося списка в поле Имя и убедитесь, что все имена диапазонов созданы.
Создание имен диапазонов с помощью инструмента Присвоить имя. Если на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Диспетчер имен (и затем нажать кнопку Создать) или инструмент Присвоить имя, откроется диалоговое окно Создание имени (рис. 1.7).
Рис. 1.7. Диалоговое окно Создание имени
При нажатии на стрелку раскрывающегося списка в поле Область можно выбрать строку Книга или любой лист в книге, указав тем самым область действия имени (рис. 1.8). К любым именам диапазонов можно добавить примечания. Очень полезная опция, если не очевидно, что подразумевает выбранное имя диапазона.
Рис. 1.8. Выбор области действия имени
Диспетчер имен
В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).
Рис. 1.9. Диспетчер имен
Для изменения имени диапазона дважды щелкните кнопкой мыши на имени этого диапазона или выделите его и нажмите кнопку Изменить; после этого можно изменить имя диапазона, ячейки в диапазоне и примечания. Область действия не подлежит изменению. Для удаления какого-либо подмножества имен диапазонов сначала выделите имена диапазонов, которые требуется удалить. Если имена диапазонов перечислены последовательно, выделите первое имя в группе имен, которую требуется удалить, затем, удерживая клавишу , выделите последнее имя в группе. Если требуемые имена не перечислены друг за другом, можно выделить любое из имен, которое необходимо удалить, а далее, удерживая клавишу , выделить остальные требуемые имена диапазонов. Затем для удаления выбранных имен диапазонов нажмите кнопку Удалить.
Редактирование формул в диалоговых окнах
Когда Excel отображает диалоговое окно (например, как на рис. 1.7 или 1.9), в котором можно записать ссылку на диапазон, поле, содержащее такую ссылку, всегда находится в режиме указания. Если активизировать поле Диапазон и воспользоваться стрелками для редактирования ссылки на диапазон, то вы обнаружите, что при этом вы именно указываете на диапазон, а не редактируете текст ссылки. Если на рисунке ниже вы поместите курсор в поле Диапазон, то попытка двинуть курсор влево с помощью стрелки даст неожиданный результат. Вместо движения курсора произошло изменение ссылки (обратите внимание: актуальный режим указан в левой части статусной панели):
Что делать? Нажмите F2. [2] Клавиша F2 позволяет переключаться между режимом указания (ввод) и режимом редактирования (правка). В режиме редактирования стрелки действуют именно так, как при редактировании формулы. На рисунке ниже попытка двинуть курсор влево увенчалась успехом:
Несколько конкретных примеров использования имен диапазонов
1. Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси.
Если вы помните наизусть сокращенные наименования штатов, то можно использовать формулу =AZ+CA+MT+NY+NJ (рис. 1.10)
Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах
2. Необходимо определить среднюю доходность акций, казначейских векселей и облигаций.
Выделите диапазон ячеек B2:D84 (рис. 1.11, часть строк на рис. скрыта), перейдите на вкладку ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного. В этом примере имена диапазона указаны в строке выше. Диапазон B3:B84 получает имя Акции, диапазон C3:C84 — имя Векселя и диапазон D3:D84 — имя Облигации. Таким образом, необходимость помнить, где находятся данные, отпадает. Например, если после начала ввода в ячейку B86 формулы нажать клавишу , откроется диалоговое окно Вставка имени. Кроме того, можно вызвать на экран список доступных имен диапазонов, если после начала ввода на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Использовать в формуле. И, наконец, если вы помните первые буквы имени диапазона, и начнете их вводить в формуле, Excel выдаст подсказку (рис. 1.12). Эта опция Excel называется автозавершение формул. Для завершения ввода имени диапазона дважды щелкните на имени Векселя. Удобство использования имен диапазонов заключается в том, что, не зная точно, где находятся данные, можно работать с данными в любом месте книги!
Рис. 1.11. Исторические данные по инвестициям
Рис. 1.12. Подсказка при вводе в формуле имени диапазона
3. Использование имен столбца и строки
4. Имена с областью действия книга и лист
При создании имен с помощью поля Имя областью действия имен по умолчанию становится Книга. Однако, можно присвоить одно и тоже имя на разных листах, выбрав область действия Лист. Например, создайте новую книгу Excel, содержащую три листа, и введите числа 4, 5, 6 в ячейки E4:E6 на листе Лист1 и 3, 4, 5 в ячейки E4:E6 на листе Лист2. Затем откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 на листе Лист1 и определите область действия для этого имени как Лист1. Далее перейдите на Лист2, откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 и определите область действия для этого имени как Лист2. Диалоговое окно Диспетчер имен показано на рис. 1.13.
Рис. 1.13. Имена на уровне Листа
5. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?
Рассмотрим небольшую таблицу, содержащую формулы (рис. 1.14).
Рис. 1.14. Новые имена диапазонов в старых формулах
Ячейка F3 содержит цену продукта, а ячейка F4 — потребность в продукте =10000–300*F3. В ячейки F5 и F6 введена себестоимость единицы продукции и постоянные затраты, соответственно. Прибыль вычисляется в ячейке F7 по формуле =F4*(F3–F5)–F6. Выделите диапазон E3:F7, затем для присвоения ячейке F3 имени цена, ячейке F4 имени потребность, ячейке F5 имени себестоимость, ячейке F6 имени затраты и ячейке F7 имени прибыль используйте вкладку ФОРМУЛЫ, инструмент Создать из выделенного и флажок в столбце слева. Теперь имена созданных диапазонов необходимо отобразить в формулах ячеек F4 и F7. Для применения имен сначала выделите диапазон, для которого они создаются (в данном случае F3:F7). Затем на вкладке ФОРМУЛЫ в группе Определенные имена нажмите стрелку раскрывающегося списка Присвоить имя и выберите инструмент Применить имена. Выделите в окне имена, которые требуется применить, и нажмите кнопку OK. Обратите внимание, что в ячейке F4 теперь находится формула =10000-300*цена, а в ячейке F7 формула =потребность*(цена–себестоимость)–затраты, что и требовалось. [3]
6. Можно ли вывести на лист Excel список всех имен диапазонов (и представляемых ими ячеек)?
Откройте окно Вставка имени с помощью клавиши и нажмите кнопку Все имена (рис. 1.15). На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.
Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)
7. Использование формул для определения диапазона
Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.
Сначала в поле Имя присвойте ячейке B3 имя прирост. Теперь самое интересное! Переместите курсор в ячейку B7 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Присвоить имя для открытия диалогового окна Создание имени. Введите данные, как показано на рис. 1.16. Поскольку активной является ячейка B7, Excel всегда будет интерпретировать имя диапазона как указывающее на ячейку, находящуюся над текущей ячейкой. Это не будет работать, если в ссылке на ячейку B6 останется знак доллара, поскольку он не позволит изменить ссылку на строку и указать строку непосредственно над активной ячейкой (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel. Если в ячейку B7 ввести формулу =предыдущий*(1+прирост) и скопировать ее в диапазон B8:B13, каждая ячейка будет содержать требуемую формулу, по которой содержимое ячейки непосредственно над активной ячейкой будет умножаться на 1,1.
Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой
Пример 2. Для каждого дня недели дана почасовая оплата и количество отработанных часов (рис. 1.17). Вычислим зарплату за каждый день по формуле почасовая*часы.
Выберите строку 12 (щелкните слева на 12) и в поле Имя (рядом со строкой формул) введите имя почасовая. Выберите строку 13 и введите в поле Имя – часы. Если теперь в ячейку F14 ввести формулу =почасовая*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце автоматически появится результат перемножения значений почасовой оплаты и отработанных часов.
Рис. 1.17. Расчет зарплаты по дням недели
Если вам интересно, предлагаю несколько более сложных примеров использования имен диапазонов: Создание пользовательских функций при помощи имен, Автоматическое обновление сводной таблицы.
Некоторые замечания:
Задания для самостоятельной работы
Исходные данные находятся в файле Имена диапазонов. Задания.xlsx
- На листе Задание 1 содержатся данные о ежемесячной доходности акций General Motors и Microsoft. Присвойте имена диапазонам, содержащим ежемесячную доходность для каждой акции, и вычислите среднемесячную доходность каждой акции.
- На листе Задание 2 присвойте имя Красный диапазону, содержащему ячейки A1:B3 и A6:B8.
- На листе Задание 3 в ячейки G5 и G6 введите широту и долготу любого города, а в ячейки G7 и G8 широту и долготу другого города. В ячейке G10 вычисляется расстояние между двумя городами. Определите имена диапазонов для широты и долготы каждого города и убедитесь, что эти имена отображаются в формуле для расчета расстояния.
- На листе Задание 4 содержится количество акций для каждого вида акций и цена одной акции. Вычислите стоимость акций для каждого вида по формуле =количество*цена.
- На листе Задание 5 создайте имя диапазона для расчета среднего значения продаж за последние пять лет. Измените формулы в ячейках Е14:Е20.
[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование, глава 1.
[2] При написании этого раздела использованы идеи книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 156.
[3] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен.
Система адресации ячеек очень похожа на используемую в игре «Морской бой»: колонки (столбцы) обозначаются буквами английского алфавита, а строчки – натуральными числами.
В результате получается пара буква–число, записываемая подряд: A1, F15.
Современные электронные таблицы строятся из листов, имеющих 2 8 (256) ячеек и 2 16 (65536) строчек. Таким образом на листе содержится 2 24 (16777216) ячеек.
Очевидно, что 26 букв английского алфавита не хватает, в связи с чем, начиная с 27 колонки, в начало буквенной части добавляется буква A: AA, AB, AC, AD. AZ. С 53 колонки вместо A используется B: BA, BB, BC. Последняя колонка обозначается IV.
Диапазон ячеек (Range)
-
Примеры:
- A1:A10 – буква не менялась, то есть берем одну колонку A.
В ней – ячейки с 1 по 10. - B1:D10 – колонки B, C и D (три), строки с 1 по 10. Итого: 3*10 = 30 ячеек.
- A:A – колонка A целиком (все 65536 ячеек).
Задавать диапазон буксировкой мыши намного проще!
Если же диапазон указывается вручную, то следует соблюдать правило углов: начать в левом верхнем и закончить в правом нижнем.
Копирование формул
Предположим, что в колонке А содержится цена, а в колонке B – количество. В колонке C мы хотим получить сумму. Для этого надо в ячейке C1 записать формулу =A1*B1, то есть перемножить значения соответствующих ячеек.
Одно из важнейших преимуществ электронных таблиц заключается в том, что не надо вводить формулы, скажем, для 100 строчек. Достаточно просто скопировать ее в соответствующие ячейки. При этом программа сама поменяет координаты.
Так, при вставке скопированной ячейки C1 в C2 мы сместимся на одну строку. В результате числовые составляющие адресов ячеек увеличатся на 1 и из =A1*B1 мы получим =A2*B2.
При её же копировании в C3 они увеличатся уже на 2 (съехали на 2 строки) и из =A1*B1 мы получим =A3*B3 и т.д.
Но, как же быть, если подобная помощь нам только навредит? Например, мы хотим получить сумму ввалюте, курс которой хранится в ячейке D1? В C1 потребуется формула =A1*B1/D1. При копировании она преобразуется в =A2*B2/D2.
Но в ячейке D2 нет никакой информации и произойдет ошибка – деление на 0! Хуже того, там может храниться другое число, а подобные ошибки крайне сложно заметить.
Разработчики предусмотрели эту ситуацию, предоставив пользователю возможность зафиксировать любую из координат, поставив перед ней знак доллара ($). После этого, при любом копировании формулы такая координата меняться не будет.
Для облегчения труда можно воспользоваться горячей клавишей F4 . Для этого следует установить курсор в любое место адреса и нажать указанную клавишу. Произойдет вставка $ перед обоими координатами. Следующее нажатие оставит $ только перед числом, следующее – только беред буквами, следующее – уберет все доллары из адреса.
Ограничения
На первый взгляд может показаться, что лист электронной таблицы невообразимо огромен, но это не совсем так.
Поскольку информация в таблице подчиняется определенному порядку, давайте обсудим только две потенциальных проблемы, отражающие суть.
Число колонок, составляющее 256 (2^8), совершенно недопустимо для бумажной таблицы. Но если мы захотим отобразить последовательный календарь, где каждая строка описывает ежедневное состояние одного объекта, то колонок явно не хватит.
Пользуясь знанием, что в году 52 недели и вычтя 104 выходных дня, мы получим 261 рабочий день. Ой, тоже не хватает!
Число строк, составляющее 65536 (2^16), можно представить и на бумаге. Что уж говорить об электронных вариантах.
Так, число жителей в вашем районе или городе в большинстве случаев окажется большим.
Очень многие банки совершают гораздо больше транзакций (финансовых проводок) всего за час.
Однако, не следует забывать, что в повседневной жизни мы сталкиваемся с гораздо меньшими объемами информации, которые, тем не менее, крайне сложно обработать вручную.
Если же возможностей электронной таблицы явно не хватает, то для этих целей используются базы данных (БД).
Начиная с версии Excel-2007, размеры листа таблицы увеличены до 16384*1048576 (2^14*2^20). Всли его заполнить формулами, то открытие, равно как и пересчет будет длиться часами (проверено на Intel i7). Будьте вдумчивы при планировании!
Ячейка в Excel – это основной структурный элемент листа, куда Вы можете вводить данные и прочее содержимое. В рамках данного урока мы освоим основы работы с ячейками и их содержимым, чтобы производить вычисления, анализировать и систематизировать данные в Excel.
Основные сведения о ячейках в Excel
Каждый лист в Excel состоит из тысяч прямоугольников, которые называют ячейками. Ячейка – это пересечение строки и столбца. Столбцы в Excel обозначают буквами (A, B, C), в то время как строки обозначают числами (1, 2, 3).
Исходя из строки и столбца, каждой ячейке в Excel присваивается имя, его также называют адрес. Например, C5 – это ячейка, которая находится на пересечении столбца С и строки 5. При выделении ячейки ее адрес отображается в поле Имя. Обратите внимание, что при выборе ячейки, заголовки строки и столбца, на пересечении которых она находится, становятся подсвеченными.
В Microsoft Office Excel имеется возможность выбирать сразу несколько ячеек. Набор из двух или более ячеек принято называть диапазоном. Любой диапазон, точно также, как и ячейка, имеет свой адрес. В большинстве случаев адрес диапазона состоит из адреса левой верхней и правой нижней ячеек, разделенных двоеточием. Такой диапазон называют смежным или непрерывным. Например, диапазон, который состоит из ячеек B1, B2, B3, B4 и B5, будет записан как B1:B5.
На рисунке ниже выделены два разных диапазона ячеек:
Если столбцы на листе обозначены числами вместо букв, необходимо изменить стиль ссылок в Excel, заданный по умолчанию. Для получения подробной информации обратитесь с уроку: Что такое стиль ссылок в Excel.
Выделение ячеек в Excel
Чтобы ввести данные или отредактировать содержимое ячейки, сначала ее нужно выделить.
- Щелкните по ячейке, чтобы выделить ее.
- Выбранная ячейка будет обозначена границами, а заголовки столбца и строки станут подсвеченными. Ячейка останется выделенной, пока Вы не выберите любую другую ячейку.
Вы также можете выделять ячейки при помощи клавиш со стрелками на клавиатуре (клавиши управления курсором).
Выделение диапазона ячеек в Excel
При работе с Excel часто возникает необходимость выделить большую группу ячеек или диапазон.
- Нажмите на первую ячейку диапазона и, не отпуская кнопку, переместите мышь пока все прилегающие ячейки, которые вы хотите выбрать, не будут выделены.
- Отпустите кнопку мыши, требуемый диапазон будет выбран. Ячейки останутся выделенными, пока вы не выберите любую другую ячейку.
Читайте также: