ГлавнаяРазноеФункция excel адрес

Функции в Excel. Как использовать? Функция excel адрес


Функции в Excel. Как использовать?

Тут вы найдете подробные описания 75+ самых важных функций Excel. Для каждой функции есть живые примеры эффективного использования.

  • DATE (ДАТА) – выдает значение даты в в виде года, месяца, дня
  • DATEVALUE (ДАТАЗНАЧ) – конвертирует значение даты в текстовом формате в числовое значение
  • DAY (ДЕНЬ) – выдает значение дня из любой даты
  • HOUR (ЧАС) – выдает значение часа из любой даты
  • MINUTE (МИНУТЫ) – выдает значение минут из любой даты
  • NETWORKDAYS (ЧИСТРАБДНИ) – вычисляет количество рабочих дней между двумя датами
  • NOW (ТДАТА) – выдает текущую дату и время
  • SECOND (СЕКУНДЫ) – преобразует дату в секунды
  • TODAY (СЕГОДНЯ) – выдает текущую дату
  • WEEKDAY (ДЕНЬНЕД) – выдает порядковое число дня недели (от 1 до 7)
  • WORKDAY (РАБДЕНЬ) – рассчитывает отстающую от данной на заданное количество рабочих дней
  • DATEDIF (РАЗНДАТ) – если вы хотите подсчитать количество лет, месяцев или дней между двумя указанными датами. Хорошим примером может быть вычисление возраста.
  • AND (И) – может быть использована, если вы хотите проверить несколько условий
  • FALSE (ЛОЖЬ) – вставляет логическое значение ЛОЖЬ
  • IF (ЕСЛИ) – выполняет проверку условий
  • IFERROR (ЕСЛИОШИБКА) – лучшая формула для коррекции отображения вычислений с ошибками
  • NOT (НЕ) – меняет на противоположное логическое значение своего аргумента.
  • OR (ИЛИ) – проверяет несколько аргументов
  • TRUE (ИСТИНА) – добавляет логическое значение ИСТИНА
  • COLUMN (СТОЛБЕЦ) – определяет номер столбца конкретной ячейки
  • COLUMNS (ЧИСЛСТОЛБ) – определяет количество столбцов в определенном диапазоне данных
  • HLOOKUP (ГПР) – определяет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке
  • INDEX (ИНДЕКС) – определяет значение ссылки или массива по индексу
  • INDIRECT (ДВССЫЛ) – преобразует ссылки написанные как текст, в действующие вычисления из этих ссылок
  • MATCH (ПОИСКПОЗ) – определяет значение в ссылке или массиве
  • OFFSET (СМЕЩ) – определяет смещение ссылки относительно заданной ячейки
  • ROW (СТРОКА) – определяет номер строки ячейки
  • ROWS (ЧСТРОК) определяет количество строк в заданном диапазоне данных
  • VLOOKUP (ВПР) – ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце

Статистические функции в Excel

  • RANK (РАНГ) – определяет ранг числа в списке чисел
  • AVERAGE (СРЗНАЧ) – определяет среднее значение между двумя аргументами
  • AVERAGEIF (СРЗНАЧЕСЛИ) – определяет среднее значение между двумя аргументами по заданному условию
  • AVERAGEIFS (СРЗНАЧЕСЛИМН) – определяет среднее значение между двумя аргументами по заданным условиям
  • COUNT (СЧЕТ) – определяет количество чисел в списке аргументов
  • COUNTA (СЧЕТЗ) – подсчитывает количество значений в списке аргументов
  • COUNTBLANK (СЧИТАТЬПУСТОТЫ) – определяет количество пустых ячеек в заданном диапазоне
  • COUNTIF (СЧЕТЕСЛИ) – определяет количество чисел в диапазоне по заданному критерию
  • COUNTIFS (СЧЕТЕСЛИМН) – определяет количество чисел в заданном диапазоне по заданным критериям
  • LARGE (НАИБОЛЬШИЙ) – находит порядковое (например, 3-е) наибольшее значение из множества данных
  • MAX (МАКС) – определяет максимальное значение из списка аргументов
  • MIN (МИН) – определяет минимальное значение из списка аргументов
  • SMALL (НАИМЕНЬШИЙ) – находит порядковое (например, 3-е) наименьшее значение из множества данных

Текстовые функции в Excel

  • CONCATENATE (СЦЕПИТЬ) – объединяет 2 и более буквы или строчки
  • FIND (НАЙТИ) – ищет совпадение одного текста с другим (с учетом регистра)
  • LEFT (ЛЕВИСМВ) – извлекает текст из левой части строки в заданном количестве символов
  • LEN (ДЛСТР) – определяет количество знаков в заданной строке
  • LOWER (СТРОЧН) – преобразует все буквы в текстовой строке в строчные
  • MID (ПСТР) – выдает определенное число знаков из строки текста, начиная с указанной позиции
  • PROPER (ПРОПНАЧ) – преобразует в каждом слове текста первую букву в прописную
  • REPLACE (ЗАМЕНИТЬ) – заменяет знаки в тексте
  • REPT (ПОВТОР) – повторяет текст заданное количество раз
  • RIGHT (ПРАВСИМВ) – извлекает текст из правой части строки в заданном количестве символов
  • SEARCH (ПОИСК) – ищет совпадение одного текста с другим (без учета регистра)
  • SUBSTITUTE (ПОДСТАВИТЬ) – заменяет в текстовой строке старый текст новым
  • TEXT (ТЕКСТ) – форматирует числов в текстовый формат
  • TRIM (СЖПРОБЕЛЫ) – удаляет из текста строки лишние пробелы
  • UPPER (ПРОПИСН) – преобразует все буквы текста в прописные

Поделиться с друзьями:

excelhack.ru

Адреса ячеек excel 2010 - Хитрости работы с Excel – таблицы, функции, формулы, списки, рабочие книги, макросы

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

Но, прежде чем разбираться с назначением адресов, давайте подумаем об именах ячеек. Почему и сообразно каким правилам они возникают? Если вы заглянете в excel 2010, то в программе вас встретят бесчисленные клеточки, а вверху каждой верхней строки, вверху всех ячеечных строк, можно увидеть латинские буквы. Так вот, эти буквы являются первой частью ячеечного адреса. И если вы чайник-екзельщик, вполне возможно, вы тоже уже поняли простую вещь: писать имена ячеек русскими буквами – не самое лучшее решение, потому как формула просто перестанет работать. Правило: имена ячейки пишем только латиницей.

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

А как же отличаются относительные и абсолютные ссылки? Отличаются они видом: в адресе абсолютной ссылки есть знак доллара - $, причем, посмотрите внимательно, если этот знак находится перед буквенным символом, то это значит, что ссылка стоит на столбец, а не на запись и столбец. То есть, если абсолютная ссылка на ячейку включает в себя абсолютность в отношении столбика и строки, то адрес будет выглядеть примерно таким образом: $V$9.

А относительную ссылку отличает отсутствие значка доллара, и ссылка выглядит просто – V9. Но помимо «чистых» типов – относительного и абсолютного, есть и смешанный тип адреса ячейки.

Грубо говоря, разница заключается в изменении формул при копировании, если там, в формуле есть относительный адрес. То есть, если формула в ячейке В будет выглядеть как = D9*9, то скопировав формулу в другую ячейку, она изменится. Если происходит копирование формулы в другой столбик, то изменяется буква. А если мы переносим формулу в другую строчку, то изменяться уже будет цифра, это стоит иметь ввиду. То есть, если адрес ячейки абсолютен, то и формула меняться не будет. А если адрес абсолютен наполовину, то будет меняться относительное значение.

Newer news items:

Older news items:

www.teachexcel.ru

Функция ЯЧЕЙКА() в MS EXCEL. Примеры и методы

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

Синтаксис функции ЯЧЕЙКА()

ЯЧЕЙКА(тип_сведений, [ссылка])

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

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

Тип_сведений Возвращаемое значение
"адрес" Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки.
"столбец" Номер столбца ячейки в аргументе «ссылка».
"цвет" 1, если ячейка изменяет цвет при выводе отрицательных значений; во всех остальных случаях — 0 (ноль).
"содержимое" Значение левой верхней ячейки в ссылке; не формула.
"имяфайла" Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка ("").
"формат" Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-». Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()».
"скобки" 1, если положительные или все числа отображаются в круглых скобках; во всех остальных случаях — 0.
"префикс" Текстовое значение, соответствующее префиксу метки ячейки. Апостроф (') соответствует тексту, выровненному влево, кавычки (") — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта (\) — тексту с заполнением, пустой текст ("") — любому другому содержимому ячейки.
"защита" 0, если ячейка разблокирована, и 1, если ячейка заблокирована.
"строка" Номер строки ячейки в аргументе «ссылка».
"тип" Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому значению.
"ширина" Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера.

Использование функции

В файле примера приведены основные примеры использования функции:

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только VBA.

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

Обратите внимание, что если в одном экземпляре MS EXCEL (см. примечание ниже) открыто несколько книг, то функция ЯЧЕЙКА() с аргументами адрес и имяфайла, будет отображать имя того файла, с который Вы изменяли последним. Например, открыто 2 книги в одном окне MS EXCEL: Базаданных.xlsx и Отчет.xlsx. В книге Базаданных.xlsx имеется формула =ЯЧЕЙКА("имяфайла") для отображения в ячейке имени текущего файла, т.е. Базаданных.xlsx (с полным путем и с указанием листа, на котором расположена эта формула). Если перейти в окно книги Отчет.xlsx и поменять, например, содержимое ячейки, то вернувшись в окно книги Базаданных.xlsx (CTRL+TAB) увидим, что в ячейке с формулой =ЯЧЕЙКА("имяфайла") содержится имя Отчет.xlsx. Это может быть источником ошибки. Хорошая новость в том, что при открытии книги функция пересчитывает свое значение (также пересчитать книгу можно нажав клавишу F9). При открытии файлов в разных экземплярах MS EXCEL - подобного эффекта не возникает - формула =ЯЧЕЙКА("имяфайла") будет возвращать имя файла, в ячейку которого эта формула введена.

Примечание: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш CTRL+TAB - будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Другие возможности функции ЯЧЕЙКА(): определение типа значения, номера столбца или строки, мало востребованы, т.к. дублируются стандартными функциями ЕТЕКСТ(), ЕЧИСЛО(), СТОЛБЕЦ() и др.

excel2.ru

Excel. Примеры использования функции ДВССЫЛ (INDIRECT)

Функция ДВССЫЛ (INDIRECT) — одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми. По сути, если в формуле есть раздел ДВССЫЛ со ссылкой на ячейку, эта ссылка обрабатывается как содержимое соответствующей ячейки. [1] Например (рис. 1), в ячейке С4 я ввел формулу =ДВССЫЛ(А4), и Excel возвратил значение, равное 6. Excel возвращает именно это значение, поскольку ссылка на А4 немедленно заменяется текстовой строкой В4. Следовательно, формула обрабатывается как =В4, что дает нам 6. По аналогии, если ввести в ячейке С5 формулу =ДВССЫЛ(А5), Excel вернет значение ячейки В5, то есть 9.

Рис. 1. Простой пример функции ДВССЫЛ

Скачать заметку в формате Word или pdf, также доступны примеры и Задание_3 в формате Excel2013

1. Формулы моей электронной таблицы часто содержат ссылки на ячейки, диапазоны или на то и на другое вместе. Можно ли не изменять ссылки в формулах, а поместить их в отдельные ячейки, чтобы редактировать ссылки на ячейки и диапазоны, не затрагивая при этом формулы?

В диапазоне ячеек В4:Н16 (рис. 2) приведены данные о ежемесячных продажах шести товаров за 12 месяцев. Сейчас я подсчитаю общие продажи каждого товара за месяцы со 2 по 12. Простейший способ подсчитать это — скопировать формулу СУММ(С6:С16) из ячейки С18 в диапазон D18:h28. Предположим, вам потребовалось изменить месяцы, по которым производится подсчет. Скажем, вы решите подсчитать общие продажи за месяцы 3–12. Можно изменить формулу в ячейке С18 на СУММ(С7:С16) и затем скопировать ее в диапазон D18:h28. Однако может быть не всегда удобно, поскольку вам приходится копировать формулу из ячейки С18 в диапазон D18:h28, и, не просматривая формул, никто не узнает, какие строки суммируются.

Рис. 2. Функция ДВССЫЛ позволяет изменять ссылки на ячейки в формулах, не изменяя сами формулы

Функция ДВССЫЛ предлагает другое решение. Я указал в ячейках D2 и Е2 номера начальной и конечной суммируемых строк. Теперь, при использовании функции ДВССЫЛ, мне достаточно изменить значения в ячейках D2 и Е2, чтобы конечная сумма обновилась, включив только те строки, которые мы хотим. Кроме того, значения ячеек D2 и Е2 наглядно показывают, какие строки (месяцы) суммируются! Все, что мне требуется, — скопировать из ячейки С18 в диапазон D18:h28 формулу =СУММ(ДВССЫЛ(C$3&$D$2&»:»&C$3&$E$2)). Каждая ссылка на ячейку в этой формуле обрабатывается как содержимое соответствующей ячейки. С$3 обрабатывается как С, $D$2 — как 6, а $Е$2 — как 16. Используя символ конкатенации — & (сцепления), Excel обрабатывает эту формулу как СУММ(С6:С16), что нам и требуется. Формула в ячейке D18 обрабатывается как СУММ(D6:D16), что также дает нужный нам результат. Конечно, если нам захочется просуммировать продажи, скажем, с 4 по 6 месяц, мы просто введем 8 в ячейку D2 и 10 в ячейку Е2. После этого формула в ячейке С18 вернет 33 + 82 + 75 = 190.

2. В ячейке В1 книги Excel, начиная с Лист1 и заканчивая Лист7 (рис. 3) содержатся данные о продажах товара за месяц. Есть ли какой-нибудь простой способ написать и скопировать формулу, которая выводила бы данные о продажах этого товара за каждый месяц на одном листе?

Рис. 3. Данные о продажах товара за 1–7 месяц, выведенные с помощью функции ДВССЫЛ

Предположим, Лист1 содержит данные о продажах за первый месяц, Лист2 — за второй и т.д. Пусть в первом месяце продажи равны 1. Например, вы хотите вывести продажи за все месяцы на одном листе. Нудный способ — подсчитать продажи за первый месяц с помощью формулы =Лист1!В1 продажи за второй месяц с помощью формулы =Лист2!В1 и т.д. Если ваши данных охватывают 100 месяцев, такое решение грозит грандиозной головной болью. Гораздо более изящный способ — вывести данные о продажах за первый месяц в ячейке С4 листа Лист1 с помощью формулы =ДВССЫЛ($A$4&B4&»!B1″), Excel обработает $A$4 как «Лист», В4 — как 1, и «!B1» — как строку текста !В1. Формула целиком будет обработана как =Лист1!В1, то есть покажет данные о продажах за первый месяц, содержащиеся в ячейке В1 листа Лист1. Скопировав эту формулу в диапазон С5:С10, вы отобразите содержимое ячейки В1 листов со 2 по 7. Обратите внимание: при копировании формулы из ячейки С4 в ячейку С5 ссылка на В4 заменяется ссылкой на В5, и формула в ячейке С5 возвращает значение ячейки Лист2!В1 и т.д.

3. Предположим, я суммирую значения из диапазона А5:А10 посредством формулы СУММ(А5:А10). Если вставить где-нибудь между 5 и 10 строками пустую строку, формула автоматически изменится на СУММ(А5:А11). Можно ли написать формулу, которая при вставке пустой строки между 5 и 10 строками все равно суммировала бы значения из диапазона А5:А10?

Рис. 4 иллюстрирует несколько способов сложения чисел из диапазона А5:А10. В ячейке А12 я ввел обычную формулу СУММ(А5:А10). Аналогичным образом формула СУММ($А$5:$А$10) в ячейке С9 тоже возвращает значение 33. Тем не менее, если вставить строку между 5 и 10 строками, обе формулы попытаются сложить ячейки диапазона А5:А11.

Функция ДВССЫЛ (INDIRECT) предоставляет, как минимум, два способа сложения значений из диапазона А5:А10. В ячейке D9 я ввел формулу =СУММ(ДВССЫЛ(«A5:A10»)). Excel обрабатывает ДВССЫЛ(«A5:A10») как строку текста «A5:A10», и поэтому, даже если я добавлю строку в электронную таблицу, формула по-прежнему будет суммировать значения из диапазона А5:А10.

Рис. 4. Несколько способов сложения значений ячеек из диапазона А5:А10; под значением суммы написана формула

Еще один вариант сложения значений из диапазона А5:А10 при помощи функции ДВССЫЛ – формула =СУММ(ДВССЫЛ(«A»&C4&»:A»&D4)), которую я ввел в ячейке С5. Excel обрабатывает ссылку на С3 как 5, а ссылку на 3 — как 10, в результате чего формула преобразуется в СУММ(А5:А10). Вставка пустой строки между 5 и 10 строками никак не скажется на формуле, поскольку ссылка на С3 по-прежнему будет обрабатываться как 5, а ссылка на D3 — как 10. На рис. 5 показаны результаты суммирования, выполненные посредством наших четырех формул после того, как ниже 7-й строки была добавлена пустая строка.

Рис. 5. Результаты, возвращенные формулами СУММ после того, как ниже строки 7 была вставлена пустая строка

Обратите внимание: классические формулы СУММ, не включающие оператор ДВССЫЛ, автоматически изменились и суммируют значения из диапазона А5:А11, по-прежнему возвращая значение 33. Две формулы СУММ, включающие оператор ДВССЫЛ, суммируют значения из диапазона А5:А10, в результате при вычислениях теряется число 2 (оно теперь находится в ячейке A11). Формулы СУММ, содержащие оператор ДВССЫЛ, возвращают значение 31.

Контрольные задания (с ответами)

1. Функция АДРЕС возвращает адрес ячейки, сопоставленный со строкой и столбцом. Например, формула АДРЕС(3;4) возвращает $D$3. Что вернет формула =ДВССЫЛ(АДРЕС(3;4))?

2. Лист Задание_2 содержит данные о продажах пяти товаров в четырех регионах. С помощью функции ДВССЫЛ создайте формулы, которые позволят легко суммировать общие продажи любых последовательно пронумерованных товаров, например Товар 1—Товар 3, Товар 2—Товар 5 и т.д.

3. Книга Excel Задание_3 содержит шесть листов. На листе Лист1 записаны данные о продажах товаров за первый месяц. Эти данные всегда указываются в диапазоне Е5:Н5. Используя функцию ДВССЫЛ, составьте таблицу с информацией о продажах каждого товара по месяцам на отдельном листе.

Ответы

1. Формула =ДВССЫЛ(АДРЕС(3;4)) вернет значение ячейки $D$3.

2. Фрагмент формулы ДВССЫЛ(«B»&B8+1&»:»&»E»&B9+1) возвращает диапазон В3:Е5:

3. В ячейке Е9 записана формула =ДВССЫЛ($B$8&$D9&»!»&E$7&5), которая возвращает значение ячейки Лист1!Е5:

Возможно, вас также заинтересует:

Excel. Использование ДВССЫЛ для транспонирования строк в столбцы с сохранением формул

Как обойти ограничение Excel и сделать выпадающий список зависимым

Excel. Сумма цифр целого числа

[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel. Анализ данных и построение бизнес-моделей, глава 22.

baguzin.ru