Способы задания ячейки excel

ЯЧЕЙКА (функция ЯЧЕЙКА)

ЯЧЕЙКА Функция возвращает сведения о форматировании, расположении или содержимом ячейки. Например, если перед выполнением вычислений с ячейкой необходимо удостовериться в том, что она содержит числовое значение, а не текст, можно использовать следующую формулу:

Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.

Примечание: Формулы, использующие функцию ЯЧЕЙКА, имеют значения аргументов для конкретного языка и возвращают ошибки при вычислениях с использованием другой языковой версии Excel. Например, если при создании формулы, содержащей ячейку, при использовании чешской версии Excel эта формула возвращает ошибку, если книга открыта во французском языке. Если важно, чтобы другие люди открывали вашу книгу с помощью разных языковых версий Excel, рассмотрите возможность использования альтернативных функций или разрешение на сохранение локальных копий, в которых они меняют аргументы ЯЧЕЙКА в зависимости от языка.

Синтаксис

Аргументы функции ЯЧЕЙКА описаны ниже.

Текстовое значение, задающее тип сведений о ячейке при возвращении. В приведенном ниже списке указаны возможные значения аргумента «тип_сведений» и соответствующие результаты.

Ячейка, сведения о которой требуется получить.

Если этот аргумент опущен, сведения, указанные в аргументе info_type, возвращаются для ячейки, выбранной на момент вычисления. Если аргумент «ссылка» является диапазоном ячеек, функция ЯЧЕЙКА возвращает сведения об активной ячейке в выбранном диапазоне.

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

В режиме автоматического вычисления при внесении пользователем изменений в ячейку вычисление может запускаться до или после выполнения выделения в зависимости от платформы, используемой для Excel. Например, Excel для Windows активирует вычисление перед изменением выделения,Excel в Интернете запускает его после этого.

Когда Co-Authoring с другим пользователем, который вносит изменения, эта функция сообщает о вашей активной ячейке, а не редакторе.

Любой пересчет, например нажатие F9, приведет к тому, что функция возвращает новый результат, даже если изменение ячейки не было изменено.

info_type значения

В следующем списке описаны текстовые значения, которые можно использовать info_type аргумента. Эти значения должны быть введены в функцию ЯЧЕЙКА с кавычками (» «).

Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки.

Номер столбца ячейки в аргументе «ссылка».

1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях — 0 (ноль).

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Значение левой верхней ячейки в ссылке; не формула.

Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка («»).

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-«. Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()».

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях — 0.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (‘) соответствует тексту, выровненному влево, двойная кавычка («) — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта (\) — тексту, распределенному по всей ширине ячейки, а пустой текст («») — любому другому содержимому ячейки.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

0, если ячейка разблокирована, и 1, если ячейка заблокирована.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Номер строки ячейки в аргументе «ссылка».

Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому содержимому.

Возвращает массив с 2 элементами.

Первый элемент массива — это ширина столбца ячейки, округленная до целого. Единица измерения равна ширине одного знака для шрифта стандартного размера.

Второй элемент массива имеет значение Boolean, значение true, если ширина столбца является значением по умолчанию, или FALSE, если ширина явно задана пользователем.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Коды форматов функции ЯЧЕЙКА

В приведенном ниже списке описаны текстовые значения, возвращаемые функцией ЯЧЕЙКА, если в качестве аргумента «тип_сведений» указано значение «формат», а аргумент ссылки указывает на ячейку, отформатированную с использованием встроенного числового формата.

Формат Microsoft Excel

Значение, возвращаемое функцией ЯЧЕЙКА

Источник

Выбор ячеек и диапазонов с помощью процедур Visual Basic в Excel

Корпорация Майкрософт предоставляет примеры программирования только в целях демонстрации без явной или подразумеваемой гарантии. Данное положение включает, но не ограничивается этим, подразумеваемые гарантии товарной пригодности или соответствия отдельной задаче. Эта статья предполагает, что пользователь знаком с представленным языком программирования и средствами, используемыми для создания и отладки процедур. Специалисты службы поддержки Майкрософт могут объяснить возможности конкретной процедуры, но они не изменяют эти примеры, чтобы предоставить дополнительные функции или создать процедуры для удовлетворения конкретных требований. В примерах, приведенных в этой статье, используются методы Visual Basic, приведенные в следующей таблице.

В примерах, приведенных в этой статье, используются свойства, указанные в таблице ниже.

Выбор ячейки на активном листе

Чтобы выбрать ячейку D5 на активном листе, можно использовать любой из следующих примеров:

Выбор ячейки на другом листе в той же книге

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

Вы также можете активировать лист, а затем использовать метод 1, чтобы выбрать ячейку:

Выбор ячейки на листе в другой книге

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

Вы также можете активировать лист, а затем использовать метод 1, чтобы выбрать ячейку:

Выбор диапазона ячеек на активном листе

Чтобы выбрать диапазон C2: D10 на активном листе, можно использовать любой из следующих примеров:

Выбор диапазона ячеек на другом листе в той же книге

Чтобы выбрать диапазон D3: E11 на другом листе той же книги, можно использовать любой из следующих примеров:

Вы также можете активировать лист, а затем использовать способ 4 выше, чтобы выбрать диапазон:

Выбор диапазона ячеек на листе в другой книге

Чтобы выбрать диапазон E4: F12 на листе в другой книге, можно использовать любой из следующих примеров:

Вы также можете активировать лист, а затем использовать способ 4 выше, чтобы выбрать диапазон:

Выбор именованного диапазона на активном листе

Чтобы выбрать именованный диапазон «Test» на активном листе, можно использовать любой из следующих примеров:

Выбор именованного диапазона на другом листе в той же книге

Чтобы выбрать именованный диапазон «Test» на другом листе той же книги, можно использовать следующий пример:

Вы также можете активировать лист, а затем использовать метод 7 выше для выбора именованного диапазона:

Выбор именованного диапазона на листе в другой книге

Чтобы выбрать именованный диапазон «Test» на листе в другой книге, можно использовать следующий пример:

Вы также можете активировать лист, а затем использовать метод 7 выше для выбора именованного диапазона:

Выбор ячейки относительно активной ячейки

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

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

При попытке выбрать ячейку, которая находится в состоянии «не на листе», произойдет ошибка. В первом примере, приведенном выше, возвращается сообщение об ошибке, если активная ячейка находится в столбцах A-D, так как при перемещении четырех столбцов влево активная ячейка будет иметь недопустимый адрес ячейки.

Выбор ячейки относительно другой (неактивной) ячейки

Чтобы выделить ячейку с пятью строками ниже и четырьмя столбцами справа от ячейки C7, можно использовать любой из следующих примеров:

Выбор диапазона смещения ячеек в указанном диапазоне

Чтобы выбрать диапазон ячеек, размер которых совпадает с именованным диапазоном «Test», но с последующим сдвигом на четыре строки вниз и тремя столбцами вправо, можно использовать следующий пример:

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

Выбор указанного диапазона и изменение размера выделенного фрагмента

Для выбора именованного диапазона «Database» и последующего расширения выделенного фрагмента на пять строк можно использовать следующий пример:

Выбор указанного диапазона, его смещение и изменение его размера

Чтобы выбрать диапазон четыре строки ниже и три столбца справа от именованного диапазона «база данных» и включить две строки и один столбец больше, чем именованный диапазон, можно использовать следующий пример:

Выбор объединения двух или более указанных диапазонов

Чтобы выбрать объединение (то есть область объединения) двух именованных диапазонов «Test» и «Sample», можно использовать следующий пример:

чтобы этот пример работал, оба диапазона должны находиться на одном листе. Кроме того, обратите внимание на то, что метод Union не работает на разных листах. Например, эта строка работает нормально.

Возвращает сообщение об ошибке:

Сбой метода Union класса приложения

Как выбрать пересечение двух или более указанных диапазонов

Чтобы выбрать пересечение двух именованных диапазонов «Test» и «Sample», можно использовать следующий пример:

Обратите внимание, что для работы этого примера оба диапазона должны находиться на одном листе.

В примерах, описанных в этой статье 17-21, описывается следующий пример набора данных. В каждом примере указывается диапазон ячеек в выбранном образце данных.

Выбор последней ячейки столбца с непрерывными данными

Чтобы выбрать последнюю ячейку в непрерывном столбце, используйте следующий пример:

Если этот код используется с образцом таблицы, будет выбрана ячейка A4.

Как выделить пустую ячейку в нижней части столбца непрерывных данных

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

Если этот код используется с образцом таблицы, будет выбрана ячейка A5.

Выбор всего диапазона смежных ячеек в столбце

Чтобы выбрать диапазон смежных ячеек в столбце, используйте один из следующих примеров:

Если этот код используется с образцом таблицы, будут выбраны ячейки a1 — A4.

Выбор всего диапазона несмежных ячеек в столбце

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

Если этот код используется с образцом таблицы, он выберет ячейки с a1 по A6.

Выбор прямоугольного диапазона ячеек

Чтобы выбрать прямоугольный диапазон ячеек вокруг ячейки, используйте метод CurrentRegion. Диапазон, выбранный методом CurrentRegion, это область, ограниченная любым сочетанием пустых строк и пустых столбцов. Ниже приведен пример использования метода CurrentRegion.

В этом коде будут выделены ячейки A1 — C4. В других примерах ниже показано, как выбрать один и тот же диапазон ячеек:

В некоторых случаях может потребоваться выделить ячейки a1 — C6. В этом примере метод CurrentRegion не будет работать из-за пустой строки на строке 5. В приведенных ниже примерах будут выбраны все ячейки:

Выбор нескольких несмежных столбцов различной длины

Чтобы выбрать несколько несмежных столбцов различной длины, используйте следующий пример таблицы и макроса:

При использовании этого кода с образцом таблицы ячейки a1: A3 и C1: C6 будут выбраны.

Примечания к примерам

Обычно свойство Активешит можно опустить, так как оно подразумевает, что конкретный лист не является именем. Например, вместо

Вы можете использовать следующие компоненты:

Также можно опустить свойство Активеворкбук. Если не указана конкретная книга, подразумевается активная книга.

При использовании метода Application. goto, если вы хотите использовать два метода Cell в методе Range, если указанный диапазон находится на другом (неактивном) листе, необходимо включить объект Sheets каждый раз. Например:

Для любого элемента в кавычках (например, именованного диапазона «Test») можно также использовать переменную, значение которой является строкой текста. Например, вместо

Источник

Способы задания ячейки excel

Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).

Интерфейс

Настраиваем панель быстрого доступа

Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».

Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.

Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:

Перемещаемся по ленте без мышки

Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:

Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.

Ввод данных

Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.

Автозамена

Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:

Прогрессия

Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:

Протягивание

Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:

Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).

Проверка ошибок

Проверка данных позволяет избежать ошибок при вводе информации в ячейки.

Какие бывают типовые ошибки в Excel?

• Текст вместо чисел
• Отрицательные числа там, где их быть не может
• Числа с дробной частью там, где должны быть целые
• Текст вместо даты
• Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

Инструмент проверки данных

Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:

Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.

Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.

Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:

Удаление пробелов

Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).

Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:

Дата и время

За любой датой в Excel скрывается целое число. Датой его делает формат.

Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.

Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:

Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.

Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.

Поиск и подстановка значений

Функция ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).

=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])

У нее есть два режима работы: интервальный просмотр и точный поиск.

Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).

В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.

Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX

У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.

Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:

=ПОИСКПОЗ (что ищем; где ищем ; 0)

На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).

ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.

=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)

Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.

Получается следующая конструкция:

=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))

Оформление

Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».

На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:

А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.

На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →

Источник