Well... not necessarily. The exception is either units that have the "Army" unit ability, or units that have the "Army" Land Strategy (I'm not sure quite which one yet). As an experiment, I decided to give the Archer the Army ability and strategy. After also giving the Granary the Build Armies Without Leader flag, I was able to build an Archer-Army and load units into it, resulting in an Archer leading my army. This unit was able to load onto a Galley with increased transport capacity:
So it is possible to have multiple army-like units that could load onto water transports. This could be a way to allow stronger armies as the game goes on, stronger armies to certain civs, or something of that sort. Though I should note that I haven't tested what happens when you get a Leader who can build armies and multiple ones are available - just that you can build them with a building that allows this (and you can choose which one you build - provided you have enough cities to allow an army).
Side note, it seemed like Civ did something kind of strange with civ-specific colors for the Archer-Army, as if it was treating more of the colors as civ-specific than normal. The Archer had a glimmery shine, and more red hues than normal, to it before I added anything to its army. Haven't looked into it in detail, though.
This is all possible, and tested with, Firaxis's editor.
Thanks for the screenshot, Jorsalfare. I wonder if you could make it loadable onto watercraft by enabling the Army flag as set above? Though as that has other side effects, too, it may well not be desirable to do so.