Microsoft Excel:

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


Rambler's Top100


Counter CO.KZ


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



    [1] [2] [3]

  1. Как с помощью формул вывести в ячейке название дня в виде текста ? Лидер голосования 24.08.2005
  2. Как с помощью формул вывести в ячейке название месяца в виде текста ? Лидер голосования 24.08.2005
  3. Как с помощью формул возвращать случайное число, например в промежутке от 17 до 59 ? 22.03.2006
  4. Как с помощью формул возвращать случайное число, "состоящее" из нужного количества повторящихся цифр ? 20.05.2007
  5. Как с помощью формул возвращать случайное число, а также контролировать этот процесс ? 06.05.2007
  6. Как с помощью формул определить последний день и последнее число любого месяца ? 22.03.2006
  7. Как с помощью формул получить первый день любого месяца ? 30.05.2009
  8. Как с помощью формул определить приходится ли указанная дата на выходные (суббота и воскресенье) ? 09.05.2007
  9. Как с помощью формул узнать количество дней прошедших с начала года ? 25.01.2007
  10. Kак получить дату отстоящую на заданное число дней (без использ. функций) ? 17.02.2007
  11. Kак прибавить/вычесть нужное количество секунд, минут, часов (без использ. функций) ? 30.04.2007
  12. Kак прибавить/вычесть нужное количество секунд, минут, часов (с применением стандартных функций рабочего листа) ? 30.04.2007
  13. Kак получить время, если известен час, минута и секунда ? 04.04.2012
  14. Как с помощью формул удалить все непечатаемые символы из текста ? 12.03.2007
  15. Как с помощью формул удалить все лишние пробелы из текста ? 03.05.2007
  16. Как с помощью формул удалить все пробелы из текста ? 21.10.2010
  17. Как с помощью формул сделать прописной первую букву ? 09.05.2007
  18. Как с помощью формул сделать прописной только первую букву, а все остальные буквы заменить на строчные ? 09.05.2007
  19. Как с помощью формул в каждом слове сделать прописной первую букву, заменив все остальные на строчные, а также удалить лишние пробелы ? 09.05.2007
  20. Как с помощью формул подсчитать количество слов в тексте (предложении) ? 06.05.2007
  21. Как с помощью формул создать строку, повторяющую текст заданное число раз ? 30.08.2006
  22. Как с помощью формул перевернуть строку или число, т.е. преобразовать lecxE SM в MS Excel ? 02.02.2007
  23. Как с помощью формул получить число миллисекунд, прошедших с момента запуска Microsoft Windows ? 02.02.2007
  24. Как с помощью формул получить отсортированный список числовых значений ? 13.02.2007
  25. Как с помощью формул получить отсортированный список текстовых значений ? 15.05.2010
  26. Как с помощью формул определить сколько цифр содержит число ? 15.07.2007
  27. Как с помощью формул определить содержит ли ячейка формулу ? 21.02.2007
  28. Как с помощью формул получить текст формулы, расположенной в нужной ячейке ? 20.02.2007
  29. Как с помощью формул определить версию приложения ? 14.07.2006
  30. Как с помощью формул узнать режим вычислений ? 12.12.2006
  31. Как с помощью формул узнать количество рабочих листов во всех открытых рабочих книгах ? 20.02.2007
  32. Как с помощью формул получить адрес самой верхней видимой ячейки ? 03.04.2007
  33. Как с помощью формул получить адрес выделенного диапазона ? 12.07.2006
  34. Как с помощью формул проверить пересекаются или нет диапазоны ? 12.12.2006
  35. Как с помощью формул определить количество несмежных ячеек/диапазонов в указанной ссылке ? 19.02.2007
  36. Как получить значение последнего столбца из массива в стандартной функции =ВПР() ? 21.03.2007
  37. Как получить значение последней строки из массива в стандартной функции =ГПР() ? 21.03.2007
  38. Kак вычислить среднее значение, но без учёта ячеек содержащих нуль (ноль) ? 13.05.2007
  39. Kак вычислить среднее значение, но только для положительных числовых значений ? 13.05.2007
  40. Kак вычислить минимум, максимум, но без учёта ячеек содержащих нуль (ноль) ? 10.10.2010
  41. Kак определить наиболее часто повторяющееся числовое значение ? 17.05.2007
  42. Kак подсчитать количество ячеек содержащих определённое количество символов в тексте ? 28.12.2006
  43. Как подсчитать количество пустых ячеек в указанном диапазоне ? 28.12.2006
  44. Как подсчитать количество уникальных числовых значений в нужном диапазоне ? 19.02.2007
  45. Как подсчитать количество уникальных текстовых значений в нужном диапазоне ? 19.02.2007
  46. Как подсчитать общее количество уникальных значений ? 19.02.2007
  47. Как определить наличие/отсутствие повторяющихся значений в указанном диапазоне ? 15.03.2007
  48. Как об'единить ячейки содержащие дату или числа (например 00,55 или 55,00)
    Как сцепить данные с учётом формата ячейки ?
    10.09.2006
    [1] [2] [3]


  • Ответ :

    Вариант I. Получение целого числа в диапазоне от 17 до 59

    =СЛУЧМЕЖДУ(17;59)


    Примечание : Если функция =СЛУЧМЕЖДУ() возвращает значение ошибки #ИМЯ? или #ЗНАЧ!, то Вам необходимо установить надстройку Пакет Анализа. Для этого в меню Сервис выберите команду Надстройка, затем в списке Список надстроек: установите "флажок" напротив Пакет анализа и нажмите кнопку OK.

    Вариант II. Получение числа в диапазоне от 17 до 59

    =СЛЧИС()*(59-17)+17


  • Получение целого числа в диапазоне от 17 до 59

    =ЦЕЛОЕ(СЛЧИС()*(59-17)+17)
    =ОТБР(СЛЧИС()*(59-17)+17)
    =ОКРУГЛВНИЗ(СЛЧИС()*(59-17)+17;0)


  • Получение целого числа в диапазоне от 17 до 59 (включительно)

    =ЦЕЛОЕ(СЛЧИС()*(59-17+1)+17)
    =ОТБР(СЛЧИС()*(59-17+1)+17)
    =ОКРУГЛВВЕРХ(СЛЧИС()*(59-17);0)+17


    Последняя формула возвратит случайное число, равное минимальному числу заданного диапазона, только при условии, что стандартная функция рабочего листа =СЛЧИС() возвратит ноль.
  • Ответ :

    Вариант I.

    =ЗНАЧЕН(ПОВТОР(СЛУЧМЕЖДУ(1;9);A1))
    =ПОВТОР(СЛУЧМЕЖДУ(1;9);A1)*1
    =ПОВТОР(СЛУЧМЕЖДУ(1;9);A1)+0


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

    Вариант II.

    =ЗНАЧЕН(ПОВТОР(ЦЕЛОЕ(СЛЧИС()*9)+1;A1))
    =ПОВТОР(ЦЕЛОЕ(СЛЧИС()*9)+1;A1)*1
    =ПОВТОР(ОТБР(СЛЧИС()*9)+1;A1)-0


    Предполагается, что :
    Ячейка A1 содержит целое число (от 1 до 15), которое определяет количество повторящихся цифр в возвращаемом значении. Если же количество цифр является константой, то можно обойтись без использования ячейки, кроме того, в этом случае можно воспользоваться более простыми вариантами :

    =СЛУЧМЕЖДУ(1;9)*11111
    =ЦЕЛОЕ(СЛЧИС()*9+1)*11111

  • Ответ :

    Вариант I. Актуально для MS Excel 97, 2000, XP
    1. Выделите ячейку, которая будет содержать данные, определяющие должна ли формула, каждый раз возвращать случайное число, или нет. Для примера, пусть это будет ячейка B1
    2. В меню Данные выберите команду Проверка, далее закладку Параметры и в списке Тип данных выберите Список. После чего, в поле Источник введите, например : ИСТИНА;ЛОЖЬ
    3. Введите в ячейку A1 формулу =ЕСЛИ(B1;СЛЧИС();A1)
    4. После того, как Excel выдаст сообщение о циклической ошибке, нажмите кнопку ОК.
    5. В меню Сервис выберите команду Параметры, далее закладку Вычисления, потом установите "флажок" итерации, а в поле Предельное число итераций введите 1 и нажмите кнопку ОК.
    Теперь, если в ячейке B1 Вы выберите ИСТИНА, то формула, при каждом пересчёте, будет возвращать случайное число, если же Вы выберите ЛОЖЬ, то формула "зафиксирует" последнее значение.

    Вариант II. Актуально для MS Excel 95, 97, 2000, XP
    1. В меню Вид выберите пункт Панели инструментов и команду Формы. Кликните кнопку Флажок и разместите выбранный элемент управления в нужном месте рабочего листа, при необходимости изменив его размер и надпись.
    2. Свяжите созданный элемент управления с ячейкой. Для этого, сразу после выполнения действий, перечисленных в предыдущем пункте, кликните правой кнопкой мышки и в контекстном меню выберите команду Формат объекта. Затем, выделите закладку Элемент управления и в поле Связать с ячейкой введите адрес нужной ячейки, например $B$1
    Вместо адреса ячейки допускается использование имени, кроме того, вместо ввода адреса/имени ячейки допускается её выбор.
    3. Повторите все действия из предыдущего варианта, начиная с третьего пункта.
  • Ответ :

    Вариант I.

    =КОНМЕСЯЦА(A1;0)

    =ДЕНЬ(КОНМЕСЯЦА(A1;0))


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

    Вариант II.

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

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


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

    Вариант I.

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


    Вариант II.

    =ДАТА(ГОД(A1);МЕСЯЦ(A1);1)
    =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)


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

    =ДЕНЬНЕД(A1;2)>5
    =ДЕНЬНЕД(A1;2)>=6


    Эта формула возвратит логическое значение ИСТИНА - если в ячейке A1 находится дата, которая приходится на выходные, т.е. субботу или воскресенье, и ЛОЖЬ - если на другие дни недели.
  • Ответ :

    Вариант I-V.

    =СЕГОДНЯ()-("1/1/"&ГОД(СЕГОДНЯ()))
    =СЕГОДНЯ()-ДАТА(ГОД(СЕГОДНЯ());1;1)
    =СЕГОДНЯ()-ДАТАЗНАЧ("1/1/"&ГОД(СЕГОДНЯ()))
    =РАЗНДАТ("1/1/"&ГОД(СЕГОДНЯ());СЕГОДНЯ();"D")
    =РАЗНДАТ(ДАТА(ГОД(СЕГОДНЯ());1;1);СЕГОДНЯ();"D")


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

    Совет : Если вместо ГОД(СЕГОДНЯ()) или "1/1/"&ГОД(СЕГОДНЯ()) указать нужный год или первый день нужного года, то Вы сможете определить количество дней прошедших с начала указанного года.
  • Ответ :

    =A1+B1

    Предполагается, что :
    Ячейка A1 содержит дату, а ячейка B1 (или наоборот) содержит нужное количество дней, причём, это количество может быть как положительным, так и отрицательным.
  • Ответ :
    Дата в MS Excel это, по сути, количество дней прошедших с определённой точки отсчёта, т.е. 30.04.2007 это 39202 дня (если отсчёт ведётся с 1 января 1900 г., т.е. в рабочей книге установлена система дат 1900) или же 37740 дней (если отсчёт ведётся со 2 января 1904 г., т.е. в рабочей книге всё-таки установлена система дат 1904)
    Время же является частью даты, т.е. двадцать четыре часа 24:00:00 это 1 (один) день, шесть часов 6:00:00, это 0,25 дня, а сорок пять минут 00:45:00, это 0,03125 дня и т.д. Поэтому, учитывая всё вышеизложенное, можно прийти к простому выводу : для того, чтобы к нужной дате+времени прибавить или вычесть нужное количество секунд, минут, часов и дней достаточно использовать обычные арифметические операторы + и -

    1. Текущая дата и время +/- 3 часа :
    =ТДАТА()+3/24
    =ТДАТА()-3/24
    =ТДАТА()+0,125
    =ТДАТА()-0,125


    2. Текущая дата и время +/- 15 минут :
    =ТДАТА()+15/(24*60)
    =ТДАТА()-15/(24*60)
    =ТДАТА()+15/1440
    =ТДАТА()+15/1440


    3. Текущая дата и время +/- 42 секунды :
    =ТДАТА()+42/(24*60*60)
    =ТДАТА()-42/(24*60*60)
    =ТДАТА()+42/86400
    =ТДАТА()-42/86400


    Bonus. Текущая дата и время +/- 15 часов 27 минут и 5 секунд :
    =ТДАТА()+15/24+27/1440+5/86400
    =ТДАТА()-15/24-27/1440-5/86400


    Комментарий : Вместо стандартной функции рабочего листа =ТДАТА() можно использовать ссылку на ячейку, имя, результат вычислений другой формулы, или константу, главное, чтобы это значение могло быть преобразовано к числовому (от 0 до 2958465/2957003 в зависимости от системы дат 1900/1904)

    Примечание : По умолчанию, в рабочей книге используется система дат 1900, но это можно изменить, если в меню Сервис выбрать команду Параметры, затем выбрать закладку Вычисления и установить "флажок" напротив Система дат 1904.
  • Ответ :

    1. Текущая дата и время +/- 3 часа :
    =ТДАТА()+ВРЕМЯ(3;0;0)
    =ТДАТА()-ВРЕМЯ(3;0;0)
    =СУММ(ТДАТА();ВРЕМЯ(3;0;0))
    =СУММ(ТДАТА();-ВРЕМЯ(3;0;0))
    =ТДАТА()+ВРЕМЗНАЧ("03:00:00")
    =ТДАТА()-ВРЕМЗНАЧ("03:00:00")
    =СУММ(ТДАТА();ВРЕМЗНАЧ("03:00:00"))
    =СУММ(ТДАТА();-ВРЕМЗНАЧ("03:00:00"))


    2. Текущая дата и время +/- 15 минут :
    =ТДАТА()+ВРЕМЯ(0;15;0)
    =ТДАТА()-ВРЕМЯ(0;15;0)
    =СУММ(ТДАТА();ВРЕМЯ(0;15;0))
    =СУММ(ТДАТА();-ВРЕМЯ(0;15;0))
    =ТДАТА()+ВРЕМЗНАЧ("00:15:00")
    =ТДАТА()-ВРЕМЗНАЧ("00:15:00")
    =СУММ(ТДАТА();ВРЕМЗНАЧ("00:15:00"))
    =СУММ(ТДАТА();-ВРЕМЗНАЧ("00:15:00"))


    3. Текущая дата и время +/- 42 секунды :
    =ТДАТА()+ВРЕМЯ(0;0;42)
    =ТДАТА()-ВРЕМЯ(0;0;42)
    =СУММ(ТДАТА();ВРЕМЯ(0;0;42))
    =СУММ(ТДАТА();-ВРЕМЯ(0;0;42))
    =ТДАТА()+ВРЕМЗНАЧ("00:00:42")
    =ТДАТА()-ВРЕМЗНАЧ("00:00:42")
    =СУММ(ТДАТА();ВРЕМЗНАЧ("00:00:42"))
    =СУММ(ТДАТА();-ВРЕМЗНАЧ("00:00:42"))


    Bonus. Текущая дата и время +/- 15 часов 27 минут и 5 секунд :
    =ТДАТА()+ВРЕМЯ(15;27;5)
    =ТДАТА()-ВРЕМЯ(15;27;5)
    =СУММ(ТДАТА();ВРЕМЯ(15;27;5))
    =СУММ(ТДАТА();-ВРЕМЯ(15;27;5))
    =ТДАТА()+ВРЕМЗНАЧ("15:27:05")
    =ТДАТА()-ВРЕМЗНАЧ("15:27:05")
    =СУММ(ТДАТА();ВРЕМЗНАЧ("15:27:05"))
    =СУММ(ТДАТА();-ВРЕМЗНАЧ("15:27:05"))


    Комментарий : Смотрите выше.
  • Ответ :

    Предположим, что ячейки A1, B1, C1 содержат значения часов, минут и секунд, и нам, используя данные этих ячеек, необходимо получить время :

    Вариант I.

    =ВРЕМЯ(A1;B1;C1)

    Вариант II.

    =A1/24+B1/1440+C1/86400

    Комментарий :
  • Обратите внимание на то, что стандартная функция рабочего листа =ВРЕМЯ() не возвращает "неправильное" время, т.е., к примеру, формула =ВРЕМЯ(27;0;0) возвратит 03:00:00, а не 27:00:00
  • Если же Вам необходимо получить именно 27:00:00 , то используйте второй вариант.
  • Если секунды учитывать не нужно, то в первой формуле замените C1 на 0, а во второй, просто удалите +C1/86400
  • Не забудьте установить нужный формат ячейки, это особенно актуально при использовании второго варианта.
  • Ответ :

    =ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1)))
    =ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ПСТР(A1;1;1)))
    =ПРОПИСН(ПСТР(A1;1;1))&ПСТР(A1;2;32766)
    =ПРОПИСН(ПСТР(A1;1;1))&ПСТР(A1;2;ДЛСТР(A1)-1)
    =СЦЕПИТЬ(ПРОПИСН(ПСТР(A1;1;1));ПСТР(A1;2;32766))
    =ПРОПИСН(ЛЕВСИМВ(A1;1))&ПРАВСИМВ(A1;ДЛСТР(A1)-1)


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

    =ЗАМЕНИТЬ(СЖПРОБЕЛЫ(A1);1;1;ПРОПИСН(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1))))
  • Ответ :

    =ЗАМЕНИТЬ(СТРОЧН(A1);1;1;ПРОПИСН(ЛЕВСИМВ(A1)))
    =ЗАМЕНИТЬ(СТРОЧН(A1);1;1;ПРОПИСН(ПСТР(A1;1;1)))
    =ПРОПИСН(ПСТР(A1;1;1))&СТРОЧН(ПСТР(A1;2;32766))
    =ПРОПИСН(ПСТР(A1;1;1))&СТРОЧН(ПСТР(A1;2;ДЛСТР(A1)-1))
    =СЦЕПИТЬ(ПРОПИСН(ПСТР(A1;1;1));СТРОЧН(ПСТР(A1;2;32766)))
    =ПРОПИСН(ЛЕВСИМВ(A1;1))&СТРОЧН(ПРАВСИМВ(A1;ДЛСТР(A1)-1))


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

    =ЗАМЕНИТЬ(СЖПРОБЕЛЫ(A1);1;1;ПРОПИСН(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1))))
  • Ответ :

    =ПРОПНАЧ(СЖПРОБЕЛЫ(A1))
    =СЖПРОБЕЛЫ(ПРОПНАЧ(A1))

  • Ответ :

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

    =ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";""))+1

    Примечание : Словом считается любое сочетание символов, за исключением пробелов, т.к. пробелы служат "разделителями" слов в тексте. Причём, количество пробелов между словами, значения не имеет, т.е. текст может содержать лишние пробелы.
  • Ответ :

    =ПОВТОР("X";3)
    =ПОВТОР("Повторяющийся текст ";5)


    Примечание : Максимально допустимое количество символов, которая может возвратить стандартная функция рабочего листа =ПОВТОР() равно 32767, при генерации большего количества символов функция возвратит значение ошибки #ЗНАЧ!
  • Ответ : Актуально для MS Excel 95, 97

    =ВЫЗВАТЬ("Msvcrt40";"_strrev";"1F";"абырвалГ")
    =ВЫЗВАТЬ("Msvcrt40";"_strrev";"1F";"987654321")+0
    =ЗНАЧЕН(ВЫЗВАТЬ("Msvcrt40";"_strrev";"1F";"987654321"))


    Комментарий : Количество используемых символов не должно превышать 255.

    Автор : Laurent Longre
  • Ответ : Актуально для MS Excel 95, 97

    =ВЫЗВАТЬ("Kernel32";"GetTickCount";"J!")

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

    =ТЕКСТ(ВЫЗВАТЬ("kernel32";"GetTickCount";"J!")/86400000;"[ч]:мм:сс")

    Источник : Справка Microsoft Office
    Примечание : www.msoffice.nm.ru


  • Ответ :

    Для сортировки в порядке возрастания можно использовать стандартную функцию рабочего листа =НАИМЕНЬШИЙ(), а для сортировки в порядке убывания функцию рабочего листа =НАИБОЛЬШИЙ()

    =НАИМЕНЬШИЙ($B$2:$B$101;A2)
    =НАИБОЛЬШИЙ($B$2:$B$101;A2)


    Предполагается, что :
  • Диапазон $B$2:$B$101 содержит исходные данные, которые представляют собой числовые значения.
  • Диапазон $A$2:$A$101 содержит нумерацию элементов списка, т.е. 1, 2, 3 и т.д.

    Комментарий : Если наличие списка, содержащего нумерацию, нежелательно, то можно воспользоваться функцией =СТРОКА() однако, при этом способе, мы зависим от добавления и удаления ячеек/строк.

    =НАИМЕНЬШИЙ($B$2:$B$101;СТРОКА(B1))
    =НАИБОЛЬШИЙ($B$2:$B$101;СТРОКА(B1))


    Следующая формула массива, в этом отношении, более универсальна.

    {=НАИМЕНЬШИЙ($B$2:$B$101;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК($B$2:$B$101))))}
    {=НАИБОЛЬШИЙ($B$2:$B$101;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК($B$2:$B$101))))}

  • Ответ :

    Для сортировки текста в порядке возрастания можно использовать следующую формулу массива

    {=ИНДЕКС($B$2:$B$101;ПОИСКПОЗ(A2;СЧЁТЕСЛИ($B$2:$B$101;"<="&$B$2:$B$101);0))}

    Предполагается, что :
  • Диапазон $B$2:$B$101 содержит исходные данные, которые представляют собой текст.
  • Диапазон $A$2:$A$101 содержит нумерацию элементов списка, т.е. 1, 2, 3 и т.д.

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

    Вариант I.

    =ИНДЕКС($B$2:$B$101;ПОИСКПОЗ(1;ЧАСТОТА(A2;СЧЁТЕСЛИ($B$2:$B$101;"<="&$B$2:$B$101));0))

    Вариант II.

    {=ИНДЕКС($B$2:$B$101;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($B$2:$B$101;"<="&$B$2:$B$101);A2);СЧЁТЕСЛИ($B$2:$B$101;"<="&$B$2:$B$101);0))}

    Комментарий : Если наличие списка, содержащего нумерацию, нежелательно, то можно воспользоваться функцией =СТРОКА(), т.е. заменить A2 на СТРОКА(A1), Однако, этот способ, зависит от добавления и удаления ячеек/строк, поэтому в примере наличествует более универсальный, в этом отношении, вариант (столбец M)

    Совет : Для сортировки текста в порядке убывания просто замените <= на >=
  • Ответ :

    =ЕСЛИ(ЕЧИСЛО(B2);ДЛСТР(B2);"нечисловое значение")
    =ЕСЛИ(ТИП(B2)=1;ДЛСТР(B2);"нечисловое значение")


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

    =ЕЧИСЛО(B2)*ДЛСТР(B2)
    =(ТИП(B2)=1)*ДЛСТР(B2)

  • Ответ :

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

    =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;$A$1)

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

    Эта формула возвратит логическое значение ИСТИНА - если в указанной ячейке находится формула, и ЛОЖЬ - если ячейка пустая или содержит константу.
  • Ответ :

    Вариант I, II, III.

    В меню Вставка выберите пункт Имя, далее выберите команду Присвоить. В диалоговом окне Присвоение имени в поле Имя введите нужное имя, например ФОРМУЛА_ТЕКСТ, а в поле Формула введите :

    =ПОЛУЧИТЬ.ЯЧЕЙКУ(6;$A$1)
    =ПОЛУЧИТЬ.ФОРМУЛУ($A$1)
    =ФОРМУЛА.ПРЕОБРАЗОВАТЬ(ПОЛУЧИТЬ.ФОРМУЛУ($A$1);;1)


    и нажмите кнопку ОК. После чего, в нужную ячейку введите именованную формулу =ФОРМУЛА_ТЕКСТ

    Примечание : Если формула в ячейке $A$1 содержит ссылки, то первая формула будет возвращать ссылки в зависимости от установленного стиля A1/R1C1, вторая формула всегда будет возвращать ссылки в виде R1C1, а в третьей формуле, ссылки всегда будут в виде A1.

    Комментарий : Нельзя получить текст формулы, если в нужной ячейке был установлен параметр скрыть формулу и рабочий лист защищён.
  • Ответ :

    Вариант I.

    ="Microsoft Excel "&ИНФОРМ("Release")

    ="Microsoft Excel "&ИНФОРМ("Версия")


    Вариант II.

    Создайте именованную формулу выбрав в качестве имени формулы, например EXCEL_ВЕРСИЯ, в качестве ссылки укажите :
    ="Microsoft Excel "&ПОЛУЧИТЬ.РАБОЧЕЕ.ПРОСТРАНСТВО(2)

  • Ответ :

    =ИНФОРМ("Recalc")

    =ИНФОРМ("Перевычислить")


    Теперь, для получения корректного результата, достаточно, например, нажать клавиши SHIFT+F9 или F9
  • Ответ :

    =ИНФОРМ("NumFile")

    =ИНФОРМ("ЧислоФайлов")


    Эта формула возвратит общее количество всех рабочих листов во всех открытых рабочих книгах, включая и надстройки.
  • Ответ :

    =ЗАМЕНИТЬ(ИНФОРМ("Origin");1;3;"")

    =ЗАМЕНИТЬ(ИНФОРМ("Источник");1;3;"")


    Теперь, для получения адрес самой верхней видимой ячейки, достаточно, например, нажать клавиши SHIFT+F9 или F9
  • Ответ :

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

    =ССЫЛТЕКСТ(ВЫДЕЛЕНИЕ();ТДАТА()/ТДАТА())

    и нажмите кнопку ОК, затем в нужную ячейку введите именованную формулу =АДРЕС_ВЫДЕЛЕНИЕ

    Теперь при каждом вычислении, в т.ч. и после нажатий SHIFT+F9, F9 эта формула будет возвращать адрес выделенного диапазона.
  • Ответ :

    =ЕЧИСЛО(ОБЛАСТИ(C1:F30 A5:G7))
    =ЕЧИСЛО(ОБЛАСТИ(C1:F30 G6:H50))


    Эта формула возвратит логическое значение ИСТИНА - если все указанные в формуле диапазоны пересекаются, и ЛОЖЬ - если у этих диапазонов нет общих ячеек, т.е. если они не пересекаются.
  • Ответ :

    =ОБЛАСТИ((A1;B2;C3;D1:D10;F20:F25))

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

    =ОБЛАСТИ(ПРОДАЖИ)
    =ОБЛАСТИ(ОТГРУЗКА)

  • Ответ :

    Для того, чтобы стандартная функция рабочего листа =ВПР() автоматически, т.е. без непосредственного изменения значения аргумента Номер_столбца, возвращала значение последнего столбца, можно использовать следующую формулу :

    =ВПР(I2;$A$2:$E$100;ЧИСЛСТОЛБ($A$2:$E$100);0)
    =ВПР("Иванов";$A$2:$E$100;ЧИСЛСТОЛБ($A$2:$E$100);0)
    =ВПР(ИМЯ_ЯЧЕЙКИ;ИМЯ_ДИАПАЗОНА;ЧИСЛСТОЛБ(ИМЯ_ДИАПАЗОНА);0)


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

    Для того, чтобы стандартная функция рабочего листа =ГПР() автоматически, т.е. без непосредственного изменения значения аргумента Номер_строки, возвращала значение последней строки, можно использовать следующую формулу :

    =ГПР(I2;$A$2:$E$100;ЧСТРОК($A$2:$E$100);0)
    =ГПР("Иванов";$A$2:$E$100;ЧСТРОК($A$2:$E$100);0)
    =ГПР(ИМЯ_ЯЧЕЙКИ;ИМЯ_ДИАПАЗОНА;ЧСТРОК(ИМЯ_ДИАПАЗОНА);0)


    Совет : Этот способ может оказаться полезен, если существует вероятность добавления/удаления строк, т.е. при работе с изменяющимся диапазоном, к числу которых, можно причислить именованный диапазон, а также именованную формулу, которая возвращает ссылку на диапазон.
  • Ответ :

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

    {=СРЗНАЧ(ЕСЛИ(A2:A102<>0;A2:A102))}
  • Ответ :

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

    Вариант I. =СУММЕСЛИ(A2:A102;">0")/СЧЁТЕСЛИ(A2:A102;">0")
    Вариант II. {=СРЗНАЧ(ЕСЛИ(A2:A102>0;A2:A102))}

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

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

    {=МИН(ЕСЛИ(A2:A102<>0;A2:A102))}

    {=МАКС(ЕСЛИ(A2:A102<>0;A2:A102))}
  • Ответ :

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

    =МОДА(A2:A102)

    Комментарий : При отсутствии повторяющихся чисел - стандартная функция =МОДА() возвратит значение ошибки #Н/Д
  • Ответ :

    Подобная формула будет выглядеть следующим образом :

    =СЧЁТЕСЛИ(A2:A102;ПОВТОР("?";15))

    Предполагается, что :
    В диапазоне A2:A102 необходимо подсчитать количество ячеек содержащих текстовые значения, где количество символов (включая пробелы и т.п.) =15
  • Ответ :

    Если ячейка, содержащая пустую строку "" должна считаться пустой,
    (хотя это и не совсем корректное допущение, т.к. ячейка может содержать формулу, типа =ЕСЛИ(B2=100;"";B2), а ячейка с формулой в принципе не может быть пустой) то :

    =СЧЁТЕСЛИ(B2:C10;"")
    =СЧИТАТЬПУСТОТЫ(B2:C10)
    {=СЧЁТ(1/(B2:C10=""))}


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

    {=СЧЁТ(1/ЕПУСТО(B2:C10))}
    {=СЧЁТ(ЕСЛИ(B2:C10="";B2:C10))}
    {=СЧЁТ(ЕСЛИ(ЕПУСТО(B2:C10);B2:C10))}
    {=СУММ(ЕПУСТО(B2:C10)*1)}
    {=СУММ(Ч(ЕПУСТО(B2:C10)))}
    =СУММПРОИЗВ(ЕПУСТО(B2:C10)*1)
    =СУММПРОИЗВ(Ч(ЕПУСТО(B2:C10)))
    =ЧСТРОК(B2:C10)*ЧИСЛСТОЛБ(B2:C10)-СЧЁТЗ(B2:C10)

  • Ответ :

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

    =СЧЁТ(1/ЧАСТОТА(B2:B51;B2:B51))
    =СЧЁТ(ЕСЛИ(ЧАСТОТА(B2:B51;B2:B51)>0;1))
    =СЧЁТ(ЕСЛИ(ЧАСТОТА(B2:B51;B2:B51)>0;B2:B51))
    =СЧЁТ(ЧАСТОТА(B2:B51;B2:B51)/ЧАСТОТА(B2:B51;B2:B51))
    =СУММ((ЧАСТОТА(B2:B51;B2:B51)>0)*1)
    =СУММ((ЧАСТОТА(B2:B51;B2:B51)>0)+0)
    =СУММ(Ч(ЧАСТОТА(B2:B51;B2:B51)>0))
    =СУММ(ЕСЛИ(ЧАСТОТА(B2:B51;B2:B51)>0;1))
    {=СУММ(ЕСЛИ(ЕЧИСЛО(B2:B51);1/СЧЁТЕСЛИ(B2:B51;B2:B51)))}


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

    {=СУММ(1/СЧЁТЕСЛИ(B2:B51;B2:B51))}
    =СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B51;B2:B51))

  • Ответ :

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

    {=СУММ(ЕСЛИ(ЕТЕКСТ(B2:B51);1/СЧЁТЕСЛИ(B2:B51;B2:B51)))}
  • Ответ :

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

    {=СУММ(ЕСЛИ(ЕПУСТО(B2:B51);;1/СЧЁТЕСЛИ(B2:B51;B2:B51)))}
    {=СУММ(ЕСЛИ(НЕ(ЕПУСТО(B2:B51));1/СЧЁТЕСЛИ(B2:B51;B2:B51)))}


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

    {=СУММ(1/СЧЁТЕСЛИ(B2:B51;B2:B51))}
    =СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B51;B2:B51))

  • Ответ :

    {=СУММ(СЧЁТЕСЛИ(A2:B51;A2:B51))<>СЧЁТЗ(A2:B51)}
    =СУММПРОИЗВ(СЧЁТЕСЛИ(A2:B51;A2:B51))<>СЧЁТЗ(A2:B51)

    {=МАКС(СЧЁТЕСЛИ(A2:B51;A2:B51))>1}
    {=НАИБОЛЬШИЙ(СЧЁТЕСЛИ(A2:B51;A2:B51);1)>1}


    Эти формулы возвратят логическое значение ИСТИНА - если в указанном диапазоне находятся повторяющиеся значения, и ЛОЖЬ - если повторов нет.
  • Ответ :

    Вариант I.

    =ТЕКСТ($B$1;"ДД.ММ.ГГГГ")&" - последний день оплаты"
    =СЦЕПИТЬ(ТЕКСТ($B$1;"ДД.ММ.ГГГГ");" - последний день оплаты")

    ="Сумма оплаты " &ТЕКСТ($B$2;"0,00")
    =СЦЕПИТЬ("Сумма оплаты ";ТЕКСТ($B$2;"0,00"))


    Предполагается, что :
    Ячейка B1 содержит дату, а ячейка B2 число, и в обеих ячейках применён формат, аналогичный тому, что указан в формулах.

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

    Совет : Обратите внимание на стандартную функцию рабочего листа =ТЕКСТ() которая преобразует значение в текст с учётом указанного формата и спектр применения которой может быть существенно шире. Например, введите любую из двух формул, а затем вводите в ячейку A1 положительные, отрицательные числовые значения, ноль и текст, и следите за полученными результатами.

    =ТЕКСТ(A1;"0,00р. \Мы \должны ;-0,00р. \Как в\се\г\да,\ на\м \должны;0р. \Наконец-то ра\сплатили\сь;-")
    =ТЕКСТ(A1;"0,00р. ""Мы должны"" ;-0,00р. ""Как всегда, нам должны"";0р. ""Наконец-то расплатились"";-")


    Вариант II, III.

    Создайте именованную формулу выбрав в качестве имени формулы, например ПОЛУЧИТЬ.ТЕКСТ, а в качестве ссылки укажите :

    =ПОЛУЧИТЬ.ЯЧЕЙКУ(53;$B$1)

    или
    =ТЕКСТ($B$1;ПОЛУЧИТЬ.ЯЧЕЙКУ(7;$B$1))


    Затем, при об'единении данных, вместо ссылки на ячейку используйте эту именованную формулу.

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

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