Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Формулы


Rambler's Top100


Counter CO.KZ


Примечание : Наличие фигурных скобок {...} означает, что речь идёт о формулах массива



    [1] [2] [3]

  1. Как с помощью формул преобразовать "число", где в качестве разделителя используется точка, в число с запятой ? 09.09.2007
  2. Как получить первый и последний день недели (понедельник, воскресенье), исходя из указанной даты ? 12.09.2007
  3. Как получить дату отстоящую на заданное число месяцев от начальной даты ? 26.03.2011
  4. Как с помощью формул получить наименьшую и наибольшую дату ? 12.09.2007
  5. Как определить наиболее часто встречающийся в ячейке символ ? 24.06.2008
  6. Как перемножить значения ячеек, если ячейки могут содержать текст ? 06.09.2009
  7. Как ссылаться на ячейку, чтобы ссылка не менялась ни при каких условиях ? 23.01.2010
  8. Как создать ссылку на ячейку с использованием переменной (имя книги, листа, номер строки и т.п.) ? 25.03.2011
  9. Как с помощью формул вывести "число" с заданным числом десятичных знаков ? 25.03.2011
  10. Как получить абсолютное значение числа ? 03.04.2011
  11. Как получить остаток от деления ? 25.03.2012
  12. Kак перевести Цельсий в Фаренгейт, и наоборот, Фаренгейт в Цельсий ? NEW 21.02.2016
    [1] [2] [3]


  • Ответ :

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

    =ЗНАЧЕН(ПОДСТАВИТЬ(A1;".";","))
    =ПОДСТАВИТЬ("123.456";".";",")*1
    =ПОДСТАВИТЬ("123.456";".";",")/1
    =ПОДСТАВИТЬ("159.217";".";",")+0
    =ПОДСТАВИТЬ("159.217";".";",")-0


    Комментарий : Этот совет может быть актуален только для тех, у кого в качестве разделителя целой и дробной части используется запятая ,
  • Ответ :

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

    =A1-ДЕНЬНЕД(A1;2)+1
    =СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();2)+1
    =СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();3)

    =A1+7-ДЕНЬНЕД(A1;2)
    =СЕГОДНЯ()+7-ДЕНЬНЕД(СЕГОДНЯ();2)


    Предполагается, что : ячейка A1 содержит дату.
  • Ответ :

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

    Вариант I.

    =ДАТАМЕС(A1;C1)


    Примечание : Если функция =ДАТАМЕС() возвращает значение ошибки #ИМЯ? или #ЗНАЧ!, то Вам необходимо установить надстройку Пакет Анализа [См. примечание]

    Вариант II.

    =ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1;МИН(ДЕНЬ(A1);ДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1+1;1)-1)))
    =ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1;МИН(ДЕНЬ(A1);ДЕНЬ(ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1+1;0))))
    =МИН(ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1;ДЕНЬ(A1));ДАТА(ГОД(A1);МЕСЯЦ(A1)+C1+1;1)-1)


    Предполагается, что : ячейка A1 содержит дату, а в ячейке C1 находится необходимое количество месяцев, которое может быть как положительным, так и отрицательным числом.
  • Ответ :

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

    =МИН(A2:A100)
    =НАИМЕНЬШИЙ(A2:A100;1)

    =МАКС(A2:A100)
    =НАИБОЛЬШИЙ(A2:A100;1)


    Примечание : При необходимости, ячейку содержащую формулу, можно отформатировать, т.е. установить нужный формат [FAQ], например ДД.ММ.ГГГГ
  • Ответ :

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

    =СИМВОЛ(МОДА(КОДСИМВ(ПСТР(C3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(C3)));1))))
    =СИМВОЛ(МОДА(КОДСИМВ(ПСТР(C3;СТРОКА(C1:ИНДЕКС(C:C;ДЛСТР(C3)));1))))
    =СИМВОЛ(МОДА(КОДСИМВ(ПСТР(C3;СТРОКА(ИНДЕКС(C:C;1):ИНДЕКС(C:C;ДЛСТР(C3)));1))))
    =ПСТР(C3;МОДА(НАЙТИ(ПСТР(C3;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(C3)));1);C3));1)
    =ПСТР(C3;МОДА(НАЙТИ(ПСТР(C3;СТРОКА(C1:ИНДЕКС(C:C;ДЛСТР(C3)));1);C3));1)
    =ПСТР(C3;МОДА(НАЙТИ(ПСТР(C3;СТРОКА(ИНДЕКС(C:C;1):ИНДЕКС(C:C;ДЛСТР(C3)));1);C3));1)


    Предполагается, что : в ячейке C3 находятся нужные данные, содержащие повторяющиеся символ(ы).

    Примечание : Обратите внимание на то, что добавление/удаление строк может привести к тому, что вторая формула возвратит результат, отличный от ожидаемого.
    В этом отношении первая формула является более универсальной, однако, наличие стандартной функции рабочего листа ДВССЫЛ() делает эту формулу пересчитываемой(volatile), а значит первая формула будет пересчитываться при изменении данных в любом рабочем листе любой открытой рабочей книги (Сервис-Параметры-Вычисления-Автоматически ...)

    Совет : Если необходимо игнорировать регистр, т.е. буквы A и a должны считаться одним символом, то либо замените во втором варианте функцию НАЙТИ() на ПОИСК(), либо преобразуйте исходный текст к верхнему/нижнему регистру используя функции ПРОПИСН()/СТРОЧН()
  • Ответ :

    Для того, чтобы перемножить значения нескольких ячеек, пусть для примера, это будут ячейки столбца C (Цена) и D (Кол-во), можно воспользоваться следующими формулами :

    =C2*D2
    =ПРОИЗВЕД(C2;D2)


    Однако, если любая из ячеек будет содержать текст (а такое встречается, когда фирма хочет подчеркнуть отсутствие товара и пишет, к примеру, "-" или "нет" или "временно отсутствует"), то в результате первая формула возвратит значение ошибки #ЗНАЧ!, а вторая одно из перемножаемых чисел (или 0, если текст это и цена и кол-во)
    И если Вы столкнулись именно с такой таблицей, то в таком случае можно просто немного изменить формулы, т.е. воспользоваться первым вариантом, если Вы уверены, что текст может содержать только столбец D, или вторым вариантом, если такой уверенности нет.

    Вариант I.

    =C2*Ч(D2)

    =ЕСЛИ(ЕЧИСЛО(D2);C2*D2;0)
    =C2*ЕСЛИ(ЕЧИСЛО(D2);D2;0)

    =ЕСЛИ(ТИП(D2)=1;C2*D2;0)
    =C2*ЕСЛИ(ТИП(D2)=1;D2;0)

    =ЕСЛИ(СЧЁТ(D2)=1;C2*D2;0)


    Вариант II.

    =Ч(C2)*Ч(D2)
    =ЕСЛИ(СЧЁТ(C2;D2)=2;C2*D2;0)
    =ЕСЛИ(ЕЧИСЛО(C2*D2);C2*D2;0)
    =ЕСЛИ(ТИП(C2*D2)=1;C2*D2;0)

  • Ответ :

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

    Вариант I.

    =ДВССЫЛ("A3")

    =ДВССЫЛ("Лист1!A3")
    =ДВССЫЛ(АДРЕС(3;1;;;"Лист1"))


    Комментарий : В данном примере предполагается, что в рабочей книге, содержащей подобные формулы, присутствует рабочий лист с именем "Лист1" и это имя, в дальнейшем, меняться не будет.

    =ДВССЫЛ("[Имя_открытой_рабочей_книги.xls]Лист1!A3")
    =ДВССЫЛ(АДРЕС(3;1;;;"[Имя_открытой_рабочей_книги.xls]Лист1"))


    Комментарий : В этом примере также предполагается, что в рабочей книге, содержится рабочий лист "Лист1", кроме того рабочая книга обязательно должна быть открыта.

    Вариант II.

    =ИНДЕКС(1:65536;3;1)

    =ИНДЕКС(Лист1!1:65536;3;1)

    =ИНДЕКС([Имя_открытой_рабочей_книги.xls]Лист1!$1:$65536;3;1)

    =ИНДЕКС('C:\Папка\[Имя_закрытой_рабочей_книги.xls]Лист1'!$1:$65536;3;1)


    Комментарий : Используя этот вариант Вы можете получить данные (но не ссылку) ячейки/диапазона закрытой рабочей книги. В MS Excel 95 необходимо заменить 65536 на 16384

    Вариант III.

    В меню Вставка выберите пункт Имя, далее выберите команду Присвоить. В диалоговом окне Присвоение имени в поле Имя введите нужное имя, например ССЫЛКА, а в поле Формула введите следущую формулу :

    =!$A$3

    и нажмите кнопку ОК. Затем, выберите нужную ячейку и введите именованную формулу =ССЫЛКА

    Комментарий : Подобная формула не будет пересчитываться при изменении данных в ячейке, однако, этого можно избежать, если немного изменить формулу =СМЕЩ(!$A$3;0;0)
  • Ответ :

    Для того, чтобы создать ссылку на ячейку с использованием переменных : имя листа, номер строки и буквенное обозначение столбца, достаточно воспользоваться стандартной функцией рабочего листа =ДВССЫЛ()

    К примеру, если в рабочей книге, содержащей нижеприведённые формулы, существует рабочий лист с именем Лист№1 (и это имя, в дальнейшем, меняться не будет) и нам необходимо получить значение его ячейки A7, то в таком случае, введите необходимые данные() в указанные ячейки и примените следующую формулу :

    =ДВССЫЛ("'"&A1&"'!"&A2&A3)
    =ДВССЫЛ(СЦЕПИТЬ("'";A1;"'!";A2;A3))


    Предполагается, что :
  • Ячейка A1 содержит имя листа - Лист№1
  • Ячейка A2 содержит название столбца - A
  • А ячейка A3 содержит номер строки - 7

    Вариант II.
    Если столбец также необходимо указать в виде номера и/или Вы не хотите явно указывать апострофы, хотя в предыдущем варианте они необходимы, то в таком случае, можно просто добавить стандартную функцию =АДРЕС()

    =ДВССЫЛ(АДРЕС(A3;A2;;;A1))

    Предполагается, что :
  • Ячейка A1 содержит имя листа - Лист№1
  • Ячейка A2 содержит номер столбца - 1
  • А ячейка A3 содержит номер строки - 7

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

    =ДВССЫЛ("'["&A1&"]"&A2&"'!"&A3)
    =ДВССЫЛ(АДРЕС(A4;A5;;;"["&A1&"]"&A2))
    =ДВССЫЛ(АДРЕС(4;2;;;"["&A1&"]"&A2))


    В этих примерах предполагается, что :
  • A1 содержит имя книги - Архив.xls
  • A2 содержит имя листа - Расходы 2005
  • A3 содержит адрес ячейки - B4
  • A4 содержит номер строки - 4 (формула#2)
  • A5 содержит номер столбца - 2 (формула#2)
  • Ответ :

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

    Вариант I.

    =ФИКСИРОВАННЫЙ(15,789;5)
    =ФИКСИРОВАННЫЙ(A1;B1)


    Вариант II.

    =ТЕКСТ(15,789;","&ПОВТОР(0;5))
    =ТЕКСТ(A1;","&ПОВТОР(0;B1))


    Предполагается, что :
  • Ячейка A1 содержит исходное число
  • Ячейка B1 содержит число = необходимое количество знаков, после запятой

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

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

    =ABS(A1)
    =A1*ЗНАК(A1)
    =ЕСЛИ(A1>0;A1;-A1)
    =ЕСЛИ(A1>0;A1;A1*-1)
    =A1*ЕСЛИ(A1<0;-1;1)


    Предполагается, что :
  • Ячейка A1 содержит число, модуль которого необходимо получить
  • Ответ :

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

    =ОСТАТ(A1;A2)
    =A1-A2*ЦЕЛОЕ(A1/A2)


    Предполагается, что :
  • Ячейка A1 содержит число, остаток от деления которого необходимо получить
  • Ячейка A2 содержит делитель, т.е. число, на которое нужно разделить.

    Комментарий : Несмотря на то, что вторая формула более "громоздкая", она позволяет получить результат даже в тех случаях, когда стандартная функция рабочего листа =ОСТАТ() возвращает значение ошибки #ЧИСЛО!
  • Ответ :

    Перевод Цельсий °C в Фаренгейт °F :

    Вариант I.

    =A1*1,8+32


    Вариант II.

    =ПРЕОБР(A1;"C";"F")
    =ПРЕОБР(A1;"cel";"fah")


    Перевод Фаренгейт °F в Цельсий °C :

    Вариант I.

    =(B1-32)/1,8


    Вариант II.

    =ПРЕОБР(B1;"F";"C")
    =ПРЕОБР(B1;"fah";"cel")


    Предполагается, что :
  • Ячейки A1 и B1 содержат градусы(числа), которые необходимо перевести(преобразовать) из одной системы в другую.

    Примечание : Если функция =ПРЕОБР() возвращает значение ошибки #ИМЯ? или #ЗНАЧ!, то Вам необходимо установить надстройку Пакет Анализа [См. примечание]


    Вопросы - Синонимы
  • Как к определённой дате прибавить/вычесть нужное количество месяцев ?


  • Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

  • © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff