Функция ячейка в excel: ЯЧЕЙКА (функция ЯЧЕЙКА)

Содержание

Функция ЯЧЕЙКА в Excel, Ячейка() в эксель, формула ЯЧЕЙКА

Функция СМЕЩ в Excel. Как автоматически расширить диапазон?

Для чего в Excel формула СУММПРОИЗВ?

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

Кто знает VBA, удивится: функционал легко заменяется программно. Но мы здесь занимаемся оптимизацией без программирования, для пользователей.

Содержание

  • Функция ЯЧЕЙКА в Excel. Описание
  • Функция ЯЧЕЙКА в Excel. Как применять?
  • Похожие статьи
Функция ЯЧЕЙКА в Excel. Описание

Если в двух словах, то функция =ЯЧЕЙКА() работает со свойствами ячейки и имеет два аргумента

  • тип_сведений — обязательный реквизит, текст, заполняется аргументами на выбор в таблице ниже
  • ссылка — необязательный аргумент, адрес ссылки на ячейку, для некоторых типов сведений, например таких как «имяфайла» его не нужно указывать. Если аргумент это диапазон, т.к. стандартно выводится результат верхней левой ячейки.

Таблица аргумента Тип_сведений представлена на рисунке в начале страницы, таблице ниже, а так же в файле примере (список представлен в последовательности формулы)

Тип_сведений Описание Значение ячейки Возвращается
«адрес» Адрес ячейки в формате А1 (если диапазон то верхней левой ячейки) $D$3
«защита» 0, если ячейка разблокирована в Формате ячейки, и 1, если ячейка заблокирована. 1
«имяфайла» Полный путь к файлу, включая имя и название листа. Для несохраненгого листа возвращается пусто «»
C:\Users\….\Пример.xlsx
C:\Users\User\Desktop\[Функция Ячейка.xlsx]1
«префикс» Текст. Код обозначающий выравнивание ячейки, расшифровка ниже текст ^
«скобки» 1, если формат отрицательных чисел в скобках; иначе — 0. 0
«содержимое» Не формула, значение ячейки 5 5
«столбец» Номер столбца аргумента «ссылка». 4
«строка» Номер строки аргумента «ссылка». 10
«тип» Текст. Зависит от типа данных, значение «b» — пустая ячейка, «l» — константа, «v» — любому другому содержимому. 1 v
«формат» Текст. Буква соответствует определенному формату (см здесь). Значения для различных форматов показаны ниже в таблице. Если меняется цвет при отрицательных значениях то добавляется  «-«. C2
«цвет» 1, если ячейка изменяет цвет для отрицательных значений; иначе = 0 (ноль) 0
«ширина» Целое число. Ширина столбца указанной ссылки 8

Расшифровка типа сведений «префикс»

  1. одинарная кавычка или апостраф(‘) текст выровнен влево,
  2. («) — текст, выровнен вправо,
  3. (^) — текст, выровнен по центру,
  4.  (/) — текст, распределен по всей ширине ячейки,
  5. Пусто («») — любому другому содержимому ячейки.
Функция ЯЧЕЙКА в Excel. Как применять?

Как найти номер строки/столбца и адрес файла, думаю, вы поняли из таблицы.

Разберем более интересные примеры:

1. Необходимо отметить в столбце все ячейки начиная с 5 и записать в них 1

Формула

=ЕСЛИ(ЯЧЕЙКА("строка";A6)>5;1;"")

2. Если вам нужно найти столбец ширина которого равна 100, не проверять же каждый столбец руками

Формула

=ЕСЛИ(ЯЧЕЙКА("ширина";C1)=10;1;0)

Примеры доступны так же в файле

 

Функция СМЕЩ в Excel. Как автоматически расширить диапазон?

Для чего в Excel формула СУММПРОИЗВ?

русский: Понятие ячеек (Cell Basics)

Lesson 3: Понятие ячеек (Cell Basics)

/en/tr_ru-misc/-getting-started-with-your-first-computer/content/

Введение

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

По желанию: загрузите наше практическое учебное пособие.

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

Понятие ячеек

Каждый рабочий лист состоит из тысяч столбцов и строк, которые называются ячейками. Ячейка представляет собой пересечение строки и столбца.

Столбцы обозначены буквами (А, В, С), в то время как строки обозначены номерами (1, 2, 3). Каждая ячейка имеет свой уникальный адрес ячейки на основании номера столбца и строк. В приведенном ниже примере, выделенная ячейка пересекает столбец С и 5 -ую строку, значит адресом ячейки является С5.

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

Вы можете также выделить одновременно несколько ячеек. Группа ячеек также может называться диапазоном ячеек. Вместо одного уникального адреса ячейки, вы можете ссылаться на диапазон ячеек с использованием адресов первой и последней в диапазоне ячеек, разделенных двоеточием. Например, диапазон ячеек, который включает ячейки A1, A2, A3, A4 и A5 будет записан как A1:A5. Ниже приведены диапазоны различных ячеек:

  • Диапазон ячеек A1: A8

  • Диапазон ячеек A1: F1
  • Диапазон ячеек A1: F8

Если столбцы в таблице обозначены цифрами вместо букв, вам необходимо изменить настройки свойств ссылок по умолчанию для Excel. Дополнительно об этом вы можете узнать, ознакомившись с уроком «Что такое свойства ссылок?»

Выделяем ячейку:

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

  1. Нажмите на ячейку, чтобы ее выделить. В нашем примере мы выберем ячейку D9.
  2. Вокруг выбранной ячейки отобразится рамка, а заголовок столбца и название строк выделятся цветом. Ячейка будет оставаться выделенной до тех пор, пока вы не нажмете в таблице на другую ячейку.

Можно также выделять ячейки с помощью нажатия клавиш со стрелками на клавиатуре.

Выделяем диапазон ячеек:

Иногда вам может понадобиться выделить большую группу ячеек или диапазон ячеек.

  1. Нажмите на ячейку и протяните курсор мыши по смежным ячейкам, которые нужно выделить. В нашем примере мы выделили диапазон B5:С18.
  2. Отпустите кнопку мыши, чтобы выделить нужный диапазон ячеек. Ячейки останутся выделенными до тех пор, пока вы не нажмете в таблице на другую ячейку.

— Разрыв страницы

Содержимое ячейки

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

  • Текстовые значения: ячейки могут содержать текстовые значения, такие как буквы, цифры и даты.
  • Форматирование атрибутов: ячейки могут содержать атрибуты форматирования, изменяющие способы отображения букв, цифр и дат. Например, проценты могут отображаться как 0,15 или 15%. Вы даже можете изменить текст или цвет фона ячейки.
  • Формулы и функции: ячейки могут содержать формулы и функции, которые вычисляют значения ячеек. В нашем примере, SUM (B2: B8) добавляет значение каждой ячейки в диапазоне ячеек В2:В8 и отображает результат в ячейке B9.
Ввод данных:
  1. Нажмите на ячейку, чтобы ее выделить. В нашем примере мы выделим ячейку F9.
  2. Наберите любой текст в выбранную ячейку, а затем нажмите Ввод на клавиатуре. Содержимое отобразится в ячейке и строке формул. Вы также можете вводить текстовые значения и редактировать ячейку в строке формул.
Удаляем содержимое ячейки:
  1. Выделите ячейки с содержимым, который вы хотите удалить. В нашем примере мы выделим диапазон ячеек А10:Н10.
  2. В главном меню выберите команду Очистить, а затем нажмите кнопку Очистить содержимое.
  3. Содержимое ячейки будет удалено.

Вы можете также использовать клавишу Удалить на клавиатуре, чтобы удалить содержимое одновременно из нескольких ячеек. Клавиша Backspace будет удалять содержимое только из одной ячейки.

Удаляем ячейки:

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

  1. Выделите ячейки, которые вы хотите удалить. В нашем примере мы выберем А10:Н10.

  2. В Главном меню выберите команду Удалить.
  3. Расположенные ниже ячейки будут смещаться вверх и заполнять пробелы.

— Разрыв страницы

Копирование и вставка содержимого ячейки:

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

  1. Выделите ячейки , которые вы хотите скопировать. В нашем примере мы выберем F9.
  2. Выберите команду Копировать в Главном меню или нажмите комбинацию клавиш Ctrl+C на клавиатуре.
  3. Выделите ячейки, в которые вы хотите вставить содержимое. В нашем примере мы выделим F12:F17. Вокруг скопированных ячеек появится пунктирная рамка.
  4. Выберите команду Вставить в Главном меню или нажмите комбинацию клавиш Ctrl+V на клавиатуре.
  5. Содержимое отобразится в выбранных ячейках.
Доступ к дополнительным параметрам вставки:

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

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

Вырезаем и вставляем содержимое ячейки:

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

  1. Выделите ячейки, которые вы хотите вырезать. В нашем примере мы выбираем опцию G5:G6.
  2. Щелкните правой кнопкой мыши и выберите команду Вырезать. Кроме того, вы можете использовать команду в Главном меню или нажмите комбинацию клавиш Ctrl+X на клавиатуре.
  3. Выделите ячейки, в которые нужно вставить содержимое. В нашем примере мы выберем F10:F11. Вокруг вырезанных ячеек появится пунктирная рамка.
  4. Кликните правой кнопкой мыши и выберите команду Вставить. Кроме того, вы можете использовать команду в Главном меню или нажмите комбинацию клавиш Ctrl+V на клавиатуре.
  5. Содержание будет удалено из исходных ячеек и перемещено в выбранные ячейки.
Перемещение ячеек:

Вместо вырезания, копирования и вставки, вы можете перетаскивать ячейки, чтобы перемещать их содержимое.

  1. Выделите ячейки, которые вы хотите переместить. В нашем примере мы выберем h5:h22.
  2. Наведите курсор мыши на край выделенного блока ячеек, чтобы он принял вид указателя с четырьмя стрелками.
  3. Удерживая нажатой кнопку мыши, перетащите ячейки в нужное место. В нашем примере, мы перенесем их с G4:G12.
  4. Отпустите кнопку мыши. Ячейки будут перемещены в выбранное место.

— Разрыв страницы

Использование маркера автозаполнения:

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

  1. Выделите ячейки, содержание которых необходимо использовать, а затем наведите курсор на нижний правый угол активной ячейки, где появится маркер автозаполнения.
  2. Нажмите, и удерживая курсором мыши, перетащите маркер заполнения по ячейкам, которые нужно заполнить. В нашем примере мы выберем G13:G17.
  3. Отпустите кнопку мыши, чтобы заполнить выбранные ячейки.
Продолжая последовательность с маркером автозаполнения:

Маркер автозаполнения также может быть использован для продолжения последовательности. Когда содержимое строки или столбца следует в последовательном порядке, как числа (1, 2, 3) или дни недели (понедельник, вторник, среда), маркер автозаполнения может угадать, что последует далее. В большинстве случаев, вам нужно будет выбрать несколько ячеек, прежде чем использовать автозаполнение, таким образом Excel сможет определить порядок последовательности. Давайте рассмотрим пример:

  1. Выделите диапазон ячеек, последовательность которых вы хотите продолжить. В нашем примере мы выберем E4:G4.
  2. Нажмите, и удерживая курсором мыши, перетащите маркер автозаполнения по ячейкам, последовательность которых нужно продолжить.
  3. Отпустите кнопку мыши. Если Excel распознает порядок последовательности, то в выделенных ячейках последовательность продолжится. В нашем примере, Excel добавила часть 4, часть 5, часть 6 до h5:J4.

— Разрыв страницы

Практическое занятие!

  1. Откройте наше практическое учебное пособие .
  2. Выберите ячейку D6 и напечатайте hlee.
  3. Очистите содержимое в строке 14.
  4. Удалите столбец G.
  5. Используя команды либо вырезать и вставить, либо перемещение, переместите содержимое ячейки из строки 18 в строку 14.
  6. Используя маркер заполнения, поместите X в ячейках F9:F17.
  7. Когда вы закончите, ваш результат должен выглядеть следующим образом:



Continue

Previous: Начало работы с первы (Getting Started with Your First Computer)


Next:Введение в формулах (Intro to Formulas)

/en/tr_ru-misc/-intro-to-formulas/content/

ЯЧЕЙКА Функция Excel — Возврат информации о ячейке

Дата да
Прибавление (вычитание) дней к дате
Объединение дат
Преобразование даты в число
Преобразование даты в текст
От имени месяца до номера
Создание диапазона дат из дат
Номер дня года
Название месяца с даты
Первый день месяца
Прибавление (вычитание) недель к дате
Если функции с датами
Максимальная дата
Количество дней между датами
Количество дней в месяце
Количество недель между датами
Количество лет между датами
Разбить дату и время на отдельные ячейки
Обратный отсчет оставшихся дней
Вставить даты
Генератор случайных дат
Использование динамических диапазонов — значения с начала года
Прибавление (вычитание) лет к дате
Примеры формулы даты
Извлечь день из даты
Получить название дня из даты
Подсчет оставшихся дней в месяце/годе
Подсчет оставшихся рабочих дней в месяце/годе
Получить последний день месяца
Последний рабочий день месяца/года
Количество рабочих/рабочих дней в месяце
Сокращения для рабочих дней
Автоматическое заполнение дат
Количество месяцев между датами
Четверть с даты
Годы службы
Изменить формат даты
Сравнение дат
Время да
Добавить (вычесть) часы ко времени
Добавить (вычесть) минуты ко времени
Добавить (вычесть) секунды ко времени
Суммарное время (общее время)
Разница во времени
Изменить формат времени
Преобразование минут в часы
Преобразование времени в десятичное число
Преобразование времени в часы
Преобразование времени в минуты
Преобразование времени в секунды
Военное время
Время раунда до ближайших 15 минут
Калькулятор сверхурочной работы
Количество часов между интервалами
Преобразование секунд в минуты, часы или время
Подсчет отработанных часов
Разница во времени
Формат времени — Показать минуты секунды
Текст да
Добавление запятых в ячейки
Получить первое слово из текста
Заглавная первая буква
Очистка и форматирование номеров телефонов
Удаление лишних пробелов в конце/в начале
Добавить пробелы в ячейку
Присвоение числового значения тексту
Объединение ячеек с запятой
Объединить имя и фамилию
Преобразование текстовой строки в дату
Преобразование текста в число
Извлечь текст из ячейки
Получить последнее слово
Удаление ненужных символов
Извлечение текста до или после символа
Как разделить текстовую строку по пробелу, запятой и т. д.
Удаление специальных символов
Удалить первые символы слева
Замена нескольких значений
Переключение имени и фамилии с запятыми
Удалить определенный текст из ячейки
Извлечение текста между символами (например, скобки)
Добавление ведущих нулей к числу
Удалить разрывы строк из текста
Удалить все цифры из текста
Обратный текст
Удалить нечисловые символы
Удалить последний символ(ы) справа
Отдельные имена и фамилии
Отдельный текст и цифры
Круглый да
Круглые формулы
Цена округления до ближайшего доллара или цента
Округлить до ближайших 10, 100 или 1000
Округлить до ближайших 5 или 0,5
Круглые проценты
Округление до значащих цифр
Количество да
Подсчет пустых и непустых ячеек
Подсчет ячеек между двумя числами
Количество ячеек не равно
Подсчет, если ячейки находятся в диапазоне
Подсчет раз, когда слово появляется в ячейке
Подсчет слов в ячейке
Подсчет определенных символов в столбце
Подсчет общего количества символов в столбце
Подсчет ячеек, равных одному из двух результатов
Подсчет ячеек, не содержащих
Подсчет ячеек, содержащих определенный текст
Подсчет уникальных значений в диапазоне
Countif — несколько критериев
Подсчет общего количества ячеек в диапазоне
Подсчет ячеек с любым текстом
Подсчет всех ячеек в таблице
Поиск да
Двумерная ВПР
ВПР Простой пример
ВПР — несколько совпадений
Поиск с учетом регистра
ВПР с учетом регистра
Сумма, если — ВПР
Поиск с учетом регистра
ВПР с учетом регистра
Поиск дубликатов с функцией ВПР или ПОИСКПОЗ
ИНДЕКС СОВПАДЕНИЕ СОВПАДЕНИЕ
Поиск — Возврат адреса ячейки (не значение)
Поиск последнего значения в столбце или строке
Обратная ВПР (справа налево)
Сегмент оценки риска с функцией ВПР
Сумма с функцией ВПР
ВПР И НЕПРЯМАЯ
ВПР Объединение
ВПР содержит (частичное совпадение)
17 причин, почему XLOOKUP не работает
Двойной (вложенный) XLOOKUP — динамические столбцы
IFERROR (& IFNA) XLOOKUP
Поиск мин. /макс. значения
Вложенная функция ВПР
11 лучших альтернатив ВПР (обновлено в 2022 г.!)
ВПР – Динамическая ссылка на столбец
ВПР – Исправить ошибку #Н/Д
ВПР – Несколько листов одновременно
Комбинированный ВПР и ГПР
ВПР И ПОИСКПОЗ Комбинированный
ВПР между рабочими листами или электронными таблицами
ВПР Повторяющиеся значения
Буквенные оценки с функцией ВПР
ВПР Возврат нескольких столбцов
ВПР Возвращает 0? Вернуть пустой вместо
ВПР без #Н/Д Ошибка
XLOOKUP Несколько листов одновременно
XLOOKUP между рабочими листами или электронными таблицами
XLOOKUP по дате
XLOOKUP Повторяющиеся значения
XLOOKUP Множественные критерии
XLOOKUP Вернуть несколько столбцов
XLOOKUP Возвращает 0? Вернуть пустой вместо
XLOOKUP Текст
XLOOKUP с ЕСЛИ
XLOOKUP с оператором If
Разное да
Сортировка нескольких столбцов
Использовать значение ячейки в формуле
Процентное изменение между числами
Процентная разбивка
Значения рангов
Добавить пробелы в ячейку
CAGR Формула
Среднее время
Десятичная часть числа
Целая часть числа
Сравнение элементов в списке
Обработка ошибок NA()
Получить имя листа
Подстановочные знаки
Гиперссылка на текущую папку
Формула сложных процентов
Увеличение в процентах
Создание случайных групп
Сортировка с функциями малого и большого размера
Альтернативы энергонезависимой функции
Уменьшить число на процент
Расчет процентной дисперсии
Калькулятор маржи прибыли
Преобразование номера столбца в букву
Получить полный адрес именованного диапазона
Вставить имя файла
Путь вставки
Широта/долгота Функции
Замена отрицательных значений
Диапазон обратного списка
Преобразование названия штата в аббревиатуру
Создание динамических гиперссылок
Пользовательский список сортировки с формулой
Проверка данных — пользовательские формулы
Динамическая ссылка на лист (КОСВЕННАЯ)
Справочная ячейка в другом листе или книге
Получить значение ячейки по адресу
Получить имя листа
Увеличение ссылки на ячейку
Список имен листов
Список пропущенных номеров в последовательности
Адрес возврата максимального значения в диапазоне
Поиск по ключевым словам
Выбрать каждую вторую (или каждую n-ю) строку
Основы да
Основы ссылок на ячейки — A1, R1C1, 3d и т. д.
Сложение (сумма) всего столбца или строки
В формулы динамического массива
Преобразования да
Преобразование часовых поясов
Преобразование градусов Цельсия в градусы Фаренгейта
Преобразование фунтов в килограммы
Преобразование времени в время Unix
Преобразование футов в метры
Преобразование сантиметров в дюймы
Преобразование километров в мили
Преобразование дюймов в футы
Преобразование даты в юлианский формат
Преобразовать букву столбца в число
Испытания да
Проверить, содержит ли диапазон какой-либо текст
Проверить, является ли какая-либо ячейка в диапазоне номером
Проверить, содержит ли ячейка конкретное значение
Проверить, содержит ли ячейка любое число
Проверить, содержит ли ячейка определенное число
Проверить, является ли ячейка числом или текстом
Если да
Процентиль Если
Промежуточный итог Если
Суммарное произведение Если
Большой, если и Маленький, если
Медиана Если
Объединить Если
Максимум Если
Ранг Если
ТЕКСТОВОЕ СОЕДИНЕНИЕ Если
Сумма да
Сумма, если — Начинается с / Заканчивается на
Сумма, если — месяц или год до даты
Сумма, если — по годам
Сумма, если — пусто/не пусто
Сумма, если — Горизонтальная сумма
Количество/сумма If — Цвет ячейки
НЕПРЯМАЯ Сумма
Сумма, если — по нескольким листам
Сумма Если — По Месяцу
Сумма, если — ячейки не равны
Сумма, если не пусто
Сумма между значениями
Сумма, если — номер недели
Сумма Текст
Сумма, если — по категориям или группам
Сумма, если — ячейка содержит определенный текст (подстановочные знаки)
Сумма, если — Дата Rnage
Сумма, если даты равны
Сумма, если — День недели
Сумма, если больше
Сумма, если меньше
В среднем да
Средние ненулевые значения
Среднее значение, если не пусто
Среднее — игнорировать 0
Среднее — игнорировать ошибки
Математика да
Таблица умножения
Кубические корни
Энные корни
Квадратные числа
Квадратные корни
Расчеты да
Рассчитать коэффициент
Рассчитать возраст
УБИТЬ
Расчет платежей по кредиту
Формула среднего балла
Расчет НДС
Как оценивать формулы
Найти да
Поиск числа в столбце/книге
Поиск наиболее частых номеров
Поиск наименьших n значений
Найти n-е вхождение символа в текст
Поиск и извлечение числа из строки
Поиск самой ранней или самой поздней даты на основе критериев
Найти первую ячейку с любым значением
Найти последнюю строку
Найти последнюю строку с данными
Поиск пропущенных значений
Поиск наибольших n значений
Самый частый номер
Условное форматирование да
Условный формат — дата и время
Условный формат — выделение пустых ячеек
Новые функции
XLOOKUP Заменяет VLOOKUP, HLOOKUP и INDEX/MATCH
Логический да
И Проверяет выполнение всех условий. ИСТИНА/ЛОЖЬ
IF Если условие выполнено, сделайте что-нибудь, если нет, сделайте что-нибудь еще.
ЕСЛИОШИБКА Если результатом является ошибка, сделайте что-нибудь еще.
НЕ Изменяет ИСТИНА на ЛОЖЬ и ЛОЖЬ на ИСТИНА.
ИЛИ Проверяет выполнение каких-либо условий. ИСТИНА/ЛОЖЬ
XOR Проверяет, выполняется ли одно и только одно условие. ИСТИНА/ЛОЖЬ
Поиск и ссылка да
ЛОЖЬ Логическое значение: ЛОЖЬ.
ИСТИНА Логическое значение: ИСТИНА.
АДРЕС Возвращает адрес ячейки в виде текста.
ОБЛАСТИ Возвращает количество областей в ссылке.
ВЫБОР Выбирает значение из списка на основе его номера позиции.
КОЛОННА Возвращает номер столбца ссылки на ячейку.
СТОЛБЦЫ Возвращает количество столбцов в массиве.
HLOOKUP Поиск значения в первой строке и возврат значения.
ГИПЕРССЫЛКА Создает интерактивную ссылку.
ИНДЕКС Возвращает значение на основе номеров столбца и строки.
НЕПРЯМАЯ Создает ссылку на ячейку из текста.
ПРОСМОТР Поиск значений по горизонтали или по вертикали.
ПОИСКПОЗ Ищет значение в списке и возвращает его позицию.
СМЕЩЕНИЕ Создает смещение привязки от начальной точки.
ROW Возвращает номер строки ссылки на ячейку.
ROWS Возвращает количество строк в массиве.
ТРАНСП Меняет ориентацию диапазона ячеек.
ВПР Поиск значения в первом столбце и возврат значения.
Дата и время да
ДАТА Возвращает дату из года, месяца и дня.
DATEDIF Количество дней, месяцев или лет между двумя датами.
DATEVALUE Преобразует дату, сохраненную в виде текста, в действительную дату
ДЕНЬ Возвращает день в виде числа (1-31).
ДНЕЙ Возвращает количество дней между двумя датами.
DAYS360 Возвращает количество дней между двумя датами в году из 360 дней.
EDATE Возвращает дату, отстоящую от начальной даты на n месяцев.
КОНМЕСЯЦА Возвращает последний день месяца, до которого осталось n месяцев.
ЧАС Возвращает час в виде числа (0-23).
МИНУТЫ Возвращает минуты в виде числа (0-59).
МЕСЯЦ Возвращает месяц в виде числа (1-12).
ЧИСТРАБДНИ Количество рабочих дней между двумя датами.
NETWORKDAYS.INTL Рабочие дни между двумя датами, настраиваемые выходные.
СЕЙЧАС Возвращает текущую дату и время.
СЕКУНД Возвращает секунды в виде числа (0-59)
ВРЕМЯ Возвращает время из часов, минут и секунд.
TIMEVALUE Преобразует время, сохраненное в виде текста, в действительное время.
СЕГОДНЯ Возвращает текущую дату.
ДЕНЬ НЕД Возвращает день недели в виде числа (1-7).
WEEKNUM Возвращает номер недели в году (1-52).
РАБДЕНЬ Дата n рабочих дней от даты.
WORKDAY.INTL Дата n рабочих дней от даты, настраиваемые выходные.
ГОД Возвращает год.
YEARFRAC Возвращает долю года между двумя датами.
Машиностроение да
ПРЕОБРАЗОВАТЬ Преобразование числа из одной единицы в другую.
Финансовый да
FV Расчет будущей стоимости.
PV Вычисляет текущую стоимость.
КПЕР Вычисляет общее количество периодов платежей.
PMT Расчет суммы платежа.
СТАВКА Расчет процентной ставки.
NPV Расчет чистой приведенной стоимости.
IRR Внутренняя норма доходности для набора периодических CF.
XIRR Внутренняя норма доходности для набора непериодических КФ.
ЦЕНА Рассчитывает цену облигации.
ДОХОД Рассчитывает доходность облигации.
INTRATE Процентная ставка полностью инвестированной ценной бумаги.
Информация да
CELL Возвращает информацию о ячейке.
ERROR.TYPE Возвращает значение, представляющее ошибку ячейки.
IПУСТО Проверить, пуста ли ячейка. ИСТИНА/ЛОЖЬ
ISERR Проверяет, является ли значение ячейки ошибкой, игнорирует #N/A. ИСТИНА/ЛОЖЬ
ЕОШИБКА Проверить, является ли значение ячейки ошибкой. ИСТИНА/ЛОЖЬ
ИСЭВЕН Проверка четности значения ячейки. ИСТИНА/ЛОЖЬ
ISFORMULA Проверить, является ли ячейка формулой. ИСТИНА/ЛОЖЬ
НЕЛОГИЧНОСТЬ Проверка логичности ячейки (ИСТИНА или ЛОЖЬ). ИСТИНА/ЛОЖЬ
ISNA Проверить, является ли значение ячейки #N/A. ИСТИНА/ЛОЖЬ
ISNONTEXT Проверить, не является ли ячейка текстом (пустые ячейки не являются текстом). ИСТИНА/ЛОЖЬ
ISNUMBER Проверить, является ли ячейка числом. ИСТИНА/ЛОЖЬ
ISODD Проверить, является ли значение ячейки нечетным. ИСТИНА/ЛОЖЬ
ISREF Проверить, является ли значение ячейки ссылкой. ИСТИНА/ЛОЖЬ
ISTEXT Проверить, является ли ячейка текстовой. ИСТИНА/ЛОЖЬ
N Преобразует значение в число.
NA Возвращает ошибку: #N/A.
TYPE Возвращает тип значения в ячейке.
Математика да
ABS Вычисляет абсолютное значение числа.
АГРЕГАТ Определение и выполнение вычислений для базы данных или списка.
CEILING Округляет число до ближайшего указанного кратного.
COS Возвращает косинус угла.
ГРАДУСЫ Преобразует радианы в градусы.
DSUM Суммирует записи базы данных, соответствующие определенным критериям.
EVEN Округляет до ближайшего четного целого числа.
EXP Вычисляет экспоненциальное значение для заданного числа.
ФАКТ Возвращает факториал.
ЭТАЖ Округляет число в меньшую сторону до ближайшего указанного кратного.
НОД Возвращает наибольший общий делитель.
INT Округляет число в меньшую сторону до ближайшего целого числа.
LCM Возвращает наименьшее общее кратное.
LN Возвращает натуральный логарифм числа.
LOG Возвращает логарифм числа по указанному основанию.
LOG10 Возвращает логарифм числа по основанию 10.
MOD Возвращает остаток после деления.
MROUND Округляет число до указанного кратного.
НЕЧЕТНОЕ Округляет до ближайшего нечетного целого числа.
PI Значение PI.
POWER Вычисляет число, возведенное в степень.
ПРОИЗВЕД Умножает массив чисел.
ЧАСТНОЕ Возвращает целочисленный результат деления.
РАДИАНЫ Преобразует угол в радианы.
RAND Вычисляет случайное число от 0 до 1.
СЛУЧМЕЖДУ Вычисляет случайное число между двумя числами.
ОКРУГЛ Округляет число до указанного количества цифр.
ОКРУГЛ ВНИЗ Округляет число в меньшую сторону (к нулю).
ОКРУГЛВВЕРХ Округляет число в большую сторону (от нуля).
ЗНАК Возвращает знак числа.
SIN Возвращает синус угла.
SQRT Вычисляет квадратный корень из числа.
ПРОМЕЖУТОЧНЫЙ ИТОГ Возвращает сводную статистику для серии данных.
СУММ Складывает числа.
СУММЕСЛИ Суммирует числа, соответствующие критерию.
СУММЕСЛИМН Суммирует числа, соответствующие нескольким критериям.
СУММПРОИЗВ Умножает массивы чисел и суммирует полученный массив.
TAN Возвращает тангенс угла.
TRUNC Усекает число до определенного количества цифр.
Статистика да
СРЕДНИЙ Средние числа.
AVERAGEA Средние числа. Включает текст и FALSE = 0, TRUE = 1.
СРЗНАЧЕСЛИ Усредняет числа, соответствующие критерию.
СРЗНАЧЕСЛИМН Усредняет числа, соответствующие нескольким критериям.
КОРРЕЛ Вычисляет корреляцию двух рядов.
COUNT Подсчет ячеек, содержащих число.
COUNTA Подсчет непустых ячеек.
COUNTB004 COUNTB005

Подсчет пустых ячеек.
СЧЁТЕСЛИ Подсчитывает ячейки, соответствующие критерию.
СЧЁТЕСЛИМН Подсчитывает ячейки, соответствующие нескольким критериям.
ПРОГНОЗ Прогноз будущих значений y на основе линейной линии тренда.
ЧАСТОТА Подсчитывает значения, попадающие в указанные диапазоны.
РОСТ Вычисляет значения Y на основе экспоненциального роста.
INTERCEPT Вычисляет точку пересечения Y для линии наилучшего соответствия.
НАИБОЛЬШИЙ Возвращает k-е наибольшее значение.
ЛИНЕЙН Возвращает статистику по линии тренда.
MAX Возвращает наибольшее число.
МЕДИАНА Возвращает медианное число.
МИН Возвращает наименьшее число.
РЕЖИМ Возвращает наиболее распространенный номер.
ПРОЦЕНТИЛЬ Возвращает k-й процентиль.
PERCENTILE.INC Возвращает k-й процентиль. Где k включительно.
PERCENTILE.EXC Возвращает k-й процентиль. Где k является исключительным.
КВАРТИЛЬ Возвращает указанное значение квартиля.
QUARTILE.INC Возвращает указанное значение квартиля. включительно.
QUARTILE.EXC Возвращает указанное значение квартиля. Эксклюзив.
RANK Ранг числа в серии.
RANK. AVG Ранг числа в серии. Средние.
RANK.EQ Ранг числа в серии. Высший ранг.
НАКЛОН Вычисляет наклон на основе линейной регрессии.
МАЛЕНЬКИЙ Возвращает k-е наименьшее значение.
СТАНДОТКЛОН Вычисляет стандартное отклонение.
STDEV.P Вычисляет стандартное отклонение всей совокупности.
STDEV.S Вычисляет SD образца.
STDEVP Расчет стандартного отклонения всей совокупности
TREND Вычисляет значения Y на основе линии тренда.
Текст да
CHAR Возвращает символ, заданный кодом.
CLEAN Удаляет все непечатаемые символы.
КОД Возвращает числовой код символа.
СЦЕПИТЬ Объединяет текст.
ДОЛЛАР Преобразует число в текст в денежном формате.
EXACT Проверить, равны ли ячейки. Деликатный случай. ИСТИНА/ЛОЖЬ
НАЙТИ Находит положение текста в ячейке. С учетом регистра.
LEFT Обрезает текст на несколько символов слева.
LEN Подсчитывает количество символов в тексте.
LOWER Преобразует текст в нижний регистр.
MID Извлекает текст из середины ячейки.
PROPER Преобразует текст в правильный регистр.
REPLACE Заменяет текст в зависимости от его местоположения.
REPT Повторяет текст несколько раз.
ПРАВО Усекает текст на несколько символов справа.
ПОИСК Находит положение текста в ячейке. Регистр не учитывается.
ПОДСТАВИТЬ Находит и заменяет текст. Деликатный случай.
ТЕКСТ Преобразует значение в текст в определенном числовом формате.
TRIM Удаляет все лишние пробелы из текста.
ПРОПИСНЫЕ Преобразует текст в верхний регистр.
ЗНАЧЕНИЕ Преобразует число, сохраненное как текст, в число.

Сравнение ячеек Excel и функций диапазона в VBA

Excel — это мощный инструмент. Если вы часто используете его, вы, вероятно, уже знаете много приемов с использованием формул или автоматического форматирования, но, используя функцию «Диапазоны и ячейки» в VBA, вы можете поднять свою аналитику Excel на совершенно новый уровень.

Проблема с использованием функций Cells и Range в макросах VBA заключается в том, что на продвинутом уровне большинству людей трудно понять, как на самом деле работают эти функции. Их использование может сильно запутать. Вот как вы можете использовать их способами, которые вы, вероятно, никогда не представляли.

Функция ячеек Visual Basic

Функции «Ячейки» и «Диапазон» позволяют указать скрипту VBA, где именно на рабочем листе вы хотите получить или поместить данные. Основное различие между двумя ячейками заключается в том, на что они ссылаются.

Функция ячеек VBA обычно ссылается на одну ячейку за раз, в то время как Range ссылается на группу ячеек одновременно.

Вот формат для функции ячеек:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Это ссылка на каждую ячейку во всем листе. Это один из таких примеров, когда функция макроячейки VBA не ссылается ни на одну ячейку:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Напротив, этот фрагмент кода ссылается на третью ячейку слева в строке номер один, т. е. Cell C1 :

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Давайте немного поэкспериментируем, сославшись на ячейку D15 , используя функцию ячеек VBA:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Если вы хотите, вы также можете сослаться на ячейку D15 со следующим кодом функции ячеек VBA:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

— вам разрешено использовать букву столбца.

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

Функция диапазона

Во многих отношениях функция «Диапазон» намного мощнее, чем использование «Ячеек», поскольку она позволяет ссылаться либо на одну ячейку, либо на определенный диапазон ячеек одновременно. Вы не захотите перебирать функцию Range, потому что ссылки на ячейки не являются числами (если только вы не встроите в нее функцию ячеек VBA Excel).

Формат этой функции следующий:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Каждая ячейка может быть обозначена буквенным номером. Давайте рассмотрим несколько примеров.

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

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Здесь функция диапазона ссылается на все ячейки от A1 до E20:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Как упоминалось выше, вам не нужно использовать присвоение ячеек число-буква. На самом деле вы можете использовать две функции ячеек VBA внутри функции Range для определения диапазона на листе, например:

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Этот код ссылается на тот же диапазон, что и функция Range(«A1:E20») . Ценность его использования заключается в том, что он позволит вам написать код, который динамически работает с диапазонами с помощью циклов.

Теперь, когда вы понимаете, как форматировать функции Cells и Range, давайте углубимся в то, как можно творчески использовать эти функции в коде VBA.

Обработка данных с помощью функции ячеек

Функция ячеек Visual Basic очень полезна, когда у вас есть сложная формула, которую вы хотите использовать в нескольких диапазонах ячеек. Эти диапазоны также могут существовать на нескольких листах.

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

У вас может быть Sheet1 , который отслеживает их количество продаж и их объем продаж.

На Sheet2, у вас есть данные для отслеживания рейтинга отзывов клиентов вашей компании за последние 30 дней.

Если вы хотите рассчитать бонус на первом листе, используя значения из двух листов, это можно сделать несколькими способами. Вы можете написать формулу в первой ячейке, которая выполняет вычисления с использованием данных на двух листах, и перетащить ее вниз. Это сработает.

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

Так почему бы тогда просто не запустить вычисления для столбца бонусов в том же скрипте?

Функция ячеек макросов Excel в действии

Если вы никогда раньше не писали код VBA в Excel, вам нужно включить Разработчик Пункт меню. Для этого перейдите в File > Options . Щелкните Настроить ленту . Наконец, выберите Developer на левой панели, Добавить на правой панели и убедитесь, что флажок установлен.

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

Вы можете использовать меню Insert , чтобы вставить командную кнопку, или просто нажмите Просмотрите код , чтобы начать кодирование.

В этом примере вы настроите сценарий для запуска при каждом открытии книги. Для этого просто щелкните View Code в меню разработчика и вставьте следующую новую функцию в окно кода.

 Указанный язык: vba не существует' 
 Генерация кода не удалась!! 

'

Окно вашего кода будет выглядеть примерно так.

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

Помните, что функция worksheets.cells имеет строку и столбец в качестве параметров для идентификации каждой отдельной ячейки. Вам нужно заменить x ссылкой на строку и использовать число для запроса данных каждого столбца. Количество строк — это количество сотрудников, поэтому оно будет от 1 до 11. Идентификатор столбца будет 2 для количества продаж, 3 для объема продаж и 2 из листа 2 для оценки отзывов.

В окончательном расчете используются следующие проценты, чтобы сложить до 100 процентов от общего бонусного счета. Он основан на идеальном количестве продаж, равном 50, объеме продаж 50 000 долларов и оценке отзывов 10.

  • (Количество продаж/50) x 0,4
  • (Объем продаж/50 000) x 0,5
  • (Оценка обратной связи/10) x 0,1 За счет 50, объем 50 000 долларов и 10 баллов они получают весь максимальный бонус за месяц. Однако все, что ниже идеального по любому фактору, уменьшает бонус. Все, что лучше идеального, увеличивает бонус.

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

     Указанный язык: vba не существует' 
     Генерация кода не удалась!! 

    '

    Вот как будет выглядеть вывод этого скрипта.

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

    Преимущество метода ячеек в VBA заключается в том, что вы можете создать довольно творческую логику для извлечения данных из множества ячеек на многих листах и ​​выполнения с ними довольно сложных вычислений.

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

    Чтобы изучить все, что вы можете сделать дальше, посетите страницу Microsoft MSDN для объекта Cells.

    Форматирование ячеек с функцией диапазона

    Для прохода по множеству ячеек по одной, функция ячеек идеальна. Но если вы хотите применить что-то сразу ко всему диапазону ячеек, функция Range гораздо эффективнее. В продолжающемся сравнении диапазона VBA с ячейками есть много чего ожидать.

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

    Например, предположим, что если общий объем продаж всех сотрудников отдела продаж превышает в общей сложности 400 000 долларов США, вы хотите выделить все ячейки в столбце бонусов зеленым цветом, чтобы обозначить, что команда получила дополнительный командный бонус.

    Давайте посмотрим, как это можно сделать с помощью оператора IF.

     Указанный язык: vba не существует' 
     Генерация кода не удалась!! 

    '

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

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

    • Применение контура вокруг группы
    • Проверка правописания текста внутри диапазона ячеек
    • Очистка, копирование или вырезание ячеек
    • Поиск в диапазоне с помощью метода Find

    Обязательно прочитайте страницу Microsoft MSDN, чтобы увидеть объект Range все возможности.

Читайте также: